Re: function currtid2() in SQL and ESQL/C to get the new CTID of a row

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(at)unixarea(dot)de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub