Re: existing row not found by SELECT ... WHERE CTID = ?

On Wed, 2022-05-25 at 14:27 +0200, Christoph Moench-Tegeder wrote:
> ## Matthias Apitz (guru(at)unixarea(dot)de):
>
> > We will solve the problem now with setting the session after connect to
> >
> >    SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;
> >
> > (with an appropriate ESQL/C call). Any comments?
>
> Maybe the real question is whether it is wise to use an implementation
> artifact (ctid) to identify rows?
> The textbook approach could be row locks (SELECT ... FOR SHARE/UPDATE and
> variants) to prevent concurrent changes or optimistic locking  (and a
> primary key in any case) - but maybe you already investigated those options?

Right.

REPEATABLE READ won't help you there. True, you will see a stable snapshot
of the database inside a single transaction, but if a concurrent session has
modified the row, you will get a serialization error.
So that is not a solution.

Yours,
Laurenz Albe