PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID15875
PG Version10.6
OSdocker image, postgres:10.6
Opened2019-06-26 10:04:35+00
Reported byHenri Cook
StatusNew

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      15875
Logged by:          Henri Cook
Email address:      (redacted)
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>
   ```

Messages

DateAuthorSubject
2019-06-26 10:04:35+00PG Bug reporting formBUG #15875: Unexpected serializable isolation error when INSERTing into a table
2019-06-26 10:33:48+00Thomas MunroRe: BUG #15875: Unexpected serializable isolation error when INSERTing into a table
2019-06-26 10:50:10+00Henri CookRe: BUG #15875: Unexpected serializable isolation error when INSERTing into a table
2019-06-26 12:12:04+00Thomas MunroRe: BUG #15875: Unexpected serializable isolation error when INSERTing into a table