Thread: lifetime of the old CTID
Hello,
We're using the SQL function currtid2() to get the new CTID of a row
when this was UPDATEd.
Investigating cases of failing updates, it turns out that the old CTID
has only a limited lifetime; one can check this with SQL:
sisis=# select ctid, d01gsi from d01buch where d01gsi = '0240564';
ctid | d01gsi
------------+-----------------------------
(29036,11) | 0240564
now I update the row and afterwards pick up the new CTID based on the
old one (29036,11):
sisis=# UPDATE d01buch set d01ort='Wolfratshausen' where d01gsi='0240564';
UPDATE 1
sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
currtid2
-----------
(29036,7)
Now I go and pick up a coffe in our kitchen and check again:
sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
currtid2
------------
(29036,11)
i.e. the function now only returns it argument. and not the new CTID
anymore.
Why is this? And what triggers exactly that the old CTID can't be used
anymore?
Thanks
matthias
--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
On Tue, 2022-07-05 at 09:51 +0200, Matthias Apitz wrote:
> We're using the SQL function currtid2() to get the new CTID of a row
> when this was UPDATEd.
>
> Investigating cases of failing updates, it turns out that the old CTID
> has only a limited lifetime; one can check this with SQL:
>
> sisis=# select ctid, d01gsi from d01buch where d01gsi = '0240564';
> ctid | d01gsi
> ------------+-----------------------------
> (29036,11) | 0240564
>
> now I update the row and afterwards pick up the new CTID based on the
> old one (29036,11):
>
> sisis=# UPDATE d01buch set d01ort='Wolfratshausen' where d01gsi='0240564';
> UPDATE 1
> sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
> currtid2
> -----------
> (29036,7)
>
> Now I go and pick up a coffe in our kitchen and check again:
>
> sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
> currtid2
> ------------
> (29036,11)
>
> i.e. the function now only returns it argument. and not the new CTID
> anymore.
>
> Why is this? And what triggers exactly that the old CTID can't be used
> anymore?
It is probably the fault of your coffee.
Another explanation could be that the HOT chain was pruned while you were away.
Yours,
Laurenz Albe
El día Dienstag, Juli 05, 2022 a las 10:40:40 +0200, Laurenz Albe escribió:
> On Tue, 2022-07-05 at 09:51 +0200, Matthias Apitz wrote:
> > We're using the SQL function currtid2() to get the new CTID of a row
> > when this was UPDATEd.
> >
> > Investigating cases of failing updates, it turns out that the old CTID
> > has only a limited lifetime; one can check this with SQL:
> >
> > sisis=# select ctid, d01gsi from d01buch where d01gsi = '0240564';
> > ctid | d01gsi
> > ------------+-----------------------------
> > (29036,11) | 0240564
> >
> > now I update the row and afterwards pick up the new CTID based on the
> > old one (29036,11):
> >
> > sisis=# UPDATE d01buch set d01ort='Wolfratshausen' where d01gsi='0240564';
> > UPDATE 1
> > sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
> > currtid2
> > -----------
> > (29036,7)
> >
> > Now I go and pick up a coffe in our kitchen and check again:
> >
> > sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
> > currtid2
> > ------------
> > (29036,11)
> >
> > i.e. the function now only returns it argument. and not the new CTID
> > anymore.
> >
> > Why is this? And what triggers exactly that the old CTID can't be used
> > anymore?
>
> It is probably the fault of your coffee.
Correct, the coffee machine is broken and never does show when the
milk powder is empty already :-)
> Another explanation could be that the HOT chain was pruned while you were away.
What exactly means "the HOT chain was pruned"? What is the HOT chain?
Thanks
matthias
--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
El día Dienstag, Juli 05, 2022 a las 10:40:40 +0200, Laurenz Albe escribió:
> On Tue, 2022-07-05 at 09:51 +0200, Matthias Apitz wrote:
> > We're using the SQL function currtid2() to get the new CTID of a row
> > when this was UPDATEd.
> >
> > Investigating cases of failing updates, it turns out that the old CTID
> > has only a limited lifetime; one can check this with SQL:
> >
> > sisis=# select ctid, d01gsi from d01buch where d01gsi = '0240564';
> > ctid | d01gsi
> > ------------+-----------------------------
> > (29036,11) | 0240564
> >
> > now I update the row and afterwards pick up the new CTID based on the
> > old one (29036,11):
> >
> > sisis=# UPDATE d01buch set d01ort='Wolfratshausen' where d01gsi='0240564';
> > UPDATE 1
> > sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
> > currtid2
> > -----------
> > (29036,7)
> >
> > Now I go and pick up a coffe in our kitchen and check again:
> >
> > sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
> > currtid2
> > ------------
> > (29036,11)
> >
> > i.e. the function now only returns it argument. and not the new CTID
> > anymore.
> >
> > Why is this? And what triggers exactly that the old CTID can't be used
> > anymore?
>
> It is probably the fault of your coffee.
>
> Another explanation could be that the HOT chain was pruned while you were away.
I've read now about HOT and understand that autovacuum will prune the
HOT chain. But also a simple SELECT seems to remove it:
sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
currtid2
-----------
(29036,7)
sisis=# select ctid, d01gsi from d01buch where d01gsi = '0240564';
ctid | d01gsi
-----------+-----------------------------
(29036,7) | 0240564
sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
currtid2
------------
(29036,11)
--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
On Tue, 2022-07-05 at 12:22 +0200, Matthias Apitz wrote:
> El día Dienstag, Juli 05, 2022 a las 10:40:40 +0200, Laurenz Albe escribió:
> > On Tue, 2022-07-05 at 09:51 +0200, Matthias Apitz wrote:
> > > We're using the SQL function currtid2() to get the new CTID of a row
> > > when this was UPDATEd.
> > >
> > > Investigating cases of failing updates, it turns out that the old CTID
> > > has only a limited lifetime; one can check this with SQL:
> > >
> > > sisis=# select ctid, d01gsi from d01buch where d01gsi = '0240564';
> > > ctid | d01gsi
> > > ------------+-----------------------------
> > > (29036,11) | 0240564
> > >
> > > now I update the row and afterwards pick up the new CTID based on the
> > > old one (29036,11):
> > >
> > > sisis=# UPDATE d01buch set d01ort='Wolfratshausen' where d01gsi='0240564';
> > > UPDATE 1
> > > sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
> > > currtid2
> > > -----------
> > > (29036,7)
> > >
> > > Now I go and pick up a coffe in our kitchen and check again:
> > >
> > > sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
> > > currtid2
> > > ------------
> > > (29036,11)
> > >
> > > i.e. the function now only returns it argument. and not the new CTID
> > > anymore.
> > >
> > > Why is this? And what triggers exactly that the old CTID can't be used
> > > anymore?
> >
> > Another explanation could be that the HOT chain was pruned while you were away.
>
> I've read now about HOT and understand that autovacuum will prune the
> HOT chain. But also a simple SELECT seems to remove it:
>
> sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
> currtid2
> -----------
> (29036,7)
>
>
> sisis=# select ctid, d01gsi from d01buch where d01gsi = '0240564';
> ctid | d01gsi
> -----------+-----------------------------
> (29036,7) | 0240564
>
>
> sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
> currtid2
> ------------
> (29036,11)
Right. Heap-Only tuples can also vanish without autovacuum; that is why I
suspected it might have been that.
Yours,
Laurenz Albe
El día martes, julio 05, 2022 a las 04:17:41p. m. +0200, Laurenz Albe escribió:
> > > Another explanation could be that the HOT chain was pruned while you were away.
> >
> > I've read now about HOT and understand that autovacuum will prune the
> > HOT chain. But also a simple SELECT seems to remove it:
> >
> > sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
> > currtid2
> > -----------
> > (29036,7)
> >
> >
> > sisis=# select ctid, d01gsi from d01buch where d01gsi = '0240564';
> > ctid | d01gsi
> > -----------+-----------------------------
> > (29036,7) | 0240564
> >
> >
> > sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
> > currtid2
> > ------------
> > (29036,11)
>
> Right. Heap-Only tuples can also vanish without autovacuum; that is why I
> suspected it might have been that.
Hi Laurenz, ist there any way to keep/freeze such tuples until the run
of the next autovaccum? Some kind of config value in 13.x or 14.x? Or
even a code change for this? We compile on Linux from the sources and
have already some code changes in place...
Thanks
matthias
--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
On Tue, 2022-07-05 at 17:52 +0200, Matthias Apitz wrote:
> El día martes, julio 05, 2022 a las 04:17:41p. m. +0200, Laurenz Albe escribió:
>
> > > > Another explanation could be that the HOT chain was pruned while you were away.
> > >
> > > I've read now about HOT and understand that autovacuum will prune the
> > > HOT chain. But also a simple SELECT seems to remove it:
> > >
> > > sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
> > > currtid2
> > > -----------
> > > (29036,7)
> > >
> > >
> > > sisis=# select ctid, d01gsi from d01buch where d01gsi = '0240564';
> > > ctid | d01gsi
> > > -----------+-----------------------------
> > > (29036,7) | 0240564
> > >
> > >
> > > sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
> > > currtid2
> > > ------------
> > > (29036,11)
> >
> > Right. Heap-Only tuples can also vanish without autovacuum; that is why I
> > suspected it might have been that.
>
> Hi Laurenz, ist there any way to keep/freeze such tuples until the run
> of the next autovaccum? Some kind of config value in 13.x or 14.x? Or
> even a code change for this? We compile on Linux from the sources and
> have already some code changes in place...
That should be possible, but why do you want that? Perhaps there is a better
solution for your problem.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
El día martes, julio 05, 2022 a las 06:19:18p. m. +0200, Laurenz Albe escribió:
> > Hi Laurenz, ist there any way to keep/freeze such tuples until the run
> > of the next autovaccum? Some kind of config value in 13.x or 14.x? Or
> > even a code change for this? We compile on Linux from the sources and
> > have already some code changes in place...
>
> That should be possible, but why do you want that? Perhaps there is a better
> solution for your problem.
Here you can read the overall picture of the problem. Thanks in advance
for your time reading it.
The logic in the application in this case here goes like this:
In a table 'd01buch' (the table of all book items) a big list is
made with a call to the DB layer and this list is fetched row
by row (i.e. book by book) and each item is analyzed in other tables
if certain criteria are matched and if so the row is locked
and some of its columns get updated:
read_where(d01buch, where ...);
while ( fetch()==OK ) {
...
if (....) {
start_transaction();
rewrite_actual_row();
end_transaction();
}
}
The above visible C-functions are entries in a generic DB layer
which offers the same functionality for some 400 tables, written in
ESQL/C, and can be compiled with smaller changes by the C-preprocessor
for the DBS Oracle, Sybase and PostgreSQL.
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 ...
The ctid is the value from the time when the big CURSOR was made in read_where().
Its size is normally some 100000 rows and the above while loop takes it time,
normally some 10 to 20 minutes. All this (and many other things like this) run
every night as some house keeping work of a Library Management System.
In parallel are other processes working in the same DB on the same tables,
esp. some OPAC (a web based Online Public Access Catalogue system) with which
library users (patrons) can make reservation of books, pay fees etc.
It happens that rows from the above table get modified within
the time frame of 10 to 20 minutes which means that the ctid changes
and the above SELECT * FROM d01buch WHERE ctid = ... FOR UPDATE fails because
the ctid is what is in the (old) CURSOR. We tried to use currtid2() to get
the current ctid based on the old one, but as we see this does not help
always.
Thanks
matthias
--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
> On Jul 5, 2022, at 22:35, Matthias Apitz <guru@unixarea.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. Thectid is never intended to be stable in the database, as you have discovered. There are really no particular guaranteesabout ctid values being retained. I'd suggest having a proper primary key column on the table, and using that instead.
Am 06.07.22 um 07:44 schrieb Christophe Pettus: > >> On Jul 5, 2022, at 22:35, Matthias Apitz <guru@unixarea.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 particularguarantees 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
Am 06.07.22 um 07:54 schrieb Andreas Kretschmer: > > > Am 06.07.22 um 07:44 schrieb Christophe Pettus: >> >>> On Jul 5, 2022, at 22:35, Matthias Apitz <guru@unixarea.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 > > it reminds me somehow on how people used he OID in old times - and now we removed the OID completely. Andreas -- Andreas Kretschmer Technical Account Manager (TAM) www.enterprisedb.com
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@unixarea.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
particularguarantees 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@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
On Wed, 2022-07-06 at 08:18 +0200, Matthias Apitz wrote: > 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@unixarea.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. > > 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. No, there isn't. I said that you might be able to mutilate the PostgreSQL code so that it does that, but I think that would be a bad idea. Using the primary key is the proper solution. To be safe from concurrent modifications, use a logic like in this pseudo-code: FOR b IN SELECT pk, other_columns FROM books WHERE condition UPDATE books SET ... WHERE pk = ... AND condition Checking the condition again on the inner UPDATE will detect concurrent data modifications. If the UPDATE changes nothing, then a book has been removed or updated by a concurrent transaction, and you ignore it. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Am Wed, Jul 06, 2022 at 08:18:42AM +0200 schrieb Matthias Apitz: > > 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 particularguarantees 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. Christopher suggested to *use* the primary key, not to *add* one. You said that there *is* a primary key. So, more thought/explanation would need to go into why that cannot be used. Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Am Wed, Jul 06, 2022 at 08:41:58AM +0200 schrieb Laurenz Albe: > Using the primary key is the proper solution. To be safe from concurrent > modifications, use a logic like in this pseudo-code: > > FOR b IN SELECT pk, other_columns FROM books WHERE condition > UPDATE books SET ... WHERE pk = ... AND condition > > Checking the condition again on the inner UPDATE will detect concurrent > data modifications. If the UPDATE changes nothing, then a book has been > removed or updated by a concurrent transaction, and you ignore it. UPDATEing conditional on XMIN not having changed might be another solution for detecting concurrent transacations if one is bent on using system columns for that. Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
El día Mittwoch, Juli 06, 2022 a las 11:45:14 +0200, Karsten Hilbert escribió:
> Am Wed, Jul 06, 2022 at 08:18:42AM +0200 schrieb Matthias Apitz:
>
> > > 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
particularguarantees 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.
>
> Christopher suggested to *use* the primary key, not to *add*
> one.
>
> You said that there *is* a primary key.
>
> So, more thought/explanation would need to go into why that
> cannot be used.
This is because of the generic, generated structure of the DB layer for
all the 400 tables. The provided pseudo-code example should only explain
from where our problem comes from. The big CURSOR is done in the DB layer by
the application calling something like
read_where(d01buch, "WHERE d01status=4");
i.e. the application provides the table name (d01buch) and a
WHERE-clause of its interest ("WHERE d01status=4"), here all books which
are lent to patrons. This gives in the DB layer a CURSOR of say 100.000
rows of the 3.000.000 in the table. Now the application fetches row
by row and see if something should be done with the row. If so, the
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.
The same problem could occure in any or the other 400 tables.
Interestingly, I tested today morning how long the new CTID can be seen
with currtid2(). I did 10 or more updates of a row and the then new CTID
could always be seen with the old CTID from the moment before the 10 updates. I even
found no way to get this tuple broken. Only deletion of the row helped
to make currtid2() loosing the relation. This is with a 14.1 server. Why
the 13.1 behaves different?
matthias
--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
On 2022-Jul-06, Matthias Apitz wrote: > This gives in the DB layer a CURSOR of say 100.000 rows of the > 3.000.000 in the table. Now the application fetches row by row and see > if something should be done with the row. If so, the DB layer must > LOCK the row for update. It does so using the CTID. This is a bad idea, for reasons already explained. The CTID is not for user consumption. If it breaks, as it does for you here, it's your fault for using it. Your rows need to have a key that you can use. The CTID is not it. > 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. Well, you need something. It is beginning to sound like your database model is wrong, because it lacks sufficient keys. > Interestingly, I tested today morning how long the new CTID can be > seen with currtid2(). I did 10 or more updates of a row and the then > new CTID could always be seen with the old CTID from the moment before > the 10 updates. I even found no way to get this tuple broken. Only > deletion of the row helped to make currtid2() loosing the relation. > This is with a 14.1 server. Why the 13.1 behaves different? There are implementation details that you shouldn't concern yourself with. Quite likely, the reason it stayed unchanged has nothing to do with the server version, and is instead related to other things happening in the server at the same time. -- Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/ "I suspect most samba developers are already technically insane... Of course, since many of them are Australians, you can't tell." (L. Torvalds)
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.
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachment
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
On Wed, Jul 06, 2022 at 02:26:00PM +0200, Matthias Apitz wrote:
> El d??a Mittwoch, Juli 06, 2022 a las 11:45:14 +0200, Karsten Hilbert escribi??:
>
> > Am Wed, Jul 06, 2022 at 08:18:42AM +0200 schrieb Matthias Apitz:
> >
[skipped]
> WHERE-clause of its interest ("WHERE d01status=4"), here all books which
> are lent to patrons. This gives in the DB layer a CURSOR of say 100.000
> rows of the 3.000.000 in the table. Now the application fetches row
> by row and see if something should be done with the row. If so, the
> 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.
Then add the primary key to the result of your function.
The primary key for a table could easily be found in pg_constraint.
You could even collapse it into one field and name it CTID in the resultset, if
it is strictly necessary by your logic.
I understand the planner can use multiple indexes to get the best plan. Can someone point me a paper that explains how this works. Thanks
> Said this, we can end this thread. Re-think the data model is not an
> option.
Why not ?
- add a primary key to each table, say db01buch.pk
- rename tables, say db01buch -> db01buch__real_table
- add views, say db01buch over db1buch__real_table with "pk AS ctid"
(untested, however)
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
On Thu, 7 Jul 2022 at 04:07, DAVID ROTH <adaptron@comcast.net> wrote: > I understand the planner can use multiple indexes to get the best plan. > Can someone point me a paper that explains how this works. I don't know of a paper, but if you're talking about using multiple indexes to scan a single relation in order to satisfy a condition such as; WHERE a = 5 OR b = 12; then the query planner is able to make use of "bitmap index scans". A bitmap index scan simply scans an index type which supports such scans and collects a set of ctids. For this example, providing there's a suitable index on the "a" column and another on the "b" column, the planner may choose to perform a bitmap index scan on the "a" index and another on the "b" index then perform a bitmap OR operation to obtain the intersecting ctids. The heap of the table can then be scanned to fetch the intersecting ctids. A ctid is the physical (more physiological) address of a tuple the heap of a table. David
On 7/6/22 01:18, Matthias Apitz wrote: [snip] > 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. The DBA who designed that should be flogged for pretending that SYB_IDENTITY_COLUMN is a primary key. -- Angular momentum makes the world go 'round.
El día miércoles, julio 06, 2022 a las 02:33:42p. m. -0500, Ron escribió:
> On 7/6/22 01:18, Matthias Apitz wrote:
> [snip]
> > 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.
>
> The DBA who designed that should be flogged for pretending that
> SYB_IDENTITY_COLUMN is a primary key.
Now things are coing to blaming. Nobody said that SYB_IDENTITY_COLUMN
is a primary key, but it is uniqu to identify a row in a table once
known.
matthias
--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
Peace instead of NATO! Мир вместо НАТО! Frieden statt NATO! ¡Paz en vez de OTAN!
> On Jul 6, 2022, at 12:51, Matthias Apitz <guru@unixarea.de> wrote: > it is uniqu to identify a row in a table once > known. I think the point that we are trying to make here is that a ctid *isn't* that. There is no guarantee, at all, at any level,that the ctid of a row will remain stable, not even between two SELECT statements. (Although it doesn't right now,I could easily image some kind of repack logic in PostgreSQL that runs on read operations, not just write.) It shouldn'tbe considered an API. I understand that it might be painful to change to a generated primary key, but I think thatwill be less painful in the long run than having to stay ahead of PostgreSQL's internals.
On 7/6/22 12:51, Matthias Apitz wrote: > El día miércoles, julio 06, 2022 a las 02:33:42p. m. -0500, Ron escribió: > >> On 7/6/22 01:18, Matthias Apitz wrote: >> [snip] >>> 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. >> >> The DBA who designed that should be flogged for pretending that >> SYB_IDENTITY_COLUMN is a primary key. > > Now things are coing to blaming. Nobody said that SYB_IDENTITY_COLUMN > is a primary key, but it is uniqu to identify a row in a table once > known. Which is the definition of a PRIMARY KEY. At any rate as has been noted repeatedly ctid is not that. > > matthias -- Adrian Klaver adrian.klaver@aklaver.com