From: | Duncan Sands <duncan(dot)sands(at)deepbluecap(dot)com> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | Reproducible GIST index corruption under concurrent updates |
Date: | 2021-01-19 11:15:57 |
Message-ID: | a9690483-6c6c-3c82-c8ba-dc1a40848f11@deepbluecap.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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.
Attachment | Content-Type | Size |
---|---|---|
gist_issue.py | text/x-python | 6.7 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | PG Bug reporting form | 2021-01-19 15:08:15 | BUG #16830: Start of standby server never happens |
Previous Message | Juan José Santamaría Flecha | 2021-01-19 08:31:22 | Re: BUG #16825: When building on Windows, cl /? retrun 'x64' not AMD64 and the build does not create x64 environment |