Re: Dropping column from big table

On Monday, July 15, 2024, sud wrote: > > Thank you for the confirmation. > And if someone wants to fully remove that column from the table , then the > only option is to create a new table with an exact set of active columns > and insert the data into that from the existing/old table and then ren

Re: PostgreSQL Active-Active Clustering

> On Jul 15, 2024, at 12:06, Sarkar, Subhadeep wrote: > > • Does the Community edition of PostgreSQL provide NATIVE active-active high > availability clustering with objectives of scalability, load balancing and > high availability without using any extensions or external components or > u

Re: PostgreSQL Active-Active Clustering

Στις 15/7/24 22:55, ο/η Ron Johnson έγραψε: On Mon, Jul 15, 2024 at 3:28 PM Christophe Pettus wrote: > On Jul 15, 2024, at 12:06, Sarkar, Subhadeep wrote: > [snip] >   • In the Community edition of PostgreSQL is it possible to setup  a cluster where all the nodes are ab

Re: Dropping column from big table

On Tue, Jul 16, 2024 at 10:26 AM David G. Johnston < david.g.johns...@gmail.com> wrote: > > > On Monday, July 15, 2024, David G. Johnston > wrote: > >> On Monday, July 15, 2024, sud wrote: >> >>> >>> However even with "vacuum full", the old rows will be removed completely >>> from the storage ,

Re: Dropping column from big table

On Monday, July 15, 2024, David G. Johnston wrote: > On Monday, July 15, 2024, sud wrote: > >> >> However even with "vacuum full", the old rows will be removed completely >> from the storage , but the new rows will always be there with the 'dropped' >> column still existing under the hood along

Re: Dropping column from big table

On Monday, July 15, 2024, sud wrote: > > However even with "vacuum full", the old rows will be removed completely > from the storage , but the new rows will always be there with the 'dropped' > column still existing under the hood along with the table storage, with > just carrying "null" values

Re: Dropping column from big table

On Tue, Jul 16, 2024 at 6:07 AM Peter J. Holzer wrote: > > > But the only issue would be "VACUUM FULL" will take a table lock and > also it > > may take longer to run this vacuum on the full table considering the > size of > > the table in TB's. Thus, is it fine to just leave it post execution of

Re: PostgreSQL Active-Active Clustering

On Mon, Jul 15, 2024 at 5:54 PM Christoph Moench-Tegeder wrote: > ## Ron Johnson (ronljohnso...@gmail.com): > > > This "lack of products" puzzles me, because DEC was doing this with VAX > > (then Alpha and Itanium) clusters 40 years ago via a Distributed Lock > > Manager integrated deep into VMS.

Re: Dropping column from big table

On 2024-07-16 02:00:27 +0530, sud wrote: > > On Mon, Jul 15, 2024 at 7:58 PM Peter J. Holzer wrote: > > Hm, true. > > > > You can always do > > > >   UPDATE tab SET id = id; > > > > followed by > > > >   VACUUM (FULL) tab; > > Yes, that should work. It nee

Re: PostgreSQL Active-Active Clustering

## Ron Johnson (ronljohnso...@gmail.com): > This "lack of products" puzzles me, because DEC was doing this with VAX > (then Alpha and Itanium) clusters 40 years ago via a Distributed Lock > Manager integrated deep into VMS. Their Rdb and (CODASYL) DBMS products Tech and trade-offs have changed o

re-novice coming back to pgsql: porting an SQLite update statement to postgres

The last time I used PostgreSQL for anything was about 15 years ago and I have only limited SQL background, so please consider this a novice question. I have an embedded SQLite database that I would like to port to PostgreSQL, I have done the majority of this porting, but am stuck on a final compo

Re: Dropping column from big table

On Mon, Jul 15, 2024 at 7:58 PM Peter J. Holzer wrote: > > > Hm, true. > > > > You can always do > > > > UPDATE tab SET id = id; > > > > followed by > > > > VACUUM (FULL) tab; > > Yes, that should work. It needs about twice the size of the table in > temporary space, though. > > Since the OP

Re: PostgreSQL Active-Active Clustering

On Mon, Jul 15, 2024 at 3:28 PM Christophe Pettus wrote: > > > On Jul 15, 2024, at 12:06, Sarkar, Subhadeep > wrote: > > > [snip] > > • In the Community edition of PostgreSQL is it possible to setup a > cluster where all the nodes are able to concurrently read-write the > underlying database

Re: PostgreSQL Active-Active Clustering

> On Jul 15, 2024, at 12:06, Sarkar, Subhadeep wrote: > > • Does the Community edition of PostgreSQL provide NATIVE active-active > high availability clustering with objectives of scalability, load balancing > and high availability without using any extensions or external components or >

Re: PostgreSQL Active-Active Clustering

On Mon, Jul 15, 2024 at 12:06 PM Sarkar, Subhadeep wrote: > > We are evaluating features of the Community edition of PostgreSQL in > relation to a proposal for a prospective client and need help with the > queries below:- > > > >- Does the Community edition of PostgreSQL provide NATIVE >a

PostgreSQL Active-Active Clustering

Hi, We are evaluating features of the Community edition of PostgreSQL in relation to a proposal for a prospective client and need help with the queries below:- * Does the Community edition of PostgreSQL provide NATIVE active-active high availability clustering with objectives of scalabilit

Re: How does this FK constraint error happen?

On 7/15/24 10:14 AM, Ron Johnson wrote: It can't be installed? Less bureaucratic overhead to write a script. But potentially fewer errors. Also if the current process allows for connecting to one database and transferring from it to another, I'm not sure how using postgres_fdw rea

Re: How does this FK constraint error happen?

On Mon, Jul 15, 2024 at 12:47 PM Adrian Klaver wrote: > On 7/15/24 09:21, Ron Johnson wrote: > > On Mon, Jul 15, 2024 at 11:37 AM Adrian Klaver > > mailto:adrian.kla...@aklaver.com>> wrote: > > > > > > I don't think it is entirely coincidental that 1210 is the only shown > > user_id with

Re: Monitoring DB size

Slightly different approach than you might expect. For larger DBs you'd likely want to exclude base and instead use pg_database_size() in addition. postgres(2454884) =# create temp table xx(dir text, sz bigint); CREATE TABLE Time: 2.587 ms postgres(2454884) =# copy xx(sz, dir) from program 'du -s

Re: How does this FK constraint error happen?

On 7/15/24 09:21, Ron Johnson wrote: On Mon, Jul 15, 2024 at 11:37 AM Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: I don't think it is entirely coincidental that 1210 is the only shown user_id with a modified_on value that is in proximity to the delete error. I don'

Re: How does this FK constraint error happen?

On Mon, Jul 15, 2024 at 11:37 AM Adrian Klaver wrote: > On 7/15/24 08:18, Ron Johnson wrote: > > On Mon, Jul 15, 2024 at 11:06 AM Adrian Klaver > > mailto:adrian.kla...@aklaver.com>> wrote: > > > > On 7/15/24 07:53, Ron Johnson wrote: > > > On Mon, Jul 15, 2024 at 10:35 AM Peter J. Holze

Re: How does this FK constraint error happen?

On 7/15/24 08:18, Ron Johnson wrote: On Mon, Jul 15, 2024 at 11:06 AM Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: On 7/15/24 07:53, Ron Johnson wrote: > On Mon, Jul 15, 2024 at 10:35 AM Peter J. Holzer > TAPd=# select * from rel_group_user > where user_id betw

Re: Monitoring DB size

Hi, You can choose from the following options. Hopefully, they will meet your requirements 1) pg_stat_user_tables view 2) pgstattuple extension Regards, Muhammad imtiaz On Mon, 15 Jul 2024, 19:42 Shenavai, Manuel, wrote: > Hi everyone, > > > > we currently capture the db size (pg_database_si

Re: How does this FK constraint error happen?

On Mon, Jul 15, 2024 at 11:06 AM Adrian Klaver wrote: > On 7/15/24 07:53, Ron Johnson wrote: > > On Mon, Jul 15, 2024 at 10:35 AM Peter J. Holzer > > wrote: > > [snip] > > > > > > Is it possible that some other process created an entry in > > rel_group_user betwe

Re: How does this FK constraint error happen?

On 7/15/24 07:53, Ron Johnson wrote: On Mon, Jul 15, 2024 at 10:35 AM Peter J. Holzer > wrote: [snip] Is it possible that some other process created an entry in rel_group_user between these two queries? That was, in fact, the problem.  At just the wrong time to

Re: How does this FK constraint error happen?

On Mon, Jul 15, 2024 at 10:35 AM Peter J. Holzer wrote: [snip] > > Is it possible that some other process created an entry in > rel_group_user between these two queries? That was, in fact, the problem. At just the wrong time to impact one of the child databases (TAPd), but not the other two (T

Monitoring DB size

Hi everyone, we currently capture the db size (pg_database_size) which gives the “Disk space used by the database with the specified name”. Is it possible to further split this data how much space is occupied by live tuples, dead tuples and free space? We would like to have something like: DB S

Re: How does this FK constraint error happen?

On 2024-07-15 10:04:39 -0400, Ron Johnson wrote: > The job does DELETE FROM rel_group_user; (no WHERE clause!!) then does DELETE > FROM public.access_user; (also no WHERE clause), but the public.access_user >  statement fails on FK constraint error on rel_group_user (which was just > recently empti

Re: Dropping column from big table

On 2024-07-15 13:53:25 +0200, Laurenz Albe wrote: > On Sun, 2024-07-14 at 00:05 +0200, Peter J. Holzer wrote: > > On 2024-07-11 10:06:47 +0200, Laurenz Albe wrote: > > > Dropping a column is fast, but doesn't reclaim the space. > > > VACUUM won't block anything, but won't reclaim the space. > > > V

How does this FK constraint error happen?

PG 14.12 The job does DELETE FROM rel_group_user; (no WHERE clause!!) then does DELETE FROM public.access_user; (also no WHERE clause), but the public.access_user statement fails on FK constraint error on rel_group_user (which was just recently emptied). Each statement is in a different transacti

Re: Dropping column from big table

On Sun, 2024-07-14 at 00:05 +0200, Peter J. Holzer wrote: > On 2024-07-11 10:06:47 +0200, Laurenz Albe wrote: > > On Thu, 2024-07-11 at 13:10 +0530, sud wrote: > > > Dropping will take it's own time for post vacuum however as you > > > rightly said, it won't be blocking which should be fine.  > >