Horton

Thread

  1. lifetime of the old CTID

    Matthias Apitz <guru@unixarea.de> — 2022-07-05T07:51:19Z

    Hello,

We're using the SQL function currtid2() to get the new CTID of a row
when this was UPDATEd.

Investigating cases of failing updates, it turns out that the old CTID
has only a limited lifetime; one can check this with SQL:

sisis=# select ctid, d01gsi from d01buch where d01gsi = '0240564';
    ctid    |           d01gsi            
------------+-----------------------------
 (29036,11) | 0240564

now I update the row and afterwards pick up the new CTID based on the
old one (29036,11):

sisis=# UPDATE d01buch set d01ort='Wolfratshausen' where d01gsi='0240564';
UPDATE 1
sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
 currtid2  
-----------
 (29036,7)

Now I go and pick up a coffe in our kitchen and check again:

sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
  currtid2  
------------
 (29036,11)

i.e. the function now only returns it argument. and not the new CTID
anymore.

Why is this? And what triggers exactly that the old CTID can't be used
anymore?

Thanks

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

  2. Re: lifetime of the old CTID

    Laurenz Albe <laurenz.albe@cybertec.at> — 2022-07-05T08:40:40Z

    On Tue, 2022-07-05 at 09:51 +0200, Matthias Apitz wrote:
> We're using the SQL function currtid2() to get the new CTID of a row
> when this was UPDATEd.
> 
> Investigating cases of failing updates, it turns out that the old CTID
> has only a limited lifetime; one can check this with SQL:
> 
> sisis=# select ctid, d01gsi from d01buch where d01gsi = '0240564';
>     ctid    |           d01gsi            
> ------------+-----------------------------
>  (29036,11) | 0240564
> 
> now I update the row and afterwards pick up the new CTID based on the
> old one (29036,11):
> 
> sisis=# UPDATE d01buch set d01ort='Wolfratshausen' where d01gsi='0240564';
> UPDATE 1
> sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
>  currtid2  
> -----------
>  (29036,7)
> 
> Now I go and pick up a coffe in our kitchen and check again:
> 
> sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
>   currtid2  
> ------------
>  (29036,11)
> 
> i.e. the function now only returns it argument. and not the new CTID
> anymore.
> 
> Why is this? And what triggers exactly that the old CTID can't be used
> anymore?

It is probably the fault of your coffee.

Another explanation could be that the HOT chain was pruned while you were away.

Yours,
Laurenz Albe

  3. Re: lifetime of the old CTID

    Matthias Apitz <guru@unixarea.de> — 2022-07-05T09:06:09Z

    El día Dienstag, Juli 05, 2022 a las 10:40:40 +0200, Laurenz Albe escribió:

> On Tue, 2022-07-05 at 09:51 +0200, Matthias Apitz wrote:
> > We're using the SQL function currtid2() to get the new CTID of a row
> > when this was UPDATEd.
> > 
> > Investigating cases of failing updates, it turns out that the old CTID
> > has only a limited lifetime; one can check this with SQL:
> > 
> > sisis=# select ctid, d01gsi from d01buch where d01gsi = '0240564';
> >     ctid    |           d01gsi            
> > ------------+-----------------------------
> >  (29036,11) | 0240564
> > 
> > now I update the row and afterwards pick up the new CTID based on the
> > old one (29036,11):
> > 
> > sisis=# UPDATE d01buch set d01ort='Wolfratshausen' where d01gsi='0240564';
> > UPDATE 1
> > sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
> >  currtid2  
> > -----------
> >  (29036,7)
> > 
> > Now I go and pick up a coffe in our kitchen and check again:
> > 
> > sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
> >   currtid2  
> > ------------
> >  (29036,11)
> > 
> > i.e. the function now only returns it argument. and not the new CTID
> > anymore.
> > 
> > Why is this? And what triggers exactly that the old CTID can't be used
> > anymore?
> 
> It is probably the fault of your coffee.

Correct, the coffee machine is broken and never does show when the
milk powder is empty already :-)

> Another explanation could be that the HOT chain was pruned while you were away.

What exactly means "the HOT chain was pruned"? What is the HOT chain?

Thanks

	matthias

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

  4. Re: lifetime of the old CTID

    Matthias Apitz <guru@unixarea.de> — 2022-07-05T10:22:01Z

    El día Dienstag, Juli 05, 2022 a las 10:40:40 +0200, Laurenz Albe escribió:

> On Tue, 2022-07-05 at 09:51 +0200, Matthias Apitz wrote:
> > We're using the SQL function currtid2() to get the new CTID of a row
> > when this was UPDATEd.
> > 
> > Investigating cases of failing updates, it turns out that the old CTID
> > has only a limited lifetime; one can check this with SQL:
> > 
> > sisis=# select ctid, d01gsi from d01buch where d01gsi = '0240564';
> >     ctid    |           d01gsi            
> > ------------+-----------------------------
> >  (29036,11) | 0240564
> > 
> > now I update the row and afterwards pick up the new CTID based on the
> > old one (29036,11):
> > 
> > sisis=# UPDATE d01buch set d01ort='Wolfratshausen' where d01gsi='0240564';
> > UPDATE 1
> > sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
> >  currtid2  
> > -----------
> >  (29036,7)
> > 
> > Now I go and pick up a coffe in our kitchen and check again:
> > 
> > sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
> >   currtid2  
> > ------------
> >  (29036,11)
> > 
> > i.e. the function now only returns it argument. and not the new CTID
> > anymore.
> > 
> > Why is this? And what triggers exactly that the old CTID can't be used
> > anymore?
> 
> It is probably the fault of your coffee.
> 
> Another explanation could be that the HOT chain was pruned while you were away.

I've read now about HOT and understand that autovacuum will prune the
HOT chain. But also a simple SELECT seems to remove it:

sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
 currtid2  
-----------
 (29036,7)


sisis=# select ctid, d01gsi from d01buch where d01gsi = '0240564';
   ctid    |           d01gsi            
-----------+-----------------------------
 (29036,7) | 0240564                    


sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
  currtid2  
------------
 (29036,11)


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

  5. Re: lifetime of the old CTID

    Laurenz Albe <laurenz.albe@cybertec.at> — 2022-07-05T14:17:41Z

    On Tue, 2022-07-05 at 12:22 +0200, Matthias Apitz wrote:
> El día Dienstag, Juli 05, 2022 a las 10:40:40 +0200, Laurenz Albe escribió:
> > On Tue, 2022-07-05 at 09:51 +0200, Matthias Apitz wrote:
> > > We're using the SQL function currtid2() to get the new CTID of a row
> > > when this was UPDATEd.
> > > 
> > > Investigating cases of failing updates, it turns out that the old CTID
> > > has only a limited lifetime; one can check this with SQL:
> > > 
> > > sisis=# select ctid, d01gsi from d01buch where d01gsi = '0240564';
> > >     ctid    |           d01gsi            
> > > ------------+-----------------------------
> > >  (29036,11) | 0240564
> > > 
> > > now I update the row and afterwards pick up the new CTID based on the
> > > old one (29036,11):
> > > 
> > > sisis=# UPDATE d01buch set d01ort='Wolfratshausen' where d01gsi='0240564';
> > > UPDATE 1
> > > sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
> > >  currtid2  
> > > -----------
> > >  (29036,7)
> > > 
> > > Now I go and pick up a coffe in our kitchen and check again:
> > > 
> > > sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
> > >   currtid2  
> > > ------------
> > >  (29036,11)
> > > 
> > > i.e. the function now only returns it argument. and not the new CTID
> > > anymore.
> > > 
> > > Why is this? And what triggers exactly that the old CTID can't be used
> > > anymore?
> > 
> > Another explanation could be that the HOT chain was pruned while you were away.
> 
> I've read now about HOT and understand that autovacuum will prune the
> HOT chain. But also a simple SELECT seems to remove it:
> 
> sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
>  currtid2  
> -----------
>  (29036,7)
> 
> 
> sisis=# select ctid, d01gsi from d01buch where d01gsi = '0240564';
>    ctid    |           d01gsi            
> -----------+-----------------------------
>  (29036,7) | 0240564                    
> 
> 
> sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
>   currtid2  
> ------------
>  (29036,11)

Right.  Heap-Only tuples can also vanish without autovacuum; that is why I
suspected it might have been that.

Yours,
Laurenz Albe

  6. Re: lifetime of the old CTID

    Matthias Apitz <guru@unixarea.de> — 2022-07-05T15:52:04Z

    El día martes, julio 05, 2022 a las 04:17:41p. m. +0200, Laurenz Albe escribió:

> > > Another explanation could be that the HOT chain was pruned while you were away.
> > 
> > I've read now about HOT and understand that autovacuum will prune the
> > HOT chain. But also a simple SELECT seems to remove it:
> > 
> > sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
> >  currtid2  
> > -----------
> >  (29036,7)
> > 
> > 
> > sisis=# select ctid, d01gsi from d01buch where d01gsi = '0240564';
> >    ctid    |           d01gsi            
> > -----------+-----------------------------
> >  (29036,7) | 0240564                    
> > 
> > 
> > sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
> >   currtid2  
> > ------------
> >  (29036,11)
> 
> Right.  Heap-Only tuples can also vanish without autovacuum; that is why I
> suspected it might have been that.

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

Thanks

	matthias

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

  7. Re: lifetime of the old CTID

    Laurenz Albe <laurenz.albe@cybertec.at> — 2022-07-05T16:19:18Z

    On Tue, 2022-07-05 at 17:52 +0200, Matthias Apitz wrote:
> El día martes, julio 05, 2022 a las 04:17:41p. m. +0200, Laurenz Albe escribió:
> 
> > > > Another explanation could be that the HOT chain was pruned while you were away.
> > > 
> > > I've read now about HOT and understand that autovacuum will prune the
> > > HOT chain. But also a simple SELECT seems to remove it:
> > > 
> > > sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
> > >  currtid2  
> > > -----------
> > >  (29036,7)
> > > 
> > > 
> > > sisis=# select ctid, d01gsi from d01buch where d01gsi = '0240564';
> > >    ctid    |           d01gsi            
> > > -----------+-----------------------------
> > >  (29036,7) | 0240564                    
> > > 
> > > 
> > > sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
> > >   currtid2  
> > > ------------
> > >  (29036,11)
> > 
> > Right.  Heap-Only tuples can also vanish without autovacuum; that is why I
> > suspected it might have been that.
> 
> 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.

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

  8. Re: lifetime of the old CTID

    Matthias Apitz <guru@unixarea.de> — 2022-07-06T05:35:26Z

    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

  9. Re: lifetime of the old CTID

    Christophe Pettus <xof@thebuild.com> — 2022-07-06T05:44:23Z

    
> 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 particular guarantees about ctid values being retained.

I'd suggest having a proper primary key column on the table, and using that instead.

  10. Re: lifetime of the old CTID

    Andreas Kretschmer <andreas@a-kretschmer.de> — 2022-07-06T05:54:28Z

    
Am 06.07.22 um 07:44 schrieb Christophe Pettus:
>
>> 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 particular guarantees about ctid values being retained.
>
> I'd suggest having a proper primary key column on the table, and using that instead.


100% ACK.

Andreas


-- 
Andreas Kretschmer
Technical Account Manager (TAM)
www.enterprisedb.com

  11. Re: lifetime of the old CTID

    Andreas Kretschmer <andreas@a-kretschmer.de> — 2022-07-06T05:58:19Z

    
Am 06.07.22 um 07:54 schrieb Andreas Kretschmer:
>
>
> Am 06.07.22 um 07:44 schrieb Christophe Pettus:
>>
>>> 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 particular guarantees about ctid values being retained.
>>
>> I'd suggest having a proper primary key column on the table, and 
>> using that instead.
>
>
> 100% ACK.
>
> Andreas
>
>

it reminds me somehow on how people used he OID in old times - and now 
we removed the OID completely.

Andreas

-- 
Andreas Kretschmer
Technical Account Manager (TAM)
www.enterprisedb.com

  12. Re: lifetime of the old CTID

    Matthias Apitz <guru@unixarea.de> — 2022-07-06T06:18:42Z

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


	matthias

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

  13. Re: lifetime of the old CTID

    Laurenz Albe <laurenz.albe@cybertec.at> — 2022-07-06T06:41:58Z

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

  14. Re: lifetime of the old CTID

    Karsten Hilbert <karsten.hilbert@gmx.net> — 2022-07-06T09:45:14Z

    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

  15. Re: lifetime of the old CTID

    Karsten Hilbert <karsten.hilbert@gmx.net> — 2022-07-06T09:48:08Z

    Am Wed, Jul 06, 2022 at 08:41:58AM +0200 schrieb Laurenz Albe:

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

UPDATEing conditional on XMIN not having changed might be
another solution for detecting concurrent transacations if
one is bent on using system columns for that.

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

  16. Re: lifetime of the old CTID

    Matthias Apitz <guru@unixarea.de> — 2022-07-06T12:26:00Z

    El día Mittwoch, Juli 06, 2022 a las 11:45:14 +0200, Karsten Hilbert escribió:

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

This is because of the generic, generated structure of the DB layer for
all the 400 tables. The provided pseudo-code example should only explain
from where our problem comes from. The big CURSOR is done in the DB layer by
the application calling something like

    read_where(d01buch, "WHERE d01status=4");

i.e. the application provides the table name (d01buch) and a
WHERE-clause of its interest ("WHERE d01status=4"), here all books which
are lent to patrons. This gives in the DB layer a CURSOR of say 100.000
rows of the 3.000.000 in the table. Now the application fetches row
by row and see if something should be done with the row. If so, the
DB layer must LOCK the row for update. It does so using the CTID. Of
course there is a key in the row (d01gsi, the signature of the book),
but this is not uniqu and can't be used to lock exactly this row for update.

The same problem could occure in any or the other 400 tables.

Interestingly, I tested today morning how long the new CTID can be seen
with currtid2(). I did 10 or more updates of a row and the then new CTID
could always be seen with the old CTID from the moment before the 10 updates. I even
found no way to get this tuple broken. Only deletion of the row helped
to make currtid2() loosing the relation. This is with a 14.1 server. Why
the 13.1 behaves different?

	matthias

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

  17. Re: lifetime of the old CTID

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2022-07-06T12:32:48Z

    On 2022-Jul-06, Matthias Apitz wrote:

> This gives in the DB layer a CURSOR of say 100.000 rows of the
> 3.000.000 in the table. Now the application fetches row by row and see
> if something should be done with the row. If so, the DB layer must
> LOCK the row for update. It does so using the CTID.

This is a bad idea, for reasons already explained.  The CTID is not for
user consumption.  If it breaks, as it does for you here, it's your
fault for using it.  Your rows need to have a key that you can use.  The
CTID is not it.

> Of course there is a key in the row (d01gsi, the signature of the
> book), but this is not uniqu and can't be used to lock exactly this
> row for update.

Well, you need something.  It is beginning to sound like your database
model is wrong, because it lacks sufficient keys.

> Interestingly, I tested today morning how long the new CTID can be
> seen with currtid2(). I did 10 or more updates of a row and the then
> new CTID could always be seen with the old CTID from the moment before
> the 10 updates. I even found no way to get this tuple broken. Only
> deletion of the row helped to make currtid2() loosing the relation.
> This is with a 14.1 server. Why the 13.1 behaves different?

There are implementation details that you shouldn't concern yourself
with.  Quite likely, the reason it stayed unchanged has nothing to do
with the server version, and is instead related to other things
happening in the server at the same time.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"I suspect most samba developers are already technically insane...
Of course, since many of them are Australians, you can't tell." (L. Torvalds)

  18. Re: lifetime of the old CTID

    Peter J. Holzer <hjp-pgsql@hjp.at> — 2022-07-06T13:53:54Z

    On 2022-07-06 14:26:00 +0200, Matthias Apitz wrote:
> DB layer must LOCK the row for update. It does so using the CTID. Of
> course there is a key in the row (d01gsi, the signature of the book),
> but this is not uniqu and can't be used to lock exactly this row for
> update.

If it isn't unique it is *not* a key. If your tables don't have a
primary key you should seriously rethink the data model.

        hp

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

  19. Re: lifetime of the old CTID

    Matthias Apitz <guru@unixarea.de> — 2022-07-06T14:32:35Z

    El día miércoles, julio 06, 2022 a las 03:53:54p. m. +0200, Peter J. Holzer escribió:

> On 2022-07-06 14:26:00 +0200, Matthias Apitz wrote:
> > DB layer must LOCK the row for update. It does so using the CTID. Of
> > course there is a key in the row (d01gsi, the signature of the book),
> > but this is not uniqu and can't be used to lock exactly this row for
> > update.
> 
> If it isn't unique it is *not* a key. If your tables don't have a
> primary key you should seriously rethink the data model.

This Library Management System runs already for 25++ years with nearly
the same data model. We came from INFORMIX, then Oracle, then Sybase and
since 2 years are now on PostgreSQL. There was always some serial key for
the rows (I don't remember INFORMIX, but 'rowid' on Oracle,
SYB_IDENTITY_COL on Sybase and now CTID on PostgreSQL. The error we made
two years ago was not investigating carefully enough that the CTID is
pruned even before the changed row is
deleted by AUTOVACUUM. Now we know this and will deal with the problem
above the DB layer which returns now a fixed return code when the row
can not be found with its old CTID or a new CTID can not be calculated.

Said this, we can end this thread. Re-think the data model is not an
option.

	matthias

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

  20. Re: lifetime of the old CTID

    Ilya Anfimov <ilan@tzirechnoy.com> — 2022-07-06T15:56:02Z

    On Wed, Jul 06, 2022 at 02:26:00PM +0200, Matthias Apitz wrote:
> El d??a Mittwoch, Juli 06, 2022 a las 11:45:14 +0200, Karsten Hilbert escribi??:
> 
> > Am Wed, Jul 06, 2022 at 08:18:42AM +0200 schrieb Matthias Apitz:
> > 

[skipped]

> WHERE-clause of its interest ("WHERE d01status=4"), here all books which
> are lent to patrons. This gives in the DB layer a CURSOR of say 100.000
> rows of the 3.000.000 in the table. Now the application fetches row
> by row and see if something should be done with the row. If so, the
> DB layer must LOCK the row for update. It does so using the CTID. Of
> course there is a key in the row (d01gsi, the signature of the book),
> but this is not uniqu and can't be used to lock exactly this row for update.

 Then add the primary key to the result of your function.
 The primary key for a table could easily be found in pg_constraint.

 You could even collapse it into one field and name it CTID in the resultset, if
it is strictly necessary by your logic.

  21. Multiple Indexes

    DAVID ROTH <adaptron@comcast.net> — 2022-07-06T16:06:52Z

    I understand the planner can use multiple indexes to get the best plan.
Can someone point me a paper that explains how this works.
Thanks

  22. Re: lifetime of the old CTID

    Karsten Hilbert <karsten.hilbert@gmx.net> — 2022-07-06T17:45:39Z

    > Said this, we can end this thread. Re-think the data model is not an
> option.

Why not ?

	- add a primary key to each table, say db01buch.pk
	- rename tables, say db01buch -> db01buch__real_table
	- add views, say db01buch over db1buch__real_table with "pk AS ctid"

(untested, however)

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

  23. Re: Multiple Indexes

    David Rowley <dgrowleyml@gmail.com> — 2022-07-06T18:53:22Z

    On Thu, 7 Jul 2022 at 04:07, DAVID ROTH <adaptron@comcast.net> wrote:
> I understand the planner can use multiple indexes to get the best plan.
> Can someone point me a paper that explains how this works.

I don't know of a paper, but if you're talking about using multiple
indexes to scan a single relation in order to satisfy a condition such
as; WHERE a = 5 OR b = 12; then the query planner is able to make use
of "bitmap index scans".  A bitmap index scan simply scans an index
type which supports such scans and collects a set of ctids.  For this
example, providing there's a suitable index on the "a" column and
another on the "b" column, the planner may choose to perform a bitmap
index scan on the "a" index and another on the "b" index then perform
a bitmap OR operation to obtain the intersecting ctids.  The heap of
the table can then be scanned to fetch the intersecting ctids.

A ctid is the physical (more physiological) address of a tuple the
heap of a table.

David

  24. Re: lifetime of the old CTID

    Ron <ronljohnsonjr@gmail.com> — 2022-07-06T19:33:42Z

    On 7/6/22 01:18, Matthias Apitz wrote:
[snip]
> 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.

The DBA who designed that should be flogged for pretending that 
SYB_IDENTITY_COLUMN is a primary key.

-- 
Angular momentum makes the world go 'round.

  25. Re: lifetime of the old CTID

    Matthias Apitz <guru@unixarea.de> — 2022-07-06T19:51:48Z

    El día miércoles, julio 06, 2022 a las 02:33:42p. m. -0500, Ron escribió:

> On 7/6/22 01:18, Matthias Apitz wrote:
> [snip]
> > 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.
> 
> The DBA who designed that should be flogged for pretending that
> SYB_IDENTITY_COLUMN is a primary key.

Now things are coing to blaming. Nobody said that SYB_IDENTITY_COLUMN
is a primary key, but it is uniqu to identify a row in a table once
known.

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

Peace instead of NATO!  Мир вместо НАТО!  Frieden statt NATO! ¡Paz en vez de OTAN!

  26. Re: lifetime of the old CTID

    Christophe Pettus <xof@thebuild.com> — 2022-07-06T19:56:55Z

    
> On Jul 6, 2022, at 12:51, Matthias Apitz <guru@unixarea.de> wrote:
> it is uniqu to identify a row in a table once
> known.

I think the point that we are trying to make here is that a ctid *isn't* that.  There is no guarantee, at all, at any level, that the ctid of a row will remain stable, not even between two SELECT statements.  (Although it doesn't right now, I could easily image some kind of repack logic in PostgreSQL that runs on read operations, not just write.)  It shouldn't be considered an API.  I understand that it might be painful to change to a generated primary key, but I think that will be less painful in the long run than having to stay ahead of PostgreSQL's internals.

  27. Re: lifetime of the old CTID

    Adrian Klaver <adrian.klaver@aklaver.com> — 2022-07-06T20:15:09Z

    On 7/6/22 12:51, Matthias Apitz wrote:
> El día miércoles, julio 06, 2022 a las 02:33:42p. m. -0500, Ron escribió:
> 
>> On 7/6/22 01:18, Matthias Apitz wrote:
>> [snip]
>>> 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.
>>
>> The DBA who designed that should be flogged for pretending that
>> SYB_IDENTITY_COLUMN is a primary key.
> 
> Now things are coing to blaming. Nobody said that SYB_IDENTITY_COLUMN
> is a primary key, but it is uniqu to identify a row in a table once
> known.

Which is the definition of a PRIMARY KEY. At any rate as has been noted 
repeatedly ctid is not that.

> 
> 	matthias


-- 
Adrian Klaver
adrian.klaver@aklaver.com