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

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