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

>>>>> "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;
}
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)