Ok... sounds not good all in all. Appreciate your help! Thanks! ________________________________ From: Laurenz Albe <[email protected]> Sent: Wednesday, March 29, 2023 5:53 PM To: Sebastien Flaesch <[email protected]>; Kirk Wolak <[email protected]> Cc: Geoff Winkless <[email protected]>; pgsql-general <[email protected]> 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 <[email protected]> > > 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
