El día Wednesday, June 01, 2022 a las 09:46:17AM -0400, Tom Lane escribió: > ... > > > Is this function currtid2() not meant to be used in ESQL/C? Or did we > > something wrong in ESQL/C? > > This is not about currtid2, this is a fundamental misunderstanding > of how ECPG works. You can only inject data values into ordinary > EXEC SQL commands. I think you could handle this as > > EXEC SQL SELECT currtid2(:table ::text, :oldCTID ::tid) INTO :newCTID; > > ... Hello Tom, We came accross cases where the above SELECT returns as :newCTID the same as the :oldCTID. The :oldCTID was picked up with FETCH from the CURSOR and before locking/updating the row in question we're now checking if its CTID has changed meanwhile we're cycling though the CURSOR. In some cases the CTID is returned as unchanged but a SELECT for UPDATE fails with the CTID. I have here an example of the ESQL/C log: ecpg_execute on line 3480: query: select currtid2 ( 'd01buch' :: text , $1 :: tid ); with 1 parameter(s) on connection sisis ecpg_execute on line 3480: using PQexecParams ecpg_free_params on line 3480: parameter 1 = (671803,22) ecpg_process_output on line 3480: correctly got 1 tuples with 1 fields ecpg_get_data on line 3480: RESULT: (671803,22) offset: 80; array: no ecpg_execute on line 2535: query: declare hc_d01buch cursor for SELECT * FROM d01buch WHERE ctid = $1 FOR UPDATE; with 1 parameter(s) on connection sisis ecpg_execute on line 2535: using PQexecParams ecpg_free_params on line 2535: parameter 1 = (671803,22) ecpg_process_output on line 2535: OK: DECLARE CURSOR ecpg_execute on line 2540: query: fetch hc_d01buch; with 0 parameter(s) on connection sisis ecpg_execute on line 2540: using PQexec ecpg_process_output on line 2540: correctly got 0 tuples with 78 fields raising sqlcode 100 on line 2540: no data found on line 2540 Why is currtid2() returning the old CTID? Looking from another SQL session the CITD of the row is indeed (671803,23), i.e. changed. Thanks matthias -- Matthias Apitz, ✉ guru@xxxxxxxxxxx, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
