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
WHEREd01status = 4 "
[29858] [23.05.2022 23:11:06:419]: ecpg_execute on line 2026: query: declare d01buch_scr scroll cursor with hold for
SELECTctid, * 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:
...
[29858] [23.05.2022 23:21:21:842]: ecpg_process_output on line 2655: correctly got 1 tuples with 79 fields
[29858] [23.05.2022 23:21:21:842]: ecpg_get_data on line 2655: RESULT: (668486,20) offset: 19; array: no
[29858] [23.05.2022 23:21:21:842]: ecpg_get_data on line 2655: RESULT: 03904016 offset: 1152;
array:no
...
but when a new CURSOR should be build to UPDATE the row based
on its CTID = (668486,20), the row could not be found:
[29858] [23.05.2022 23:21:21:843]: deallocate_one on line 2494: name hs_d01buch
[29858] [23.05.2022 23:21:21:843]: prepare_common on line 2494: name hs_d01buch; query : "SELECT * FROM d01buch
WHEREctid = $1 FOR UPDATE"
[29858] [23.05.2022 23:21:21:843]: ecpg_execute on line 2526: query: declare hc_d01buch cursor for SELECT * FROM
d01buchWHERE ctid = $1 FOR UPDATE; with 1 parameter(s) on connection sisis
[29858] [23.05.2022 23:21:21:844]: ecpg_execute on line 2526: using PQexecParams
[29858] [23.05.2022 23:21:21:844]: ecpg_free_params on line 2526: parameter 1 = (668486,20)
...
and the first FETCH in the CURSOR hc_d01buch could not see any row:
104 [29858] [23.05.2022 23:21:21:844]: raising sqlcode 100 on line 2531: no data found on line 2531
Why is this? Thanks
matthias
--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
