Horton
Menu

Re: Using CTID system column as a "temporary" primary key

Laurenz Albe <laurenz.albe@cybertec.at>

View thread
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Kirk Wolak <wolakk@gmail.com>, Sebastien Flaesch <sebastien.flaesch@4js.com>
Cc: Geoff Winkless <pgsqladmin@geoff.dj>, pgsql-general <pgsql-general@lists.postgresql.org>
Date: 2023-03-29T11:08:34Z
Lists: pgsql-general 
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