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

El día martes, julio 05, 2022 a las 06:19:18p. m. +0200, Laurenz Albe escribió:

> > Hi Laurenz, ist there any way to keep/freeze such tuples until the run
> > of the next autovaccum? Some kind of config value in 13.x or 14.x? Or
> > even a code change for this? We compile on Linux from the sources and
> > have already some code changes in place...
> 
> That should be possible, but why do you want that?  Perhaps there is a better
> solution for your problem.

Here you can read the overall picture of the problem. Thanks in advance
for your time reading it.


The logic in the application in this case here goes like this:

In a table 'd01buch' (the table of all book items) a big list is
made with a call to the DB layer and this list is fetched row
by row (i.e. book by book) and each item is analyzed in other tables
if certain criteria are matched and if so the row is locked
and some of its columns get updated:

   read_where(d01buch, where ...);

   while ( fetch()==OK )   {

       ...
       if (....)  {

         start_transaction();
     rewrite_actual_row();
     end_transaction();

       }

   }

The above visible C-functions are entries in a generic DB layer
which offers the same functionality for some 400 tables, written in
ESQL/C, and can be compiled with smaller changes by the C-preprocessor
for the DBS Oracle, Sybase and PostgreSQL.

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 ...

The ctid is the value from the time when the big CURSOR was made in read_where().
Its size is normally some 100000 rows and the above while loop takes it time,
normally some 10 to 20 minutes. All this (and many other things like this) run
every night as some house keeping work of a Library Management System.

In parallel are other processes working in the same DB on the same tables,
esp. some OPAC (a web based Online Public Access Catalogue system) with which
library users (patrons) can make reservation of books, pay fees etc.

It happens that rows from the above table get modified within
the time frame of 10 to 20 minutes which means that the ctid changes
and the above SELECT * FROM d01buch WHERE ctid = ... FOR UPDATE fails because
the ctid is what is in the (old) CURSOR. We tried to use currtid2() to get
the current ctid based on the old one, but as we see this does not help
always.

Thanks

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