agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedFrom: Artem Anisimov <artem.anisimov.255@gmail.com>
To: Thomas Munro <thomas.munro@gmail.com>
To: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17949: Adding an index introduces serialisation anomalies.
Date: Tue, 13 Jun 2023 11:00:41 +0300
Message-ID: <b5f39987-edba-4b40-9b88-56760a4267db@gmail.com> (raw)
In-Reply-To: <CA+hUKGLgtMZbzm5md-eKtoKQo7tZU+FVM95YPTfH9k+3UQcvSQ@mail.gmail.com>
References: <17949-a0f17035294a55e2@postgresql.org>
<CA+hUKGLgtMZbzm5md-eKtoKQo7tZU+FVM95YPTfH9k+3UQcvSQ@mail.gmail.com>
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)
view thread (28+ messages) latest in thread
Message-ID: <b5f39987-edba-4b40-9b88-56760a4267db@gmail.com>
Permalink: ../b5f39987-edba-4b40-9b88-56760a4267db@gmail.com/
Also on: postgresql.org/message-id/b5f39987-edba-4b40-9b88-56760a4267db@gmail.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 using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: artem.anisimov.255@gmail.com, thomas.munro@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17949: Adding an index introduces serialisation anomalies.
In-Reply-To: <b5f39987-edba-4b40-9b88-56760a4267db@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox