The following bug has been logged on the website:
Bug reference: 15875
Logged by: Henri Cook
Email address: github@henricook.com
PostgreSQL version: 10.6
Operating system: docker image, postgres:10.6
Description:
Hi all,
When running the SQL below we get a serialisation error at the end of
Transaction 2. Because of the presence of the primary key PG isn't doing a
seq scan to perform the selects and manual inspection of pg_locks would seem
to imply it's not a page lock issue either. Can anyone shed some light on
why this query causes an isolation issue? The exact isolation error is:
```
ERROR: could not serialize access due to read/write dependencies among
transactions
DETAIL: Reason code: Canceled on identification as a pivot, during write.
HINT: The transaction might succeed if retried.
```
Obviously in this case a retry will probably succeed, as per the docs - but
i'm not sure it should be necessary?
Thanks in advance for your attention
Henri
Steps to recreate:
```
DROP TABLE IF EXISTS foo;
CREATE TABLE foo(id int PRIMARY KEY);
INSERT INTO foo (id) VALUES (44);
INSERT INTO foo (id) VALUES (33);
```
-- Transaction 1:
```
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT id FROM foo WHERE id = 44;
```
-- Transaction 2:
```
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT id FROM foo WHERE id = 33;
INSERT INTO foo (id) VALUES (1);
COMMIT;
```
-- Transaction 1:
```
INSERT INTO foo (id) VALUES (2);
<observe serialisation error>
```