Thread: function currtid2() in SQL and ESQL/C to get the new CTID of a row
While looking for a way to get the new CTID of an UPDATEd row, I came accros this function currtid2() which works fine on SQL with a 14.1 server: https://www.postgresql.org/message-id/Pine.BSO.4.44.0206031939050.21627-100000%40kitten.greentechnologist.org testdb=# SELECT ctid, * FROM dbctest WHERE tstint = 10; ctid | tstchar25 | tstint -------+-----------+-------- (0,9) | | 10 testdb=# UPDATE dbctest SET tstint = 11 WHERE tstint = 10; UPDATE 1 testdb=# SELECT currtid2('dbctest'::text, '(0,9)'::tid); currtid2 ---------- (0,10) checking if it (0,10) is really the new CTID: testdb=# SELECT ctid, * FROM dbctest WHERE tstint = 11; ctid | tstchar25 | tstint --------+-----------+-------- (0,10) | | 11 So far so good, but we do need this in ESQL/C. There the code looks as: EXEC SQL BEGIN DECLARE SECTION; char stmt[255]; static char newCTID[80]; EXEC SQL END DECLARE SECTION; memset(stmt, 0, sizeof(stmt)); sprintf(stmt, "currtid2('%s'::text, '%s'::tid)", table, oldCTID); fprintf(stderr, stmt); fprintf(stderr, "\n"); EXEC SQL SELECT :stmt INTO :newCTID; sprintf(stmt, "table %s oldCTID %s newCTID %s\n", table, oldCTID, newCTID); fprintf(stderr, stmt); The code runs fine but the content of the host variable is the statement itself 'currtid2('dbctest'::text, '(0,13)'::tid)' like the SELECT was just an echo function. Is this function currtid2() not meant to be used in ESQL/C? Or did we something wrong in ESQL/C? I read as well in some posting that the functions currtid2() and currtid() should be removed... Is there some better way to get the new CTID based on the known old (invalid) CTID? Thanks matthias -- Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
Matthias Apitz <guru@unixarea.de> writes:
> So far so good, but we do need this in ESQL/C. There the code looks as:
> EXEC SQL BEGIN DECLARE SECTION;
> char stmt[255];
> static char newCTID[80];
> EXEC SQL END DECLARE SECTION;
> memset(stmt, 0, sizeof(stmt));
> sprintf(stmt, "currtid2('%s'::text, '%s'::tid)", table, oldCTID);
> fprintf(stderr, stmt);
> fprintf(stderr, "\n");
> EXEC SQL SELECT :stmt INTO :newCTID;
> sprintf(stmt, "table %s oldCTID %s newCTID %s\n",
> table, oldCTID, newCTID);
> fprintf(stderr, stmt);
> The code runs fine but the content of the host variable is the statement
> itself 'currtid2('dbctest'::text, '(0,13)'::tid)' like the SELECT was
> just an echo function.
Indeed.
> 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;
If you want full-on dynamic SQL, that's also possible but you'd need
PREPARE/EXECUTE, and it wouldn't look much like this fragment.
I don't see a need for that here, though.
regards, tom lane
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
1parameter(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@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
El día miércoles, junio 22, 2022 a las 08:39:31 +0200, Matthias Apitz escribió:
> > 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:
>
We have been lucky to have the full contents of all columns of the row
in question we wanted to lock in our log files as the process saw the
row and could compare this afterwards with the actual row contents. It
turned out that the row was updated and perhaps the update transaction
not commit in the moment when the process was asking for the actual
:newCTID. We have still to investigate why this COMMIT took so long that
such a hit of two processes asking for the same row (which is like
winning the lottery on Sunday). In any case, for the moment we have no
evidence that currtid2() is the culprit.
matthias
--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub