|
Ok... sounds not good all in all.
Appreciate your help!
Thanks!
From: Laurenz Albe <laurenz.albe@xxxxxxxxxxx>
Sent: Wednesday, March 29, 2023 5:53 PM To: Sebastien Flaesch <sebastien.flaesch@xxxxxxx>; Kirk Wolak <wolakk@xxxxxxxxx> Cc: Geoff Winkless <pgsqladmin@xxxxxxxx>; pgsql-general <pgsql-general@xxxxxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxx> > > 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. In short: don't use the CTID to identify a row. Yours, Laurenz Albe |
