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

> On Oct 4, 2021, at 2:00 AM, Alexander Lakhin <exclusion@gmail.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
tableas 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
commitfesttickets for these two items. 

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