Laurent, Thanks for the advice about REPEATABLE READ isolation level!
Seb ________________________________ From: Laurenz Albe <laurenz.a...@cybertec.at> Sent: Wednesday, March 29, 2023 1:08 PM To: Kirk Wolak <wol...@gmail.com>; Sebastien Flaesch <sebastien.flae...@4js.com> Cc: Geoff Winkless <pgsqlad...@geoff.dj>; pgsql-general <pgsql-general@lists.postgresql.org> Subject: Re: Using CTID system column as a "temporary" primary key EXTERNAL: Do not click links or open attachments if you do not recognize the sender. On Tue, 2023-03-28 at 14:24 -0400, Kirk Wolak wrote: > > I cringe at the thought of using CTID. And while it's probably "safe enough" > inside a single transaction. I doubt that there is much "testing" of this > concept. It is safe to assume that the CTID is stable within a single transaction only if you use REPEATABLE READ or better transaction isolation level. With READ COMMITTED, you see updated rows (and consequently changed CTID) within a single transaction. And if you use SELECT ... FOR UPDATE, you could even see a changed CTID within a single statement. So don't use CTID to identify rows unless you use REPEATABLE READ or better. Yours, Laurenz Albe
