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@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub