Reproducible GIST index corruption under concurrent updates

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

Responses

Browse pgsql-bugs by date

  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