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

On Tue, 2022-05-24 at 16:44 +0200, Matthias Apitz wrote:
> We have a C-written program, written in ESQL/C, of our LMS where the logic
> crawls with FETCH through a hit list and does UPDATE on some rows which
> match certain condition. This works fine for thousands of rows every night,
> but magically sometimes it fails. I have here the part of ESQL/C logs
> when I was able to catch such a case:
>
> The hit list for the FETCHes is built with:
>
> [29858] [23.05.2022 23:11:06:419]: prepare_common on line 1825: name sid_d01buch; query: "SELECT ctid, * from d01buch WHERE d01status = 4 "
> [29858] [23.05.2022 23:11:06:419]: ecpg_execute on line 2026: query: declare d01buch_scr scroll cursor with hold for SELECT ctid, * from d01buch WHERE d01status = 4 ; with 0 parameter(s) on
> connection sisis
>
> then the CURSOR d01buch_scr is FETCHed some 59537 times, some of the
> rows were updated and here is the failing situation with a good FETCH of
> the row:

I cannot understand many of your log messages, but it seems quite clear that
you are declaring a WITH HOLD cursor.

Such cursors are materialized when the transactoin that creates the cursor
commits, so the result set is "frozen" and does no longer reflect the current
state of the table.

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.

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