From: Andrew Gierth <andrew@tao11.riddles.org.uk>
To: Andres Freund <andres@anarazel.de>
Cc: pgsql-bugs@postgresql.org
Subject: Re: BUG #14057: vacuum setting reltuples=0 for tables with >0 tuples
Date: Thu, 16 Mar 2017 21:03:44 +0000
Message-ID: <874lytj697.fsf@news-spur.riddles.org.uk> (raw)
In-Reply-To: <20170316203750.d5hyt3vp33hzxlxa@alap3.anarazel.de>
References: <20160331103739.8956.94469@wrigleys.postgresql.org>
<20160331104129.GG23562@awork2.anarazel.de>
<87y3w53est.fsf@news-spur.riddles.org.uk>
<20170316203750.d5hyt3vp33hzxlxa@alap3.anarazel.de>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-bugs>
>>>>> "Andres" == Andres Freund <andres@anarazel.de> writes:
>> I've reproduced the bug on all of them, and confirmed that this
>> fixes it on all of them. Is it worth also including the isolation
>> tester script in the changes?
Andres> Hm, I haven't seen the isolationtester test (it's not in this
Andres> thread, right?) - how fragile and how slow is it?
Oh, sorry, forgot to include that. There are two versions of the test,
because the error is slightly harder to reproduce in older branches;
this one works in 9.6 and master:
setup {
create table smalltbl
as select i as id,
'foo '||i as val
from generate_series(1,20) i;
}
setup {
vacuum analyze smalltbl;
}
teardown {
drop table smalltbl;
}
session "worker"
step "open" { BEGIN; DECLARE c1 CURSOR FOR select * from smalltbl; }
step "fetch1" { FETCH NEXT FROM c1; }
step "close" { COMMIT; }
step "stats" { select relpages, reltuples from pg_class where oid='smalltbl'::regclass; }
session "vacuumer"
step "vac" { VACUUM smalltbl; }
step "modify" {
insert into smalltbl
select max(id)+1, 'foo '||(max(id) + 1) from smalltbl;
delete from smalltbl
where id in (select min(id) from smalltbl);
}
permutation "modify" "vac" "stats"
permutation "modify" "open" "fetch1" "vac" "close" "stats"
permutation "modify" "vac" "stats"
The first and last permutations return relpages=1 reltuples=20 as
expected, but the middle one returns relpages=1 reltuples=0 when the bug
is present, due to the worker thread's cursor holding a pin on the page.
9.5 and before need a slightly more complex setup that juggles the
values of vacuum_freeze_table_age and relfrozenxid in order to get the
right code path in vacuum.
They don't seem to be fragile at all - there are no timing issues and
the results always seem to be consistent. There's no locking and runtime
is basically just how long to create/drop the table and do 3 rounds of
updates/vacuums on it.
--
Andrew (irc:RhodiumToad)
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
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: andrew@tao11.riddles.org.uk, andres@anarazel.de
Subject: Re: BUG #14057: vacuum setting reltuples=0 for tables with >0 tuples
In-Reply-To: <874lytj697.fsf@news-spur.riddles.org.uk>
* 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