Re: BUG #17949: Adding an index introduces serialisation anomalies.

From: Thomas Munro <thomas(dot)munro(at)gmail(dot)com>
To: artem(dot)anisimov(dot)255(at)gmail(dot)com, pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: BUG #17949: Adding an index introduces serialisation anomalies.
Date: 2023-05-30 01:29:09
Message-ID: CA+hUKGLgtMZbzm5md-eKtoKQo7tZU+FVM95YPTfH9k+3UQcvSQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hi,

Reproduced here. Thanks for the reproducer. I agree that something
is wrong here, but I haven't had time to figure out what, yet, but let
me share what I noticed so far... I modified your test to add a pid
column to the locks table and to insert insert pg_backend_pid() into
it, and got:

postgres=# select xmin, * from locks;

┌───────┬──────┬───────┐
│ xmin │ path │ pid │
├───────┼──────┼───────┤
│ 17634 │ xyz │ 32932 │
│ 17639 │ xyz │ 32957 │
└───────┴──────┴───────┘

Then I filtered the logs (having turned the logging up to capture all
queries) so I could see just those PIDs and saw this sequence:

2023-05-29 00:15:43.933 EDT [32932] LOG: duration: 0.182 ms
statement: BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE
2023-05-29 00:15:43.934 EDT [32957] LOG: duration: 0.276 ms
statement: BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE
2023-05-29 00:15:43.935 EDT [32932] LOG: duration: 1.563 ms
statement: SELECT * FROM locks WHERE path = 'xyz'
2023-05-29 00:15:43.936 EDT [32932] LOG: duration: 0.126 ms
statement: INSERT INTO locks(path, pid) VALUES('xyz',
pg_backend_pid())
2023-05-29 00:15:43.937 EDT [32957] LOG: duration: 2.191 ms
statement: SELECT * FROM locks WHERE path = 'xyz'
2023-05-29 00:15:43.937 EDT [32957] LOG: duration: 0.261 ms
statement: INSERT INTO locks(path, pid) VALUES('xyz',
pg_backend_pid())
2023-05-29 00:15:43.937 EDT [32932] LOG: duration: 0.222 ms statement: COMMIT
2023-05-29 00:15:43.939 EDT [32957] LOG: duration: 1.775 ms statement: COMMIT

That sequence if run (without overlap) in the logged order is normally
rejected. The query plan being used (at least when I run the query
myself) looks like this:

Query Text: SELECT * FROM locks WHERE path = 'xyz'
Bitmap Heap Scan on locks (cost=4.20..13.67 rows=6 width=36)
Recheck Cond: (path = 'xyz'::text)
-> Bitmap Index Scan on locks_path_idx (cost=0.00..4.20 rows=6 width=0)
Index Cond: (path = 'xyz'::text)

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Ba Jinsheng 2023-05-30 03:19:28 Suspicious Estimated Number of Returned Rows
Previous Message Magnus Hagander 2023-05-30 00:11:00 Re: BUG #17919: "client hello" message / SNI / Openshift Routes