Re: brin index vacuum versus transaction snapshots

Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> wrote:
> Kevin Grittner wrote:

>> If you run `make installcheck` against a cluster with
>> default_transaction_isolation = 'repeatable read' you get one
>> failure:
>
> I am tempted to say that we should just disallow to run vacuum on
> a table containing a brin index in a transaction-snapshot
> transaction.

You do realize that the VACUUM command was not run inside an
explicit transaction, right? The only change was that in the
postgresql.conf file I set default_transaction_isolation =
'repeatable read'. That was my first step before confirming that
nothing was broken for 'serializable'; but if it doesn't pass in
'repeatable read' it's not going to pass in serializable. Anyway,
for those using serializable transactions to handle race conditions
so that they don't need to use explicit LOCK TABLE statements or
SELECT FOR UPDATE clauses, setting that as the default in
postgresql.conf is standard procedure. Are you suggesting that
those users should need to run VACUUM commands bracketed with
adjustments to that GUC, like this?:

set default_transaction_isolation = 'read committed';
vacuum [...];
reset default_transaction_isolation;

What about autovacuum?

> It is possible to silence the problem by checking for vacuum
> flags, but (without testing) I think there will be a problem
> because the snapshot is acquired too early and it is possible for
> concurrent transactions to insert tuples in the table that the
> summarizing scan will not see, which will cause the index to
> become effectively corrupt.

If that's true, it sounds like vacuum of BRIN indexes might be
broken in a pretty fundamental way. Shouldn't it be using a
snapshot that lets it see everything past the global xmin
threshold, and let the *users* of the index ignore those tuples
that are not visible to *them*? From what you're saying here it
sounds like the BRIN index is failing to include in its bounds any
tuples not visible to some arbitrary snapshot?

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company