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
In-Reply-To: <20170316203750.d5hyt3vp33hzxlxa@alap3.anarazel.de> (Andres
	Freund's message of "Thu, 16 Mar 2017 13:37:50 -0700")
Message-ID: <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>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/25.1 (berkeley-unix)
Date: Thu, 16 Mar 2017 21:03:44 +0000
MIME-Version: 1.0
Content-Type: text/plain
Precedence: bulk
Sender: pgsql-bugs-owner@postgresql.org

>>>>> "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