Subject: Re: BUG #17212: pg_amcheck fails on checking temporary relations
To: Mark Dilger <mark.dilger@enterprisedb.com>,
 PostgreSQL-development <pgsql-hackers@postgresql.org>
Cc: Robert Haas <robertmhaas@gmail.com>, Peter Geoghegan <pg@bowt.ie>
References: <17212-34dd4a1d6bba98bf@postgresql.org>
 <CAH2-Wz=bw-Uk2yoOq7yevC9hyMGgZ2Ud34nd9YJJPXJ3pkVcrw@mail.gmail.com>
 <E85855BD-C063-4B24-9EAA-02DECC8F42B9@enterprisedb.com>
 <E283B45F-5745-49C9-BBC7-99876E2FABD1@enterprisedb.com>
From: Alexander Lakhin <exclusion@gmail.com>
Message-ID: <f42de8ec-f219-0922-93b3-37a72bba1feb@gmail.com>
Date: Mon, 4 Oct 2021 12:00:01 +0300
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.11.0
MIME-Version: 1.0
In-Reply-To: <E283B45F-5745-49C9-BBC7-99876E2FABD1@enterprisedb.com>
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Archived-At: 
 <https://www.postgresql.org/message-id/f42de8ec-f219-0922-93b3-37a72bba1feb%40gmail.com>
Precedence: bulk

Hello Mark,

04.10.2021 01:20, Mark Dilger wrote:
> The attached patch includes a test case for this, which shows the problems against the current pg_amcheck.c, and a new version of pg_amcheck.c which fixes the bug.  Could you review it?
>
> Thanks for bringing this to my attention.
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.
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

When an index created without CONCURRENTLY, it runs successfully.

Beside that, it seems that pg_amcheck produces a deadlock in such a case:
2021-10-04 11:23:38.584 MSK [1451296] ERROR:  deadlock detected
2021-10-04 11:23:38.584 MSK [1451296] DETAIL:  Process 1451296 waits for
ShareLock on virtual transaction 5/542; blocked by process 1451314.
    Process 1451314 waits for ShareLock on relation 16385 of database
16384; blocked by process 1451296.
    Process 1451296: CREATE INDEX CONCURRENTLY t_idx ON t(i);
    Process 1451314: SELECT * FROM
"pg_catalog".bt_index_parent_check(index := '16390'::regclass,
heapallindexed := true, rootdescend := true)
2021-10-04 11:23:38.584 MSK [1451296] HINT:  See server log for query
details.
2021-10-04 11:23:38.584 MSK [1451296] STATEMENT:  CREATE INDEX
CONCURRENTLY t_idx ON t(i);

I think that the deadlock is yet another issue, as invalid indexes could
appear in other circumstances too.

Best regards,
Alexander