From: Andres Freund <andres@anarazel.de>
To: Andrew Gierth <andrew@tao11.riddles.org.uk>
Cc: pgsql-bugs@postgresql.org
Subject: Re: BUG #14057: vacuum setting reltuples=0 for tables with >0 tuples
Date: Thu, 16 Mar 2017 14:08:02 -0700
Message-ID: <20170316210802.ioaflzbc3igkk2nc@alap3.anarazel.de> (raw)
In-Reply-To: <874lytj697.fsf@news-spur.riddles.org.uk>
References: <20160331103739.8956.94469@wrigleys.postgresql.org>
<20160331104129.GG23562@awork2.anarazel.de>
<87y3w53est.fsf@news-spur.riddles.org.uk>
<20170316203750.d5hyt3vp33hzxlxa@alap3.anarazel.de>
<874lytj697.fsf@news-spur.riddles.org.uk>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-bugs>
On 2017-03-16 21:03:44 +0000, Andrew Gierth wrote:
> >>>>> "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;
> }
Hm, should we prevent autovacuum/analyze from running on the table?
> 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.
Seems like a good thing to include in the tree. I'd be ok with just
including the simpler version in the relevant branches.
- Andres
--
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: andres@anarazel.de, andrew@tao11.riddles.org.uk
Subject: Re: BUG #14057: vacuum setting reltuples=0 for tables with >0 tuples
In-Reply-To: <20170316210802.ioaflzbc3igkk2nc@alap3.anarazel.de>
* 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