Re: Using CTID system column as a "temporary" primary key
| От | Sebastien Flaesch |
|---|---|
| Тема | Re: Using CTID system column as a "temporary" primary key |
| Дата | |
| Msg-id | DBAP191MB128988C5A20F0A8714111FA6B0899@DBAP191MB1289.EURP191.PROD.OUTLOOK.COM обсуждение исходный текст |
| Ответ на | Re: Using CTID system column as a "temporary" primary key (Laurenz Albe <laurenz.albe@cybertec.at>) |
| Ответы |
Re: Using CTID system column as a "temporary" primary key
|
| Список | pgsql-general |
Laurent,
Thanks for the advice about REPEATABLE READ isolation level!
Seb
From: Laurenz Albe <laurenz.albe@cybertec.at>
Sent: Wednesday, March 29, 2023 1:08 PM
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>
Subject: Re: Using CTID system column as a "temporary" primary key
Sent: Wednesday, March 29, 2023 1:08 PM
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>
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
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
В списке pgsql-general по дате отправления: