pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedReproducible 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