El día miércoles, julio 06, 2022 a las 03:53:54p. m. +0200, Peter J. Holzer escribió: > On 2022-07-06 14:26:00 +0200, Matthias Apitz wrote: > > DB layer must LOCK the row for update. It does so using the CTID. Of > > course there is a key in the row (d01gsi, the signature of the book), > > but this is not uniqu and can't be used to lock exactly this row for > > update. > > If it isn't unique it is *not* a key. If your tables don't have a > primary key you should seriously rethink the data model. This Library Management System runs already for 25++ years with nearly the same data model. We came from INFORMIX, then Oracle, then Sybase and since 2 years are now on PostgreSQL. There was always some serial key for the rows (I don't remember INFORMIX, but 'rowid' on Oracle, SYB_IDENTITY_COL on Sybase and now CTID on PostgreSQL. The error we made two years ago was not investigating carefully enough that the CTID is pruned even before the changed row is deleted by AUTOVACUUM. Now we know this and will deal with the problem above the DB layer which returns now a fixed return code when the row can not be found with its old CTID or a new CTID can not be calculated. Said this, we can end this thread. Re-think the data model is not an option. matthias -- Matthias Apitz, ✉ guru@xxxxxxxxxxx, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
