Date: Thu, 16 Mar 2017 14:08:02 -0700
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
Message-ID: <20170316210802.ioaflzbc3igkk2nc@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>
 <874lytj697.fsf@news-spur.riddles.org.uk>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <874lytj697.fsf@news-spur.riddles.org.uk>
Precedence: bulk
Sender: pgsql-bugs-owner@postgresql.org

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