To reproduce, run the attached python3 script after adjusting CONNECTION_STRING
appropriately. The target database should have the btree_gist extension
installed. If it fails due to making too many connections to the database, try
reducing NUM_THREADS (or allowing more connections).
Example bad output (may take a minute or two):
$ ./gist_issue.py
inconsistent: 9286
Each "inconsistent" line means that looking up by "id" in the "sections" table
returns a row but looking up by "file" doesn't, even though "id" and "file" are
always the same:
duncan=> SELECT * FROM sections WHERE id=9286;
id | file | valid
------+------+---------------
9286 | 9286 | (,1970-01-01)
(1 row)
duncan=> SELECT * FROM sections WHERE file=9286;
id | file | valid
----+------+-------
(0 rows)
What is going on here is that querying by "file" uses the GIST index but this
index is corrupt:
duncan=> EXPLAIN SELECT * FROM sections WHERE file=9286;
QUERY PLAN
------------------------------------------------------------------------------------------
Index Scan using sections_file_valid_excl on sections (cost=0.15..2.37 rows=1
width=18)
Index Cond: (file = 9286)
(2 rows)
If the "gist_issue.py" script runs forever then it failed to reproduce the problem.
Reproduces with postgres 13.1 on Linux (ubuntu groovy, package
13.1-1.pgdg20.10+1, x86-64). I reproduced it with default database settings and
with a tuned database, and on multiple Linux machines.
Enjoy!
Best wishes, Duncan.