Re: lifetime of the old CTID

Am Wed, Jul 06, 2022 at 08:18:42AM +0200 schrieb Matthias Apitz:

> > On first glance, it appears that you are using the ctid as a primary key for a row, and that's highly not-recommended.  The ctid is never intended to be stable in the database, as you have discovered.  There are really no particular guarantees about ctid values being retained.
> >
> > I'd suggest having a proper primary key column on the table, and using that instead.
>
> Ofc, each table has its own primary key(s), used for example for the
> SELECT ctid, * FROM d01buch WHERE ...
>
> As I said, we came to PostgreSQL from Sybase (and Oracle) and Sybase has
> for each table a so called SYB_IDENTITY_COLUMN which is static for the
> table and its value does not change. When we would add now to some 400 tables an
> additional INTEGER column (and triggers to fill this on INSERT) this
> would be a big change in our DB layer and migration of databases in the
> field. Your suggesting (thanks for it in any case) is not that easy to
> implement, and no option at the moment.

Christopher suggested to *use* the primary key, not to *add*
one.

You said that there *is* a primary key.

So, more thought/explanation would need to go into why that
cannot be used.

Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B