El día martes, julio 05, 2022 a las 10:44:23p. m. -0700, Christophe Pettus escribió: > > > > On Jul 5, 2022, at 22:35, Matthias Apitz <guru@xxxxxxxxxxx> 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. Ofc, each table has its own primary key(s), used for example for the SELECT ctid, * FROM d01buch WHERE ... As I said, we came to PostgreSQL from Sybase (and Oracle) and Sybase has for each table a so called SYB_IDENTITY_COLUMN which is static for the table and its value does not change. When we would add now to some 400 tables an additional INTEGER column (and triggers to fill this on INSERT) this would be a big change in our DB layer and migration of databases in the field. Your suggesting (thanks for it in any case) is not that easy to implement, and no option at the moment. At the moment the DB layer informs the application layer correctly when the row can not be found by the ctid for an UPDATE and the application must handle this situation correctly (logging, 2nd pass through with a new CURSOR etc.). If I understand Laurenz correctly, there seems to be a way to keep the tuple old-ctid : new-ctid for some time, at least until the ney autovacuum. matthias -- Matthias Apitz, ✉ guru@xxxxxxxxxxx, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
