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

Laurent,

Thanks for the advice about REPEATABLE READ isolation level!

Seb
________________________________
From: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
Sent: Wednesday, March 29, 2023 1:08 PM
To: Kirk Wolak <wolakk(at)gmail(dot)com>; Sebastien Flaesch <sebastien(dot)flaesch(at)4js(dot)com>
Cc: Geoff Winkless <pgsqladmin(at)geoff(dot)dj>; pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)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 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