Re: BUG #14057: vacuum setting reltuples=0 for tables with >0 tuples

On 2017-03-16 21:03:44 +0000, Andrew Gierth wrote:
> >>>>> "Andres" == Andres Freund <andres(at)anarazel(dot)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