Re: existing row not found by SELECT ... WHERE CTID = ? - Mailing list pgsql-general

From Laurenz Albe
Subject Re: existing row not found by SELECT ... WHERE CTID = ?
Date
Msg-id a4128a144406c3cd730fbc442d71ba5afe31af4f.camel@cybertec.at
Whole thread Raw
In response to existing row not found by SELECT ... WHERE CTID = ?  (Matthias Apitz <guru@unixarea.de>)
List pgsql-general
On Wed, 2022-05-25 at 14:27 +0200, Christoph Moench-Tegeder wrote:
> ## Matthias Apitz (guru@unixarea.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



pgsql-general by date:

Previous
From: Färber, Franz-Josef (StMUK)
Date:
Subject: Extension pg_trgm, permissions and pg_dump order
Next
From: "David G. Johnston"
Date:
Subject: Re: cast to domain with default collation issue.