Re: recovering from "found xmin ... from before relfrozenxid ..." - Mailing list pgsql-hackers

From Robert Haas
Subject Re: recovering from "found xmin ... from before relfrozenxid ..."
Date
Msg-id CA+TgmobfJ8CkabKJZ-1FGfvbSz+b8bBX807Y6hHEtVfzVe+g6A@mail.gmail.com
Whole thread Raw
In response to Re: recovering from "found xmin ... from before relfrozenxid ..."  (Andres Freund <andres@anarazel.de>)
Responses Re: recovering from "found xmin ... from before relfrozenxid ..."
List pgsql-hackers
On Wed, Jul 15, 2020 at 11:41 AM Andres Freund <andres@anarazel.de> wrote:
> > Do you have a reason for believing that INSERT ... DELETE is going to
> > be better than UPDATE? It seems to me that either way you can end up
> > with a deleted and thus invisible tuple that you still can't get rid
> > of.
>
> None of the "new" checks around freezing would apply to deleted
> tuples. So we shouldn't fail with an error like $subject.

It can definitely happen at least transiently:

S1:
rhaas=# create table wubble (a int, b text);
CREATE TABLE
rhaas=# insert into wubble values (1, 'glumpf');
INSERT 0 1

S2:
rhaas=# begin transaction isolation level repeatable read;
BEGIN
rhaas=*# select * from wubble;
 a |   b
---+--------
 1 | glumpf
(1 row)

S1:
rhaas=# delete from wubble;
DELETE 1
rhaas=# update pg_class set relfrozenxid =
(relfrozenxid::text::integer + 1000000)::text::xid where relname =
'wubble';
UPDATE 1
rhaas=# vacuum verbose wubble;
INFO:  vacuuming "public.wubble"
ERROR:  found xmin 528 from before relfrozenxid 1000527
CONTEXT:  while scanning block 0 of relation "public.wubble"

S2:
rhaas=*# commit;
COMMIT

S1:
rhaas=# vacuum verbose wubble;
INFO:  vacuuming "public.wubble"
INFO:  "wubble": removed 1 row versions in 1 pages
INFO:  "wubble": found 1 removable, 0 nonremovable row versions in 1
out of 1 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 531
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  "wubble": truncated 1 to 0 pages
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO:  vacuuming "pg_toast.pg_toast_16415"
INFO:  index "pg_toast_16415_index" now contains 0 row versions in 1 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  "pg_toast_16415": found 0 removable, 0 nonremovable row
versions in 0 out of 0 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 532
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM

I see your point, though: the tuple has to be able to survive
HOT-pruning in order to cause a problem when we check whether it needs
freezing.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company



pgsql-hackers by date:

Previous
From: Dave Cramer
Date:
Subject: Re: Binary support for pgoutput plugin
Next
From: Georgios
Date:
Subject: Re: Include access method in listTables output