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: ... [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 WHERE ctid = $1 FOR UPDATE" [29858] [23.05.2022 23:21:21:843]: ecpg_execute on line 2526: query: declare hc_d01buch cursor for SELECT * FROM d01buch WHERE 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@xxxxxxxxxxx, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
