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
