Re: lifetime of the old CTID - Mailing list pgsql-general

El día martes, julio 05, 2022 a las 10:44:23p. m. -0700, Christophe Pettus escribió:

> 
> 
> > On Jul 5, 2022, at 22:35, Matthias Apitz <guru@unixarea.de> wrote:
> > Internally, in the DB layer, the read_where() builds the row list matching
> > the WHERE clause as a SCROLLED CURSOR of
> > 
> >    SELECT ctid, * FROM d01buch WHERE ...
> > 
> > and each fetch() delivers the next row from this cursor. The functions
> > start_transaction() and end_transaction() do what their names suggest and
> > rewrite_actual_row() does a new SELECT based on the ctid of the actual row
> > 
> >    SELECT * FROM d01buch WHERE ctid = ... FOR UPDATE
> >    ...
> >    UPDATE ...
> 
> 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
particularguarantees 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.

At the moment the DB layer informs the application layer correctly when
the row can not be found by the ctid for an UPDATE and the application
must handle this situation correctly (logging, 2nd pass through with a
new CURSOR etc.).

If I understand Laurenz correctly, there seems to be a way to keep the
tuple old-ctid : new-ctid for some time, at least until the ney
autovacuum.


    matthias

-- 
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub