Re: lifetime of the old CTID

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