[BUGS] BUG #14691: Isolation failure in deferrable transaction concurrentwith schema change

The following bug has been logged on the website:

Bug reference:      14691
Logged by:          Chris Pacejo
Email address:      cpacejo@clearskydata.com
PostgreSQL version: 9.5.7
Operating system:   CentOS 7
Description:

Hi, issuing a SERIALIZABLE DEFERRABLE READ ONLY read from a table to which a
column is being added results in an isolation failure.

Example 1:

Session A (setup):

me=> CREATE TABLE foo(x integer);
CREATE TABLE
me=> INSERT INTO foo VALUES (1), (2), (3);
INSERT 0 3


Session B (normal read):

me=> BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE; TABLE foo;
COMMIT;
BEGINx 
---123
(3 rows)

COMMIT


Session A (add column but do not commit):

me=> BEGIN; ALTER TABLE foo ADD COLUMN y integer DEFAULT 0;
BEGIN
ALTER TABLE


Session B (stalled read):

me=> BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE; TABLE foo;
COMMIT;
BEGIN


Session A (commit added column):

me=> COMMIT;
COMMIT


Session B (stalled read returns empty table; a second read is again
correct):
x | y 
---+---
(0 rows)

COMMIT
me=> BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE; TABLE foo;
COMMIT;
BEGINx | y 
---+---1 | 02 | 03 | 0
(3 rows)

COMMIT



Example 2:

Session A (setup):

me=> CREATE TABLE foo(x integer);
CREATE TABLE
me=> INSERT INTO foo VALUES (1), (2), (3);
INSERT 0 3


Session B (normal read):

me=> BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE; TABLE foo;
COMMIT;
BEGINx 
---123
(3 rows)

COMMIT


Session A (add column but do not commit):

me=> BEGIN; ALTER TABLE foo ADD COLUMN y integer; UPDATE foo SET y = 0;
BEGIN
ALTER TABLE
UPDATE 3


Session B (stalled read):

me=> BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE; TABLE foo;
COMMIT;
BEGIN


Session A (commit added column):

me=> COMMIT;
COMMIT


Session B (stalled read returns empty column; a second read is again
correct):
x | y 
---+---1 |  2 |  3 |  
(3 rows)

COMMIT
me=> BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE; TABLE foo;
COMMIT;
BEGINx | y 
---+---1 | 02 | 03 | 0
(3 rows)

COMMIT


I would expect the "middle" result of each example (with missing data) not
to be visible at any isolation level.

This does not occur if the schema-modifying transaction is aborted, if a
column is deleted, with a view, or if a non-deferrable read transaction is
used.

Please let me know if there is any additional information I can give or if I
misunderstand the deferrable transaction mechanism.


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs