Re: existing row not found by SELECT ... WHERE CTID = ?

El día miércoles, mayo 25, 2022 a las 10:38:24a. m. -0400, Tom Lane escribió:

> Ravi Krishna <srkrishna(at)vivaldi(dot)net> writes:
> >> No. PostgreSQL may remove a dead row, but a dead row is by definition
> >> no longer visible, so it wouldn't be found by a query.
>
> > I am wondering whether it is a good practice to use CTID in a where
> > clause.
>
> It's fine if part of your business logic is that you don't want to allow
> concurrent updates. In this case, the OP seems to want to prevent rather
> than tolerate the concurrent update, so I don't think he needs to revisit
> the app's use of CTID.
>
> If you do need to support concurrent updates, then yeah relying on CTID
> is likely to be problematic.

Tom, we detected another issue of missing a row by its CTID in another table
where fees are stored which one(!) process cumulates in the night. The time
window between creating the CURSOR and missing the CTID is only 42
seconds and I can not imagine that any other concurrent process is updating
such fee rows at midnight. Could exist any other reason why a row changes
its CTID? Full VACUUM is not used either.

Thanks

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