Re: lifetime of the old CTID

Am 06.07.22 um 07:44 schrieb Christophe Pettus:
>
>> 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.

100% ACK.

Andreas

--
Andreas Kretschmer
Technical Account Manager (TAM)
www.enterprisedb.com