pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Reproducible GIST index corruption under concurrent updates
8+ messages / 4 participants
[nested] [flat]

Commits from this thread:
  2021-01-20 6b4d3046f4 b8403d140f 0326635dd4  Fix bug in detecting concurrent page splits in GiST insert
* Reproducible GIST index corruption under concurrent updates
@ 2021-01-19 11:15  Duncan Sands <duncan.sands@deepbluecap.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Duncan Sands @ 2021-01-19 11:15 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org

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)
^ permalink raw reply [nested|flat] 8+ messages in thread

* Re: Reproducible GIST index corruption under concurrent updates
@ 2021-01-19 17:12  Andrey Borodin <x4mmm@yandex-team.ru>
  parent: Duncan Sands <duncan.sands@deepbluecap.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Andrey Borodin @ 2021-01-19 17:12 UTC (permalink / raw)
  To: Duncan Sands <duncan.sands@deepbluecap.com>; +Cc: pgsql-bugs@lists.postgresql.org



> 19 янв. 2021 г., в 16:15, Duncan Sands <duncan.sands@deepbluecap.com> написал(а):
> 
> Enjoy!

Thanks!
It reproduces on my machine. Seems like all tuples are indexed, but some root downlinks are not adjusted properly. Probably, after concurrent split.

Best regards, Andrey Borodin.




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Reproducible GIST index corruption under concurrent updates
@ 2021-01-19 21:53  Heikki Linnakangas <hlinnaka@iki.fi>
  parent: Andrey Borodin <x4mmm@yandex-team.ru>
  0 siblings, 1 reply; 8+ messages in thread

From: Heikki Linnakangas @ 2021-01-19 21:53 UTC (permalink / raw)
  To: Andrey Borodin <x4mmm@yandex-team.ru>; Duncan Sands <duncan.sands@deepbluecap.com>; +Cc: pgsql-bugs@lists.postgresql.org

On 19/01/2021 19:12, Andrey Borodin wrote:
>> 19 янв. 2021 г., в 16:15, Duncan Sands <duncan.sands@deepbluecap.com> написал(а):
>>
>> Enjoy!
> 
> Thanks!
> It reproduces on my machine. Seems like all tuples are indexed, but some root downlinks are not adjusted properly. Probably, after concurrent split.

This is reproducable down to v12. I bisected it to this commit:

commit e2e992c93145cfc0e3563fb84efd25b390a84bb9 (refs/bisect/bad)
Author: Heikki Linnakangas <heikki.linnakangas@iki.fi>
Date:   Wed Jul 24 20:24:05 2019 +0300

     Refactor checks for deleted GiST pages.

     The explicit check in gistScanPage() isn't currently really 
necessary, as
     a deleted page is always empty, so the loop would fall through without
     doing anything, anyway. But it's a marginal optimization, and it 
gives a
     nice place to attach a comment to explain how it works.

     Backpatch to v12, where GiST page deletion was introduced.

     Reviewed-by: Andrey Borodin
     Discussion: 
https://www.postgresql.org/message-id/835A15A5-F1B4-4446-A711-BF48357EB602%40yandex-team.ru

I'll dig deeper tomorrow.

- Heikki





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Reproducible GIST index corruption under concurrent updates
@ 2021-01-19 22:31  Heikki Linnakangas <hlinnaka@iki.fi>
  parent: Heikki Linnakangas <hlinnaka@iki.fi>
  0 siblings, 1 reply; 8+ messages in thread

From: Heikki Linnakangas @ 2021-01-19 22:31 UTC (permalink / raw)
  To: Andrey Borodin <x4mmm@yandex-team.ru>; Duncan Sands <duncan.sands@deepbluecap.com>; +Cc: pgsql-bugs@lists.postgresql.org

On 19/01/2021 23:53, Heikki Linnakangas wrote:
> On 19/01/2021 19:12, Andrey Borodin wrote:
>>> 19 янв. 2021 г., в 16:15, Duncan Sands <duncan.sands@deepbluecap.com> написал(а):
>>>
>>> Enjoy!
>>
>> Thanks!
>> It reproduces on my machine. Seems like all tuples are indexed, but some root downlinks are not adjusted properly. Probably, after concurrent split.
> 
> This is reproducable down to v12. I bisected it to this commit:
> 
> commit e2e992c93145cfc0e3563fb84efd25b390a84bb9 (refs/bisect/bad)
> Author: Heikki Linnakangas <heikki.linnakangas@iki.fi>
> Date:   Wed Jul 24 20:24:05 2019 +0300
> 
>       Refactor checks for deleted GiST pages.
> 
>       The explicit check in gistScanPage() isn't currently really
> necessary, as
>       a deleted page is always empty, so the loop would fall through without
>       doing anything, anyway. But it's a marginal optimization, and it
> gives a
>       nice place to attach a comment to explain how it works.
> 
>       Backpatch to v12, where GiST page deletion was introduced.
> 
>       Reviewed-by: Andrey Borodin
>       Discussion:
> https://www.postgresql.org/message-id/835A15A5-F1B4-4446-A711-BF48357EB602%40yandex-team.ru
> 
> I'll dig deeper tomorrow.

The culprit is this change:

> @@ -858,12 +856,13 @@ gistdoinsert(Relation r, IndexTuple itup, Size freespace,
>                                          * leaf/inner is enough to recognize split for root
>                                          */
>                                 }
> -                               else if (GistFollowRight(stack->page) ||
> -                                                stack->parent->lsn < GistPageGetNSN(stack->page))
> +                               else if ((GistFollowRight(stack->page) ||
> +                                                 stack->parent->lsn < GistPageGetNSN(stack->page)) &&
> +                                                GistPageIsDeleted(stack->page))
>                                 {
>                                         /*
> -                                        * The page was split while we momentarily unlocked the
> -                                        * page. Go back to parent.
> +                                        * The page was split or deleted while we momentarily
> +                                        * unlocked the page. Go back to parent.
>                                          */
>                                         UnlockReleaseBuffer(stack->buffer);
>                                         xlocked = false;

The comment change was correct, but the condition used &&. Should've 
been ||. There is another copy of basically the same condition earlier 
in the function that was changed correctly, but I blundered this one. Oops.

The attached patch fixes this. I also added an assertion to the 
gistplacetopage() function, to check that we never try to insert on a 
deleted page. This bug could've made that happen too, although in this 
case the problem was a concurrent split, not a deletion. I'll backpatch 
and push this tomorrow.

Many thanks for the easy reproducer script, Duncan!

- Heikki

Attachments:

fix-gist-corruption-bug.patch (text/x-patch, 806B) download diff --git a/src/backend/access/gist/gist.c b/src/backend/access/gist/gist.c index 79030e7715..d94d9d33a6 100644 --- a/src/backend/access/gist/gist.c +++ b/src/backend/access/gist/gist.c @@ -242,6 +242,9 @@ gistplacetopage(Relation rel, Size freespace, GISTSTATE *giststate, if (GistFollowRight(page)) elog(ERROR, "concurrent GiST page split was incomplete"); + /* should never try to insert to a deleted page */ + Assert(!GistPageIsDeleted(page)); + *splitinfo = NIL; /* @@ -857,7 +860,7 @@ gistdoinsert(Relation r, IndexTuple itup, Size freespace, */ } else if ((GistFollowRight(stack->page) || - stack->parent->lsn < GistPageGetNSN(stack->page)) && + stack->parent->lsn < GistPageGetNSN(stack->page)) || GistPageIsDeleted(stack->page)) { /*
^ permalink raw reply [nested|flat] 8+ messages in thread

* Re: Reproducible GIST index corruption under concurrent updates
@ 2021-01-20 08:23  Duncan Sands <duncan.sands@deepbluecap.com>
  parent: Heikki Linnakangas <hlinnaka@iki.fi>
  0 siblings, 1 reply; 8+ messages in thread

From: Duncan Sands @ 2021-01-20 08:23 UTC (permalink / raw)
  To: Heikki Linnakangas <hlinnaka@iki.fi>; Andrey Borodin <x4mmm@yandex-team.ru>; +Cc: pgsql-bugs@lists.postgresql.org

> The comment change was correct, but the condition used &&. Should've been ||. 
> There is another copy of basically the same condition earlier in the function 
> that was changed correctly, but I blundered this one. Oops.
> 
> The attached patch fixes this. I also added an assertion to the 
> gistplacetopage() function, to check that we never try to insert on a deleted 
> page. This bug could've made that happen too, although in this case the problem 
> was a concurrent split, not a deletion. I'll backpatch and push this tomorrow.
> 
> Many thanks for the easy reproducer script, Duncan!

No problem Heikki, thanks for the quick fix.

Best wishes, Duncan.





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Reproducible GIST index corruption under concurrent updates
@ 2021-01-20 10:04  Heikki Linnakangas <hlinnaka@iki.fi>
  parent: Duncan Sands <duncan.sands@deepbluecap.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Heikki Linnakangas @ 2021-01-20 10:04 UTC (permalink / raw)
  To: Duncan Sands <duncan.sands@deepbluecap.com>; Andrey Borodin <x4mmm@yandex-team.ru>; +Cc: pgsql-bugs@lists.postgresql.org

On 20/01/2021 10:23, Duncan Sands wrote:
>> The comment change was correct, but the condition used &&. Should've been ||.
>> There is another copy of basically the same condition earlier in the function
>> that was changed correctly, but I blundered this one. Oops.
>>
>> The attached patch fixes this. I also added an assertion to the
>> gistplacetopage() function, to check that we never try to insert on a deleted
>> page. This bug could've made that happen too, although in this case the problem
>> was a concurrent split, not a deletion. I'll backpatch and push this tomorrow.
>>
>> Many thanks for the easy reproducer script, Duncan!
> 
> No problem Heikki, thanks for the quick fix.

Pushed as commit 6b4d3046f4, and backpatched down to version 12, where 
this bug was introduced. It will appear in the next minor release.

- Heikki





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Reproducible GIST index corruption under concurrent updates
@ 2021-01-20 15:25  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Heikki Linnakangas <hlinnaka@iki.fi>
  0 siblings, 1 reply; 8+ messages in thread

From: Tom Lane @ 2021-01-20 15:25 UTC (permalink / raw)
  To: Heikki Linnakangas <hlinnaka@iki.fi>; +Cc: Duncan Sands <duncan.sands@deepbluecap.com>; Andrey Borodin <x4mmm@yandex-team.ru>; pgsql-bugs@lists.postgresql.org

Heikki Linnakangas <hlinnaka@iki.fi> writes:
> Pushed as commit 6b4d3046f4, and backpatched down to version 12, where 
> this bug was introduced. It will appear in the next minor release.

I see you noted that REINDEX is required to repair any corrupted indexes.
For the purposes of the release notes, can we characterize which indexes
might be affected, or do we just have to say "better reindex all GIST
indexes"?

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Reproducible GIST index corruption under concurrent updates
@ 2021-01-20 22:26  Heikki Linnakangas <hlinnaka@iki.fi>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 8+ messages in thread

From: Heikki Linnakangas @ 2021-01-20 22:26 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Duncan Sands <duncan.sands@deepbluecap.com>; Andrey Borodin <x4mmm@yandex-team.ru>; pgsql-bugs@lists.postgresql.org

On 20/01/2021 17:25, Tom Lane wrote:
> Heikki Linnakangas <hlinnaka@iki.fi> writes:
>> Pushed as commit 6b4d3046f4, and backpatched down to version 12, where
>> this bug was introduced. It will appear in the next minor release.
> 
> I see you noted that REINDEX is required to repair any corrupted indexes.
> For the purposes of the release notes, can we characterize which indexes
> might be affected, or do we just have to say "better reindex all GIST
> indexes"?

Any GiST index that's been concurrently inserted might be corrupt. 
Better reindex all GiST indexes.

- Heikki

P.S. The GiST amcheck extension that's been discussed at [1] would catch 
this corruption, so if that was already committed, we could suggest 
using it. But that doesn't help us now.

[1] 
https://www.postgresql.org/message-id/CAF3eApa07-BajjG8+RYx-Dr_cq28ZA0GsZmUQrGu5b2ayRhB5A@mail.gmail... 
would catch this corruption





^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2021-01-20 22:26 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-01-19 11:15 Reproducible GIST index corruption under concurrent updates Duncan Sands <duncan.sands@deepbluecap.com>
2021-01-19 17:12 ` Andrey Borodin <x4mmm@yandex-team.ru>
2021-01-19 21:53   ` Heikki Linnakangas <hlinnaka@iki.fi>
2021-01-19 22:31     ` Heikki Linnakangas <hlinnaka@iki.fi>
2021-01-20 08:23       ` Duncan Sands <duncan.sands@deepbluecap.com>
2021-01-20 10:04         ` Heikki Linnakangas <hlinnaka@iki.fi>
2021-01-20 15:25           ` Tom Lane <tgl@sss.pgh.pa.us>
2021-01-20 22:26             ` Heikki Linnakangas <hlinnaka@iki.fi>

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