On Wed, Jul 15, 2020 at 11:41 AM Andres Freund <and...@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
