Re: How are the SELECT queries reconstructed in pg_views

On 3/28/23 13:23, Adrian Klaver wrote: On 3/28/23 06:43, Julius de Bruijn wrote: Hi, I haven't been able to find exactly what changes PostgreSQL does when reconstructing the query, but I've successfully been able to create views where the resulting query differs from what I wrote. Is there an

Re: How are the SELECT queries reconstructed in pg_views

On 3/28/23 06:43, Julius de Bruijn wrote: Hi, We at Prisma are implementing developer tooling for PostgreSQL database amongst the others. One part of our tooling is the migration of schema changes to the database. We do that by diffing the schema from the file system against the one we introspec

How are the SELECT queries reconstructed in pg_views

Hi, We at Prisma are implementing developer tooling for PostgreSQL database amongst the others. One part of our tooling is the migration of schema changes to the database. We do that by diffing the schema from the file system against the one we introspect from the database; storing the changes to

Re: Using CTID system column as a "temporary" primary key

On Tue, Mar 28, 2023 at 6:40 AM Sebastien Flaesch wrote: > ... > > > I think if you're honest with yourself you already know the answer to this > question. The only real solution is to update the legacy code to use the > primary key, or (if that's not possible) change the table definition to add

Re: Cluster table based on grand parent?

On 3/28/23 11:28, Dominique Devienne wrote: On Tue, Mar 28, 2023 at 6:06 PM Ron wrote: You can only get from parent to grandchild via//child.id to grandchild.parent, so why not cluster grandchild on grandchild.parent? Hi. I don't understand your question. Yes, of

Re: Cluster table based on grand parent?

On 3/28/23 10:28, Dominique Devienne wrote: On Tue, Mar 28, 2023 at 6:06 PM Ron wrote: You can only get from parent to grandchild via//child.id to grandchild.parent, so why not cluster grandchild on grandchild.parent? Hi. I don't understand your question. Yes, of

Re: Using CTID system column as a "temporary" primary key

Note that VACUUM FULL and CLUSTER can update ctids. I don't believe regular VACUUM can, so you should be safe from autovacuum interfering in this scheme, but the ctid colum documentation [1] states "A primary key should be used to identify logical rows," so this is not exactly intended usage. Than

Re: Cluster table based on grand parent?

On Tue, Mar 28, 2023 at 6:06 PM Ron wrote: > You can only get from parent to grandchild via child.id to > grandchild.parent, so why not cluster grandchild on grandchild.parent? > Hi. I don't understand your question. Yes, of course, if I want all grand-children of a given parent, I'll do for exa

Re: Cluster table based on grand parent?

On 3/28/23 08:17, Dominique Devienne wrote: Hi again, I just sent a question regarding parent/child and cascading FKs. But in reality, our schema has not 2 but 3 "layers", with an additional grandchild "leaf" table (see below). Given that many acces patterns are parent-based, i.e. get all child

Re: Patroni, slots, and expiring WALs

On 2023-03-28 17:27:27 +0200, Peter J. Holzer wrote: > On 2023-03-28 17:08:38 +0200, Alexander Kukushkin wrote: > > The second option - you can put all member names into permanent slots > > configuration (using patronictl edit-config): > > slots: > >   nodename1: > >     type: physical > >   nodena

Re: Patroni, slots, and expiring WALs

On 2023-03-28 17:08:38 +0200, Alexander Kukushkin wrote: > On Tue, 28 Mar 2023 at 16:55, Peter J. Holzer wrote: > > > However, when we took down one node for about two hours for some tests > recently (with some moderate traffic on the remaining node), the replica > didn't catch up af

Re: Cluster table based on grand parent?

On Tue, Mar 28, 2023 at 5:08 PM Peter J. Holzer wrote: > On 2023-03-28 07:26:47 -0700, Adrian Klaver wrote: > > On 3/28/23 06:17, Dominique Devienne wrote: > > > PS: At this point, I don't even know how much cluster affects > performance. > > I think that this depends a lot on your access pattern

Re: Patroni, slots, and expiring WALs

On 2023-03-28 11:07:04 -0400, Jeremy Smith wrote: > On Tue, Mar 28, 2023 at 10:55 AM Peter J. Holzer wrote: > > The configuration includes `use_slots: true` and I can see a slot in > pg_replication_slots on the leader. > > I was under the impression that this would be sufficient to p

Re: Patroni, slots, and expiring WALs

Hi, On Tue, 28 Mar 2023 at 16:55, Peter J. Holzer wrote: > > However, when we took down one node for about two hours for some tests > recently (with some moderate traffic on the remaining node), the replica > didn't catch up after being restarted and inspection of the logs showed > that it was

Re: Cluster table based on grand parent?

On 2023-03-28 07:26:47 -0700, Adrian Klaver wrote: > On 3/28/23 06:17, Dominique Devienne wrote: > > PS: At this point, I don't even know how much cluster affects performance. I think that this depends a lot on your access patterns (especially on how much you update the grandchild table and whethe

Re: Patroni, slots, and expiring WALs

On Tue, Mar 28, 2023 at 10:55 AM Peter J. Holzer wrote: > > > The configuration includes `use_slots: true` and I can see a slot in > pg_replication_slots on the leader. > > I was under the impression that this would be sufficient to prevent WALs > from being deleted on the leader before they are

Patroni, slots, and expiring WALs

I think I'm missing something basic here. We have set up a postgresql cluster with Patroni (3.0.1-1.pgdg22.04+1) and PostgreSQL (15+248.pgdg22.04+1) from the PGDG repo fur Ubuntu. The patroni configuration was created via the pg_createconfig_patroni script, basically using all the defaults. The

Re: Cluster table based on grand parent?

On 3/28/23 06:17, Dominique Devienne wrote: Hi again, Thanks for any insights. --DD PS: At this point, I don't even know how much cluster affects performance.     But because it can affect the schema structure (by denormalizing), i'd rather know early. You will need to explain to me how

Re: Using CTID system column as a "temporary" primary key

CAST seems to do the job so that's a good solution here. Seb From: Christophe Pettus Sent: Tuesday, March 28, 2023 2:39 PM To: Sebastien Flaesch Cc: Geoff Winkless ; pgsql-general Subject: Re: Using CTID system column as a "temporary" primary key EXTERNAL: Do

Re: Plans for ON DELETE CASCADE? Which index is used, if at all?

On 3/28/23 06:55, Dominique Devienne wrote: On Tue, Mar 28, 2023 at 3:23 PM Tom Lane > wrote: Dominique Devienne mailto:ddevie...@gmail.com>> writes: > Hi. Given the classical parent/child schema below, with an ON DELETE > CASCADE FK constraint, I'd like

Re: Plans for ON DELETE CASCADE? Which index is used, if at all?

On Tue, Mar 28, 2023 at 3:23 PM Tom Lane wrote: > Dominique Devienne writes: > > Hi. Given the classical parent/child schema below, with an ON DELETE > > CASCADE FK constraint, I'd like to know which index is used (if any) to > > delete child rows affected by the CASCADE. > > > But explain (anal

Re: Plans for ON DELETE CASCADE? Which index is used, if at all?

Dominique Devienne writes: > Hi. Given the classical parent/child schema below, with an ON DELETE > CASCADE FK constraint, I'd like to know which index is used (if any) to > delete child rows affected by the CASCADE. > But explain (analyze) does not give me that. Yeah, it will just report the ti

Cluster table based on grand parent?

Hi again, I just sent a question regarding parent/child and cascading FKs. But in reality, our schema has not 2 but 3 "layers", with an additional grandchild "leaf" table (see below). Given that many acces patterns are parent-based, i.e. get all child of given parent, or get all grandchild of giv

Plans for ON DELETE CASCADE? Which index is used, if at all?

Hi. Given the classical parent/child schema below, with an ON DELETE CASCADE FK constraint, I'd like to know which index is used (if any) to delete child rows affected by the CASCADE. But explain (analyze) does not give me that. The reason I'm asking, is because we currently (automatically) add i

Re: Using CTID system column as a "temporary" primary key

> On Mar 28, 2023, at 03:39, Sebastien Flaesch > wrote: > Do I have to cast() ? Yes: select * from t where ctid='(0,1)'::tid; The string representation can be up to 17 characters: 10 for the page number, 4 for the tuple number, and three for the delimiters. Remember that updating

Re: Using CTID system column as a "temporary" primary key

Hi Geoff, Your remark makes total sense, and this is what should be done. However, we have to deal with quite complex legacy 4GL code that we prefer to not touch, and we can adapt the SQL statements on the fly with our solution (kind of Java compiler/runtime system). Next question: How can I

Re: Using CTID system column as a "temporary" primary key

How large can the string representation of a CTID (TID type) be? This page mentions 6 bytes for t_ctid / ItemPointerData... => how can I deduce the max size of its string representation? https://www.postgresql.org/docs/14/storage-page-layout.html Seb From: Sebast

Re: Using CTID system column as a "temporary" primary key

On Tue, 28 Mar 2023 at 10:28, Sebastien Flaesch wrote: > Is the CTID a good choice? > I think if you're honest with yourself you already know the answer to this question. The only real solution is to update the legacy code to use the primary key, or (if that's not possible) change the table defi

Re: Using CTID system column as a "temporary" primary key

I mean Oracle's ROWID of course, not ROWNUM. ROWNUM is temporary in the context of the SELECT, so it cannot be used in subsequent SQL statements. Seb From: Sebastien Flaesch Sent: Tuesday, March 28, 2023 11:28 AM To: pgsql-general Subject: Using CTID system colum

DB migration : Sybase to Postgres

Team, Need some help regarding Open Source tools for DB migration (from SYBASE to PostgreSQL) with real time CDC. Along with this, is there any possibility to migrate other objects (like Triggers, Stored Procedures and Functions) to PostgreSQL? Thanks, Senko

Using CTID system column as a "temporary" primary key

Hello! We are looking for an equivalent of Informix ROWID or Oracle's ROWNUM. Is the CTID a good choice? I assume it must be used in a specific context, and of course not considered as permanent primary key. I understand that if the row is updated, the CTID may change. Where can we find detai