Horton

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

Artem Anisimov <artem.anisimov.255@gmail.com>

View thread View on postgr.es
From: Artem Anisimov <artem.anisimov.255@gmail.com>
To: Thomas Munro <thomas.munro@gmail.com>, pgsql-bugs@lists.postgresql.org
Date: 2023-06-13T08:00:41Z
Lists: pgsql-bugs 
Hi Thomas,


thank you for the confirmation and analysis. Did you have a chance to 
take a more detailed look at the problem?


Best regards,

Artem.


On 30/05/2023 04:29, Thomas Munro wrote:
> 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)

Commits

  1. Fix race in SSI interaction with bitmap heap scan.

  2. Fix race in SSI interaction with gin fast path.

  3. Fix race in SSI interaction with empty btrees.

  4. Re-think predicate locking on GIN indexes.