Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Ok... sounds not good all in all.
Appreciate your help!
Thanks!

From: Laurenz Albe <laurenz.albe@xxxxxxxxxxx>
Sent: Wednesday, March 29, 2023 5:53 PM
To: Sebastien Flaesch <sebastien.flaesch@xxxxxxx>; Kirk Wolak <wolakk@xxxxxxxxx>
Cc: Geoff Winkless <pgsqladmin@xxxxxxxx>; pgsql-general <pgsql-general@xxxxxxxxxxxxxxxxxxxx>
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.albe@xxxxxxxxxxx>
> > 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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux