Re: lifetime of the old CTID

On Wed, 2022-07-06 at 08:18 +0200, Matthias Apitz wrote:
> 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(at)unixarea(dot)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 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.
>
> 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.

No, there isn't. I said that you might be able to mutilate the PostgreSQL
code so that it does that, but I think that would be a bad idea.

Using the primary key is the proper solution. To be safe from concurrent
modifications, use a logic like in this pseudo-code:

FOR b IN SELECT pk, other_columns FROM books WHERE condition
UPDATE books SET ... WHERE pk = ... AND condition

Checking the condition again on the inner UPDATE will detect concurrent
data modifications. If the UPDATE changes nothing, then a book has been
removed or updated by a concurrent transaction, and you ignore it.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com