Обсуждение: [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

On Tue, Jul 18, 2017 at 5:08 AM, David G. Johnston
<david.g.johnston@gmail.com> wrote:
> On Mon, Jun 5, 2017 at 12:11 PM, <cpacejo@clearskydata.com> wrote:
>>
>> 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.
>
>
>>
>> me=> BEGIN; ALTER TABLE foo ADD COLUMN y integer DEFAULT 0;
>
>
>>
>> me=> BEGIN; ALTER TABLE foo ADD COLUMN y integer; UPDATE foo SET y = 0;
>
>
>>
>>  x | y
>> ---+---
>>  1 |
>>  2 |
>>  3 |
>> (3 rows)
>
>
> This is a documented limitation.
>
> https://www.postgresql.org/docs/9.5/static/mvcc-caveats.html
>
> "Some DDL commands, currently only TRUNCATE and the table-rewriting forms of
> ALTER TABLE, are not MVCC-safe."
>
> The first command is "table-rewriting".
>
> The second ALTER TABLE doesn't rewrite the table but does change its
> structure - so you get rows but the newly added column is null.  This would
> seem to be a reasonable behavior.

It is quite weird that you see the new column (DDL effects) but not
the new values (DML effects) that were produced by the same
transaction.  That happens even if the altering session runs in
SERIALIZABLE.  There are defences against seeing anything weird when a
concurrent serializable transaction TRUNCATEs (see the call to
CheckTableForSerializableConflictIn(rel)), but those defences don't
simply nuke concurrent transactions: they use the usual SSI algorithm
so that the schedule might be allowed.

The same approach won't work here, as the attached patch shows.  If we
add a call to CheckTableForSerializableConflict(rel) in ATExecCmd (one
of the ALTER TABLE workhorse functions) then I think a conflict
between the overlapping transactions is detected, but there is no
cycle: the snapshot is judged safe and appears to run before the
ALTER, even though it sees the table definition with the new column,
ie after the ALTER.  To fix that maybe we'd need to do SSI on system
catalogs...  Then the attached patch should cause a serialization
failure in the second permutation, because a cycle would exist between
s2 and s3 (s2 read a catalog entry that s3 wrote, and s3 wrote to the
whole relation by running ALTER TABLE and s2 read the whole table).

-- 
Thomas Munro
http://www.enterprisedb.com

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