Re: [BUGS] BUG #14691: Isolation failure in deferrable transactionconcurrent with schema change

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