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

> On Oct 4, 2021, at 2:00 AM, Alexander Lakhin <exclusion(at)gmail(dot)com> wrote:

Thank you, Alexander, for these bug reports.

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

> For example, consider the following script:
> psql -c "CREATE TABLE t(i numeric); INSERT INTO t VALUES
> (generate_series(1, 10000000));"
> psql -c "CREATE INDEX CONCURRENTLY t_idx ON t(i);" &
> pg_amcheck -a --install-missing --heapallindexed --rootdescend
> --progress || echo "FAIL"
>
> pg_amcheck fails with:
> btree index "regression.public.t_idx":
> ERROR: cannot check index "t_idx"
> DETAIL: Index is not valid.
> 781/781 relations (100%), 2806/2806 pages (100%)
> FAIL

Yes, I can reproduce this following your steps. (It's always appreciated to have steps to reproduce.)

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

amcheck % psql -c "CREATE INDEX CONCURRENTLY t_idx ON t(i);" & sleep 0.1 && psql -c "SELECT * FROM pg_catalog.bt_index_parent_check(index := 't_idx'::regclass, heapallindexed := true, rootdescend := true)"
[1] 9553
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.
[1] + exit 1 psql -c "CREATE INDEX CONCURRENTLY t_idx ON t(i);"

If Peter agrees that this is not pg_amcheck specific, then we should start a new thread to avoid confusing the commitfest tickets for these two items.

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company