From: Wood, Dan <hexpert@amazon.com>
To: pgsql-bugs@postgresql.org <pgsql-bugs@postgresql.org>
Subject: Old row version in hot chain become visible after a freeze
Date: Thu, 31 Aug 2017 22:36:43 +0000
Message-ID: <E5711E62-8FDF-4DCA-A888-C200BF6B5742@amazon.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-bugs>
From: Wood, Dan hexpert(at)amazon(dot)com
I’ve found a bug in Postgres which causes old row versions to appear in a table. DEAD rows in a hot chain are getting frozen and becoming visible. I’ve repro’d this in both 9.6.1 and 11-devel.
The repro consists of two short psql scripts.
While the repro does an explicit VACUUM FREEZE, this bug also happens with autovacuum.
FILE: lock.sql
begin;
select id from t where id=3 for key share;
select pg_sleep(1);
update t set x=x+1 where id=3;
commit;
vacuum freeze t;
select ctid, xmin, xmax, id from t;
FILE: repro.sql
drop table t;
create table t (id int primary key, name char(3), x integer);
insert into t values (1, '111', 0);
insert into t values (3, '333', 0);
\! psql -p 5432 postgres -f lock.sql &
\! psql -p 5432 postgres -f lock.sql &
\! psql -p 5432 postgres -f lock.sql &
\! psql -p 5432 postgres -f lock.sql &
\! psql -p 5432 postgres -f lock.sql &
It’s about 50-50 whether any given run of repro.sql will produce output like:
…
ctid | xmin | xmax | id | x
-------+------+------+----+---
(0,1) | 984 | 0 | 1 | 0
(0,7) | 990 | 0 | 3 | 5
(2 rows)
ctid | xmin | xmax | id | x
-------+------+------+----+---
(0,1) | 984 | 0 | 1 | 0
(0,3) | 986 | 0 | 3 | 1 // This, and x = 2, 3 and 4 came back from the DEAD
(0,4) | 987 | 0 | 3 | 2
(0,5) | 988 | 0 | 3 | 3
(0,6) | 989 | 0 | 3 | 4
(0,7) | 990 | 0 | 3 | 5
(6 rows)
Root cause analysis: lazy_scan_heap() deletes DEAD tuples in heap_page_prune(). However, it is possible for concurrent commits/rollbacks to render a tuple DEAD by the time we reach the switch statement on HeapTupleSatisfiesVacuum(). If such a row IsHotUpdated or IsHeapOnly we can’t delete it below, and must allow a later prune to take care of it.
if (HeapTupleIsHotUpdated(&tuple) || HeapTupleIsHeapOnly(&tuple))
nkeep += 1; // Don't delete, allow later prune to delete it
else
tupgone = true; // We can delete it below
Because tupgone is false we freeze instead of deleting. Freezing a DEAD tuple makes it visible. Here is a comment in heap_prepare_freeze_tuple()
* It is assumed that the caller has checked the tuple with
* HeapTupleSatisfiesVacuum() and determined that it is not HEAPTUPLE_DEAD
* (else we should be removing the tuple, not freezing it).
It is rare that we run into a DEAD tuple in this way during a freeze. More often RECENTLY_DEAD is returned. But we did see this with a more realistic long running test and I was able to create the simplified test case above. Skipping the Freeze on a DEAD tuple that IsHotUpdated or IsHeapOnly does fix the problem. I’ve attached a patch with this fix.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugsAttachments:
[application/octet-stream] update_freeze.patch (2.0K, ../E5711E62-8FDF-4DCA-A888-C200BF6B5742@amazon.com/3-update_freeze.patch)
download | inline diff:diff --git a/src/backend/commands/vacuumlazy.c b/src/backend/commands/vacuumlazy.cindex 45b1859..0f8c9c8 100644--- a/src/backend/commands/vacuumlazy.c+++ b/src/backend/commands/vacuumlazy.c@@ -593,6 +593,7 @@ lazy_scan_heap(Relation onerel, int options, LVRelStats *vacrelstats,
OffsetNumber offnum,
maxoff;
bool tupgone,
+ tupkeep,
hastup;
int prev_dead_count;
int nfrozen;
@@ -974,6 +975,7 @@ lazy_scan_heap(Relation onerel, int options, LVRelStats *vacrelstats,
tuple.t_tableOid = RelationGetRelid(onerel);
tupgone = false;
+ tupkeep = false;
switch (HeapTupleSatisfiesVacuum(&tuple, OldestXmin, buf))
{
@@ -996,7 +998,10 @@ lazy_scan_heap(Relation onerel, int options, LVRelStats *vacrelstats,
*/
if (HeapTupleIsHotUpdated(&tuple) ||
HeapTupleIsHeapOnly(&tuple))
+ {
nkeep += 1;
+ tupkeep = true;+ }
else
tupgone = true; /* we can delete the tuple */
all_visible = false;
@@ -1050,6 +1055,7 @@ lazy_scan_heap(Relation onerel, int options, LVRelStats *vacrelstats,
* from relation.
*/
nkeep += 1;
+ tupkeep = true;
all_visible = false;
break;
case HEAPTUPLE_INSERT_IN_PROGRESS:
@@ -1081,16 +1087,19 @@ lazy_scan_heap(Relation onerel, int options, LVRelStats *vacrelstats,
hastup = true;
/*
- * Each non-removable tuple must be checked to see if it needs+ * Each non-removable tuple that we do not keep must be checked to see if it needs
* freezing. Note we already have exclusive buffer lock.
*/
- if (heap_prepare_freeze_tuple(tuple.t_data, FreezeLimit,+ if (!tupkeep)+ {+ if (heap_prepare_freeze_tuple(tuple.t_data, FreezeLimit,
MultiXactCutoff, &frozen[nfrozen],
&tuple_totally_frozen))
- frozen[nfrozen++].offset = offnum;+ frozen[nfrozen++].offset = offnum;- if (!tuple_totally_frozen)- all_frozen = false;+ if (!tuple_totally_frozen)+ all_frozen = false;+ }
}
} /* scan along page */
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: hexpert@amazon.com
Subject: Re: Old row version in hot chain become visible after a freeze
In-Reply-To: <E5711E62-8FDF-4DCA-A888-C200BF6B5742@amazon.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox