From: Laurenz Albe <laurenz.albe@cybertec.at> Sent: Wednesday, March 29, 2023 5:53 PM To: Sebastien Flaesch <sebastien.flaesch@4js.com>; Kirk Wolak <wolakk@gmail.com> Cc: Geoff Winkless <pgsqladmin@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 Wed, 2023-03-29 at 14:23 +0000, Sebastien Flaesch wrote: > From: Laurenz Albe <laurenz.albe@cybertec.at> > > 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. > > Thanks for the advice about REPEATABLE READ isolation level!
... but that is only useful in a read-only scenario.
If you try to UPDATE the row in a REPEATABLE READ transaction, you will get a serialization error if there was a concurrent update.
