From: | Christophe Pettus <xof(at)thebuild(dot)com> |
---|---|
To: | Matthias Apitz <guru(at)unixarea(dot)de> |
Cc: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: lifetime of the old CTID |
Date: | 2022-07-06 05:44:23 |
Message-ID: | 9B418AE6-EA48-4493-B1D1-566708ADC9AC@thebuild.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> On Jul 5, 2022, at 22:35, Matthias Apitz <guru(at)unixarea(dot)de> wrote:
> Internally, in the DB layer, the read_where() builds the row list matching
> the WHERE clause as a SCROLLED CURSOR of
>
> SELECT ctid, * FROM d01buch WHERE ...
>
> and each fetch() delivers the next row from this cursor. The functions
> start_transaction() and end_transaction() do what their names suggest and
> rewrite_actual_row() does a new SELECT based on the ctid of the actual row
>
> SELECT * FROM d01buch WHERE ctid = ... FOR UPDATE
> ...
> UPDATE ...
On first glance, it appears that you are using the ctid as a primary key for a row, and that's highly not-recommended. The ctid is never intended to be stable in the database, as you have discovered. There are really no particular guarantees about ctid values being retained.
I'd suggest having a proper primary key column on the table, and using that instead.
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas Kretschmer | 2022-07-06 05:54:28 | Re: lifetime of the old CTID |
Previous Message | Matthias Apitz | 2022-07-06 05:35:26 | Re: lifetime of the old CTID |