Re: lifetime of the old CTID

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)