Ok... sounds not good all in all. Appreciate your help! Thanks! ________________________________ From: Laurenz Albe <laurenz.a...@cybertec.at> Sent: Wednesday, March 29, 2023 5:53 PM To: Sebastien Flaesch <sebastien.flae...@4js.com>; Kirk Wolak <wol...@gmail.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 Wed, 2023-03-29 at 14:23 +0000, Sebastien Flaesch wrote: > From: Laurenz Albe <laurenz.a...@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. In short: don't use the CTID to identify a row. Yours, Laurenz Albe
