Using CTID system column as a "temporary" primary key

[Sebastien Flaesch]

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 details about the validity and lifetime of the value such 
column?

Will CTID be supported long term or is there any plan to remove it or hide it 
some day?

Of course, one should use a real primary key definition. However, we have 
legacy code to adapt to PostgreSQL, and in some cases, tables have a composite 
primary key. A first SELECT uses that primary key, but it also fetches the 
ROWID, and will use that one in a subsequent SELECT, UPDATE or DELETE, instead 
of carrying the composite pkey values.

Seb

DB migration : Sybase to Postgres

[Sengottaiyan T]

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

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

[Sebastien Flaesch]

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 column as a "temporary" primary key


EXTERNAL: Do not click links or open attachments if you do not recognize the 
sender.

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 details about the validity and lifetime of the value such 
column?

Will CTID be supported long term or is there any plan to remove it or hide it 
some day?

Of course, one should use a real primary key definition. However, we have 
legacy code to adapt to PostgreSQL, and in some cases, tables have a composite 
primary key. A first SELECT uses that primary key, but it also fetches the 
ROWID, and will use that one in a subsequent SELECT, UPDATE or DELETE, instead 
of carrying the composite pkey values.

Seb

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

[Geoff Winkless]

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 definition to add
your own indexed BIGSERIAL value called "ROWID" to the rows and use that
instead (assuming it will be large enough).

Geoff

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

[Sebastien Flaesch]

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: Sebastien Flaesch 
Sent: Tuesday, March 28, 2023 11:57 AM
To: pgsql-general 
Subject: Re: Using CTID system column as a "temporary" primary key


EXTERNAL: Do not click links or open attachments if you do not recognize the 
sender.

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 column as a "temporary" primary key


EXTERNAL: Do not click links or open attachments if you do not recognize the 
sender.

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 details about the validity and lifetime of the value such 
column?

Will CTID be supported long term or is there any plan to remove it or hide it 
some day?

Of course, one should use a real primary key definition. However, we have 
legacy code to adapt to PostgreSQL, and in some cases, tables have a composite 
primary key. A first SELECT uses that primary key, but it also fetches the 
ROWID, and will use that one in a subsequent SELECT, UPDATE or DELETE, instead 
of carrying the composite pkey values.

Seb

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

[Sebastien Flaesch]

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 UPDATE or DELETE a row, with the CTID column?

When I bind a string parameter, I get this error:

SQLSTATE = 42883
MESSAGE: operator does not exist: tid = character varying

Do I have to cast() ?

Seb


From: Geoff Winkless 
Sent: Tuesday, March 28, 2023 12:20 PM
To: Sebastien Flaesch 
Cc: pgsql-general 
Subject: Re: Using CTID system column as a "temporary" primary key


EXTERNAL: Do not click links or open attachments if you do not recognize the 
sender.

On Tue, 28 Mar 2023 at 10:28, Sebastien Flaesch 
mailto:sebastien.flae...@4js.com>> 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 definition to add 
your own indexed BIGSERIAL value called "ROWID" to the rows and use that 
instead (assuming it will be large enough).

Geoff

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

[Christophe Pettus]

> 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 a row changes its CTID.

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

[Dominique Devienne]

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 indexes
on FK columns, to avoid full-scan of child tables when a parent row is
deleted (i.e. un-indexed FKs), but I realized that index we create
explicitly is also a *prefix* of the natural-key constraint, thus I suspect
redundant with the index supporting that NK constraint.

But before getting rid of that index, which I suspect is redundant, I
wanted to verify.
And I happen to discover I don't know how to verify it. My PostgreSQL Fu is
not great...

So, could someone please:
1) teach me how to know which index is used when cascading FKs in child
tables?
2) confirm my suspicion that the  child_parent_idx index below is
redundant, given the  child_parent_name_key one?

Thanks, --DD

```
dd=> create table parent (id int generated always as identity primary key,
name text not null unique);
CREATE TABLE

dd=> create table child (id int generated always as identity primary key,
parent int not null references parent(id) on delete cascade, name text not
null, unique(parent, name));
CREATE TABLE

dd=> create index child_parent_idx on child(parent);
CREATE INDEX

dd=> insert into parent(name) values ('foo');
INSERT 0 1
dd=> insert into child(parent, name) values (1, 'c1');
INSERT 0 1
dd=> insert into child(parent, name) values (1, 'c2');
INSERT 0 1
dd=> insert into parent(name) values ('bar');
INSERT 0 1
dd=> insert into child(parent, name) values (2, 'c3');
INSERT 0 1

dd=> explain (analyze) delete from parent where id = 2;
QUERY PLAN
--
 Delete on parent  (cost=0.15..8.17 rows=1 width=6) (actual
time=0.052..0.053 rows=0 loops=1)
   ->  Index Scan using parent_pkey on parent  (cost=0.15..8.17 rows=1
width=6) (actual time=0.021..0.024 rows=1 loops=1)
 Index Cond: (id = 2)
 Planning Time: 0.090 ms
 Trigger for constraint child_parent_fkey: time=0.242 calls=1
 Execution Time: 0.329 ms
(6 rows)

dd=> \d+ child
  Table "public.child"
 Column |  Type   | Collation | Nullable |   Default|
Storage  | Stats target | Description
+-+---+--+--+--+--+-
 id | integer |   | not null | generated always as identity |
plain|  |
 parent | integer |   | not null |  |
plain|  |
 name   | text|   | not null |  |
extended |  |
Indexes:
"child_pkey" PRIMARY KEY, btree (id)
"child_parent_name_key" UNIQUE CONSTRAINT, btree (parent, name)
"child_parent_idx" btree (parent)
Foreign-key constraints:
"child_parent_fkey" FOREIGN KEY (parent) REFERENCES parent(id) ON
DELETE CASCADE
Access method: heap
```

Cluster table based on grand parent?

[Dominique Devienne]

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 given child, I can
use [CLUSTER][1] leveraging the natural-key (parent, name) constraint/index.

But for grandchild rows, doesn't mean the rows for a given (grand)parent
won't be fully clustered? Yes, our software often accesses rows in child
and grandchild
for a given parent row.

So can grandchild table(s) be "fully" clustered per-(grand)parent?
Would that require denormalizing, and adding an extra grandparent column FK
in grandchild, to achieve that?
And if that's the case, then there are two "paths" to CASCADE a delete from
parent; Would that be a problem?  (w.r.t. performance or otherwise?)

Finally, does cluster affect associated toast tables too? (the doc doesn't
say)

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.

[1]: https://www.postgresql.org/docs/current/sql-cluster.html

```
dd=> create table parent (id int generated always as identity primary key,
name text not null unique);
CREATE TABLE

dd=> create table child (id int generated always as identity primary key,
parent int not null references parent(id) on delete cascade, name text not
null, unique(parent, name));
CREATE TABLE

dd=> create table grandchild (id int generated always as identity primary
key, parent int not null references child(id) on delete cascade, name text
not null, unique(parent, name));
CREATE TABLE
```

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

[Tom Lane]

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 time spent in the FK triggers,
not what they were doing exactly.

IIRC, you can see the CASCADE operations with contrib/auto_explain,
if you enable auto_explain.log_nested_statements.

regards, tom lane

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

[Dominique Devienne]

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 (analyze) does not give me that.
>
> Yeah, it will just report the time spent in the FK triggers,
> not what they were doing exactly.
>
> IIRC, you can see the CASCADE operations with contrib/auto_explain,
> if you enable auto_explain.log_nested_statements.
>

Thanks. Looks like this won't be easily available to me :(. --DD

dd=> select * from pg_available_extensions where name like '%auto%';
  name   | default_version | installed_version |comment
-+-+---+---
 autoinc | 1.0 |   | functions for
autoincrementing fields
(1 row)

dd=> select * from pg_available_extensions where name like '%explain%';
 name | default_version | installed_version | comment
--+-+---+-
(0 rows)

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

[Adrian Klaver]

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 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 time spent in the FK triggers,
not what they were doing exactly.

IIRC, you can see the CASCADE operations with contrib/auto_explain,
if you enable auto_explain.log_nested_statements.


Thanks. Looks like this won't be easily available to me :(. --DD


It is part of the community contrib modules:

https://www.postgresql.org/docs/current/auto-explain.html

So install via whatever package system you are using. Or if building 
from source build in the contrib/ directory.




dd=> select * from pg_available_extensions where name like '%auto%';
   name   | default_version | installed_version |                comment
-+-+---+---
  autoinc | 1.0             |                   | functions for 
autoincrementing fields

(1 row)

dd=> select * from pg_available_extensions where name like '%explain%';
  name | default_version | installed_version | comment
--+-+---+-
(0 rows)


--
Adrian Klaver
adrian.kla...@aklaver.com

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

[Sebastien Flaesch]

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 not click links or open attachments if you do not recognize the 
sender.

> 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 a row changes its CTID.

Re: Cluster table based on grand parent?

[Adrian Klaver]

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 it denormalizes? It reorders rows by 
index definition and does not maintain that order over updates and inserts.




[1]: https://www.postgresql.org/docs/current/sql-cluster.html 



```
dd=> create table parent (id int generated always as identity primary 
key, name text not null unique);

CREATE TABLE

dd=> create table child (id int generated always as identity primary 
key, parent int not null references parent(id) on delete cascade, name 
text not null, unique(parent, name));

CREATE TABLE

dd=> create table grandchild (id int generated always as identity 
primary key, parent int not null references child(id) on delete cascade, 
name text not null, unique(parent, name));

CREATE TABLE
```


--
Adrian Klaver
adrian.kla...@aklaver.com

Patroni, slots, and expiring WALs

[Peter J. Holzer]

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 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 used on the replica.

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 trying to get WALs which had already been deleted.

So apparently, `use_slots: true` isn't enough. What else do I have to
configure? (I know about wal_keep_size, but it was my understanding that
this isn't needed when slots are used)

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: Patroni, slots, and expiring WALs

[Jeremy Smith]

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 used on the replica.
>


Is max_slot_wal_keep_size set to something other than -1 on the leader?

Re: Cluster table based on grand parent?

[Peter J. Holzer]

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 whether those updates can
be HOT), so you will probably have to measure it yourself with a
realistic work load.

(Personally I doubt the impact is large, but I don't know your data or
your access patterns.)

> >      But because it can affect the schema structure (by denormalizing),
> > i'd rather know early.
> 
> You will need to explain to me how it denormalizes? It reorders rows by
> index definition and does not maintain that order over updates and inserts.

I think he means that in order to cluster the grandchild table by the parent.id
he would have to denormalize the table. I.e. instead of like this:

> > ```
> > dd=> create table parent (id int generated always as identity primary
> > key, name text not null unique);
> > CREATE TABLE
> > 
> > dd=> create table child (id int generated always as identity primary
> > key, parent int not null references parent(id) on delete cascade, name
> > text not null, unique(parent, name));
> > CREATE TABLE
> > 
> > dd=> create table grandchild (id int generated always as identity
> > primary key, parent int not null references child(id) on delete cascade,
> > name text not null, unique(parent, name));
> > CREATE TABLE
> > ```

The last create statement would have to be like this:

create table grandchild (
id int generated always as identity primary key,
parent int not null references child(id) on delete cascade,
grandparent int not null references parent(id) on delete cascade,
name text not null,
unique(grandparent, parent, name)
);

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: Patroni, slots, and expiring WALs

[Alexander Kukushkin]

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 trying to get WALs which had already been deleted.
>


It is impossible to know whether it is down temporarily or gone forever,
therefore the slot is removed as soon as the member key expires (after TTL).


> So apparently, `use_slots: true` isn't enough. What else do I have to
> configure? (I know about wal_keep_size, but it was my understanding that
> this isn't needed when slots are used)
>

The best option is to configure continuous archiving and PITR. Backups are
always important.
The second option - you can put all member names into permanent slots
configuration (using patronictl edit-config):
slots:
  nodename1:
type: physical
  nodename2:
type: physical
  nodename3:
type: physical

This way slots representing these members will not be removed.

Regards,
--
Alexander Kukushkin

Re: Patroni, slots, and expiring WALs

[Peter J. Holzer]

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 prevent WALs
> from being deleted on the leader before they are used on the replica.
> 
> 
> 
> Is max_slot_wal_keep_size set to something other than -1 on the leader? 

Nope, it's -1:

postgres=# show max_slot_wal_keep_size ;
╔╗
║ max_slot_wal_keep_size ║
╟╢
║ -1 ║
╚╝
(1 row)

(Sorry, forgot to mention this)

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: Cluster table based on grand parent?

[Dominique Devienne]

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 patterns


As I wrote, per-parent access to child and grandchild rows is typical.
So w/o parent-based clustering of grandchild table(s), access those rows
could potential seek to several (~50, see below) smaller clusters with
arbitrary gaps.

Cardinality is a few to ~20K on parent, x10-x50 on child, x20 - x100 on
grandchild.
So total row count rarely exceeds the 1M - 10M range. But there are
LOBs/BYTEa...


> (especially on
> how much you update the grandchild table and whether those updates can
> be HOT), so you will probably have to measure it yourself with a
> realistic work load.
>

In this particular case, there aren't much UPDATEs, because of a deficiency
of the client applications, which mostly do DELETE+INSERT instead of
UPDATEs.
Although we have to cascade modified dates up the parent hierarchy,
so some UPDATEs do occur, but mostly on the less numerous child and parent
tables.


> (Personally I doubt the impact is large, but I don't know your data or
> your access patterns.)
>

OK.


> > >  But because it can affect the schema structure (by denormalizing),
> > > i'd rather know early.
> >
> > You will need to explain to me how it denormalizes? It reorders rows by
> > index definition and does not maintain that order over updates and
> inserts.
>
> I think he means that in order to cluster the grandchild table by the
> parent.id
> he would have to denormalize the table.
>

exactly.

Thanks for your input.

Re: Patroni, slots, and expiring WALs

[Peter J. Holzer]

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 after being restarted and inspection of the logs showed
> that it was trying to get WALs which had already been deleted.
> 
> 
> 
> It is impossible to know whether it is down temporarily or gone forever,
> therefore the slot is removed as soon as the member key expires (after TTL).
>  

TTL is 30 seconds by default. So that doesn't seem very useful.


> So apparently, `use_slots: true` isn't enough. What else do I have to
> configure? (I know about wal_keep_size, but it was my understanding that
> this isn't needed when slots are used)
> 
> 
> The best option is to configure continuous archiving and PITR. Backups are
> always important.

Yeah, but for the given application a daily dump is sufficient for
backups, so I'd like to keep that simple.

> The second option - you can put all member names into permanent slots
> configuration (using patronictl edit-config):
> slots:
>   nodename1:
>     type: physical
>   nodename2:
>     type: physical
>   nodename3:
>     type: physical
> 
> This way slots representing these members will not be removed.

That seems to work (at least the slot didn't disappear within a few
minutes). I'll do some more testing.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: Patroni, slots, and expiring WALs

[Peter J. Holzer]

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
> >   nodename2:
> >     type: physical
> >   nodename3:
> >     type: physical
> > 
> > This way slots representing these members will not be removed.
> 
> That seems to work (at least the slot didn't disappear within a few
> minutes). I'll do some more testing.

Works nicely. Thanks!

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: Cluster table based on grand parent?

[Ron]

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 of given parent, or get all grandchild of given child, I can
use [CLUSTER][1] leveraging the natural-key (parent, name) constraint/index.

But for grandchild rows, doesn't mean the rows for a given (grand)parent
won't be fully clustered? Yes, our software often accesses rows in child 
and grandchild

for a given parent row.

So can grandchild table(s) be "fully" clustered per-(grand)parent?
Would that require denormalizing, and adding an extra grandparent column 
FK in grandchild, to achieve that?
And if that's the case, then there are two "paths" to CASCADE a delete 
from parent; Would that be a problem? (w.r.t. performance or otherwise?)


Finally, does cluster affect associated toast tables too? (the doc doesn't 
say)


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.


[1]: https://www.postgresql.org/docs/current/sql-cluster.html

```
dd=> create table parent (id int generated always as identity primary key, 
name text not null unique);

CREATE TABLE

dd=> create table child (id int generated always as identity primary key, 
parent int not null references parent(id) on delete cascade, name text not 
null, unique(parent, name));

CREATE TABLE

dd=> create table grandchild (id int generated always as identity primary 
key, parent int not null references child(id) on delete cascade, name text 
not null, unique(parent, name));

CREATE TABLE
```


You can only get from parent to grandchild via//child.id to 
grandchild.parent, so why not cluster grandchild on grandchild.parent?


--
Born in Arizona, moved to Babylonia.

Re: Cluster table based on grand parent?

[Dominique Devienne]

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 example:

select p.id, c.id, c.name, gc.*
  from  grandchild gc
   join child c on gc.parent = c.id
   join parent p on c.parent = p.id
where p.name = $1

But w/o clustering on a denormalized grandchild.grandparent FK column, as
Peter showed,
and cluster only on grandchild.parent, that's not going to access a mostly
continuous range
of pages to fetch those all grandchild rows for that one parent. But
probably 10 to 50 "row-clusters",
given the fan-out I mentioned earlier at the child-table level. Or am I
missing something?

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

[Maciek Sakrejda]

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.

Thanks,
Maciek

[1]: https://www.postgresql.org/docs/current/ddl-system-columns.html

Re: Cluster table based on grand parent?

[Rob Sargent]

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 course, if I want all 
grand-children of a given parent, I'll do for example:


select p.id , c.id , c.name , 
gc.*

  from  grandchild gc
   join child c on gc.parent = c.id 
   join parent p on c.parent = p.id 
where p.name  = $1

But w/o clustering on a denormalized grandchild.grandparent FK column, 
as Peter showed,
and cluster only on grandchild.parent, that's not going to access a 
mostly continuous range
of pages to fetch those all grandchild rows for that one parent. But 
probably 10 to 50 "row-clusters",
given the fan-out I mentioned earlier at the child-table level. Or am 
I missing something?

Are you using HDD (spinning) or SSD discs?
Is you world strictly three levels: grand,parent,child?
What tests have you done so far to compare clustered to non-clustered?

Re: Cluster table based on grand parent?

[Ron]

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 course, if I want all 
grand-children of a given parent, I'll do for example:


select p.id , c.id , c.name , gc.*
  from  grandchild gc
   join child c on gc.parent = c.id 
   join parent p on c.parent = p.id 
where p.name  = $1

But w/o clustering on a denormalized grandchild.grandparent FK column, as 
Peter showed,
and cluster only on grandchild.parent, that's not going to access a mostly 
continuous range
of pages to fetch those all grandchild rows for that one parent. But 
probably 10 to 50 "row-clusters",
given the fan-out I mentioned earlier at the child-table level. Or am I 
missing something?


No, you're not missing something.  If you want to go directly from 
grandparent to grandchild, then you need to put grandparent_id in the 
grandchild table.


Rob Sargent is right, too, though: *practically* it might not make a 
difference.  You've got to test.


--
Born in Arizona, moved to Babylonia.

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

[Kirk Wolak]

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
> your own indexed BIGSERIAL value called "ROWID" to the rows and use that
> instead (assuming it will be large enough).
>
> Geoff
>

I have to second this...  Why not, during conversion, create a ROWID
BIGSERIAL column in the PG only version.  (And if not large enough, it's
easy enough to use a NUMERIC field, and a sequence)
Then the code would have access to this field, and when it uses it to
delete/update it should work.

I cringe at the thought of using CTID.  And while it's probably "safe
enough" inside a single transaction.  I doubt that there is much "testing"
of this concept.

Having been through this process (Oracle to PG), I wonder how far you are
into the process...  Because Packages/Package Variables, Global Temp
Tables, and Autonomous Transactions all consumed significant time in our
process, as well as variable/field naming problems...  If you pull off
converting this to PG without changing the source.  Let me know...

Regards, Kirk

How are the SELECT queries reconstructed in pg_views

[Julius de Bruijn]

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 a migration file.

We are right now extending this to cover database views, and right now
I'm looking for ways to compare the SQL written by the user against
the SQL definition found in the pg_views view in the database.

We do have a good SQL parser, which makes minor differences such as
whitespace or newlines not matter in the comparison. What makes things
more difficult is how PostgreSQL reconstructs the SELECT query before
storing it to the database, as is written in the documentation:

https://www.postgresql.org/docs/current/view-pg-views.html

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
any documentation for this feature where I can learn more about what
happens before the query is stringified to the information schema? Or,
even better, is there a way for me to send a query to the database and
as a result get back a reconstructed query?

Thank you!

Julius de Bruijn
Software Engineer
https://www.prisma.io/

Re: How are the SELECT queries reconstructed in pg_views

[Adrian Klaver]

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 introspect from the database;
storing the changes to a migration file.

We are right now extending this to cover database views, and right now
I'm looking for ways to compare the SQL written by the user against
the SQL definition found in the pg_views view in the database.

We do have a good SQL parser, which makes minor differences such as
whitespace or newlines not matter in the comparison. What makes things
more difficult is how PostgreSQL reconstructs the SELECT query before
storing it to the database, as is written in the documentation:

https://www.postgresql.org/docs/current/view-pg-views.html

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
any documentation for this feature where I can learn more about what
happens before the query is stringified to the information schema? Or,
even better, is there a way for me to send a query to the database and
as a result get back a reconstructed query?



SELECT pg_catalog.pg_get_viewdef('61822'::pg_catalog.oid, true);
 pg_get_viewdef

  SELECT up_test.id AS up_id,  +
 up_test.col_1 AS bool_col,+
 up_test.col_2 AS col2 +
FROM up_test;

Find pg_get_viewdef here:
https://www.postgresql.org/docs/current/functions-info.html

Source here:

~/src/backend/utils/adt/ruleutils.c



Thank you!

Julius de Bruijn
Software Engineer
https://www.prisma.io/





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: How are the SELECT queries reconstructed in pg_views

[Adrian Klaver]

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
any documentation for this feature where I can learn more about what
happens before the query is stringified to the information schema? Or,
even better, is there a way for me to send a query to the database and
as a result get back a reconstructed query?





I should have added I got pg_get_viewdef() from the below :

\d+ information_schema.views


...

 CASE
WHEN pg_has_role(c.relowner, 'USAGE'::text) THEN 
pg_get_viewdef(c.oid)

ELSE NULL::text
END::information_schema.character_data AS view_definition,

...


SELECT pg_catalog.pg_get_viewdef('61822'::pg_catalog.oid, true);
  pg_get_viewdef

   SELECT up_test.id AS up_id,  +
  up_test.col_1 AS bool_col,+
  up_test.col_2 AS col2 +
     FROM up_test;




--
Adrian Klaver
adrian.kla...@aklaver.com