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

El día martes, mayo 24, 2022 a las 12:11:49 -0400, Tom Lane escribió:

> Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> writes:
> > It may well be that somebody deleted or updated a few rows between the time
> > the cursor was materialized and the time the 50000th row was fetched.
>
> Even without HOLD, a cursor will return a view of the data as it stood
> when the cursor was opened, just as a plain SELECT does. There is
> *plenty* of time for another session to get in there if you've been
> groveling through 50K records one at a time.

Tom, Thanks for pointing us in the right direction where to look for a
solution. The CURSOR was opened around 23:11 pm and the CTID not found
at 23:21 pm, i.e. ten minutes later. This piece of software does every
night some housekeeping work in the circulation area of our LMS (Library
Management System) and is meant to run as a standalone job (only one
process after the other). We're trying to figure out with the customer if something
else was started/running at this time between 23:11 and 23:21, to shut this
off in the future. Is it possible that the PostgreSQL 13.1 server does
something by its own to invalidate the rowid?

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