pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Duncan Sands <duncan.sands@deepbluecap.com>
To: pgsql-bugs@lists.postgresql.org
Subject: Reproducible GIST index corruption under concurrent updates
Date: Tue, 19 Jan 2021 12:15:57 +0100
Message-ID: <a9690483-6c6c-3c82-c8ba-dc1a40848f11@deepbluecap.com> (raw)

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.

Attachments:

gist_issue.py (text/x-python, 6.7K) download #!/usr/bin/env python3 # Shows a corruption in gist indexes, when multiple threads are involved. CONNECTION_STRING="dbname=duncan" # CHANGE THIS TO SUIT YOU. # You will also need to do: CREATE EXTENSION btree_gist; import datetime import psycopg2.extras import threading NUM_THREADS = 32 META_IDS_PER_THREAD = 100 CHECK_EVERY = 100 with psycopg2.connect(CONNECTION_STRING) as conn: with conn.cursor() as cursor: cursor.execute( """ DROP TABLE IF EXISTS files CASCADE; CREATE TABLE files ( id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, meta INTEGER NOT NULL ); DROP TABLE IF EXISTS sections CASCADE; CREATE TABLE sections ( id INTEGER PRIMARY KEY, file INTEGER NOT NULL REFERENCES files, valid DATERANGE NOT NULL, EXCLUDE USING gist(file WITH =, valid WITH &&) ); """ ) found_problem = False class Worker(threading.Thread): def __init__(self, lower, upper): # upper not included super().__init__() self.lower = lower self.upper = upper def run(self): global found_problem try: check_counter = 0 metaid = self.lower with psycopg2.connect(CONNECTION_STRING) as conn: with conn.cursor() as cursor: while not found_problem: metaid = metaid + 1 if metaid >= self.upper: metaid = self.lower cursor.execute( "SELECT sections.id, valid FROM sections" " JOIN files ON sections.file = files.id WHERE meta=%s" " ORDER BY files.id DESC LIMIT 1", (metaid, )) r = cursor.fetchone() if not r: cursor.execute("BEGIN") cursor.execute( "INSERT INTO files(meta) VALUES (%s) RETURNING id", (metaid, ) ) fileid = cursor.fetchone() cursor.execute( "INSERT INTO sections(id,file,valid) VALUES (" " %s, %s, '(,)')", (fileid, fileid, ) ) cursor.execute("COMMIT") cursor.execute( "SELECT sections.id, valid FROM sections" " JOIN files ON sections.file = files.id WHERE meta=%s" " ORDER BY files.id DESC LIMIT 1", (metaid, )) r = cursor.fetchone() if not r: found_problem = True return row, valid = r cursor.execute("BEGIN") if valid.lower: mid = valid.lower + datetime.timedelta(days=1) else: mid = datetime.date(1970, 1, 1) v1 = psycopg2.extras.DateRange( valid.lower, mid, '[)', empty=valid.lower and valid.lower >= mid) v2 = psycopg2.extras.DateRange( mid, valid.upper, '[)', empty=valid.upper and mid >= valid.upper) add_1 = not v1.isempty add_2 = not v2.isempty # split ranges if add_1 or add_2: cursor.execute( "DELETE FROM sections WHERE id=%s", (row, )) if add_1: cursor.execute( "INSERT INTO files(meta) VALUES (%s) RETURNING id", (metaid, ) ) file1 = cursor.fetchone() cursor.execute( "INSERT INTO sections(id,file,valid) VALUES (" " %s, %s, %s::daterange)", (file1, file1, v1)) if add_2: cursor.execute( "INSERT INTO files(meta) VALUES (%s) RETURNING id", (metaid, ) ) file2 = cursor.fetchone() cursor.execute( "INSERT INTO sections(id,file,valid) VALUES (" " %s, %s, %s::daterange)", (file2, file2, v2)) cursor.execute("COMMIT") check_counter = check_counter + 1 if check_counter % CHECK_EVERY == 0: cursor.execute( "SELECT id FROM files WHERE meta>=%s AND meta<%s", (self.lower, self.upper)) ids = [row[0] for row in cursor] for i in ids: cursor.execute("SELECT * FROM sections WHERE id=%s", (i, )) has1 = cursor.fetchone() is not None cursor.execute("SELECT * FROM sections WHERE file=%s", (i, )) has2 = cursor.fetchone() is not None if has1 != has2: found_problem = True return except: found_problem = True raise workers = [Worker(j*META_IDS_PER_THREAD, (j+1)*META_IDS_PER_THREAD) for j in range(0, NUM_THREADS)] for w in workers: w.start() for w in workers: w.join() with psycopg2.connect(CONNECTION_STRING) as conn: with conn.cursor() as cursor: cursor.execute("SELECT id FROM files") ids = [row[0] for row in cursor] for i in ids: cursor.execute("SELECT * FROM sections WHERE id=%s", (i, )) has1 = cursor.fetchone() is not None cursor.execute("SELECT * FROM sections WHERE file=%s", (i, )) has2 = cursor.fetchone() is not None if has1 != has2: print("inconsistent: %s" %i)

view thread (8+ messages)  latest in thread

Message-ID: <a9690483-6c6c-3c82-c8ba-dc1a40848f11@deepbluecap.com>
Permalink:  ../a9690483-6c6c-3c82-c8ba-dc1a40848f11@deepbluecap.com/
Also on:    postgresql.org/message-id/a9690483-6c6c-3c82-c8ba-dc1a40848f11@deepbluecap.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: duncan.sands@deepbluecap.com
  Subject: Re: Reproducible GIST index corruption under concurrent updates
  In-Reply-To: <a9690483-6c6c-3c82-c8ba-dc1a40848f11@deepbluecap.com>
  References: <a9690483-6c6c-3c82-c8ba-dc1a40848f11@deepbluecap.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox