Re: BUG #17212: pg_amcheck fails on checking temporary relations

On Mon, Oct 4, 2021 at 8:10 AM Mark Dilger <mark.dilger@enterprisedb.com> wrote:
> > There is another issue, that maybe should be discussed separately (or
> > this thread could be renamed to "... on checking specific relations"),
> > but the solution could be similar to that.
> > pg_amcheck also fails on checking invalid indexes, that could be created
> > legitimately by the CREATE INDEX CONCURRENTLY command.
>
> I believe this is a bug in amcheck's btree checking functions.  Peter, can you take a look?

Why do you say that? verify_nbtree.c will throw an error when called
with an invalid index -- which is what we actually see here. Obviously
that is the intended behavior, and always has been. This hasn't been a
problem before now, probably because the sample verification query in
the docs (under bt_index_check()) accounts for this directly.

Why shouldn't we expect pg_amcheck to do the same thing, at the SQL
level? It's practically the same thing as the temp table issue.
Indeed, verify_nbtree.c will throw an error on a temp table (at least
if it's from another session).

> I can also get this failure without pg_amcheck, going directly to the btree checking code.  Having already built the table as you prescribe:

> ERROR:  deadlock detected
> DETAIL:  Process 9555 waits for ShareLock on virtual transaction 5/11; blocked by process 9558.
> Process 9558 waits for ShareLock on relation 16406 of database 16384; blocked by process 9555.
> HINT:  See server log for query details.
> ERROR:  cannot check index "t_idx"
> DETAIL:  Index is not valid.

I think that the deadlock is just another symptom of the same problem.

-- 
Peter Geoghegan