Thread: ALTER TABLE transaction isolation problem

DT wrote
> Hi,
>
>   I'm reading code of ALTER TABLE, and I found when target table needs
> rewrite, tuple inserted into new heap uses current transaction's xid as
> xmin. Does this behavior satisfy serializable isolation? I wrote some test
> cases:
> CREATE TABLE t1(a INT);CREATE TABLE t2(a INT);INSERT INTO t1
> VALUES(1);INSERT INTO t2 VALUES(1);
> transaction one:
> postgres=# commit;COMMITpostgres=# BEGIN;BEGINpostgres=# SET TRANSACTION
> ISOLATION LEVEL SERIALIZABLE;SETpostgres=# SELECT * FROM t1; a--- 1(1
> rows)
>         transaction two execute SQL:  ALTER TABLE t2 ADD COLUMN b INT
> DEFAULT 1;
> postgres=# SELECT * FROM t2; a | b---+---(0 rows)
> Transaction one sees nothing in t2, and i can not give any serial
> execution order of these two transactions, does it still  satisfy
> serializable isolation?

I'm not a hacker but I wanted to try and understand this better so I took a
look...my testing is on 9.0

Wrapping the ALTER TABLE into serializable transaction causes the SELECT to
wait on the lock established by the ALTER TABLE transaction but once the
ALTER TABLE commits the result of the SELECT includes the new column but
does not contain any data.

I was curious if it would fail on commit instead of mid-transaction but that
did not pan out.

It is correct that the new xmin should be the xid of the ALTER TABLE
transaction; the problem is that the original table seems to be basically
erased - i.e. like it was truncated (an explicitly MVCC-unsafe operation and
one which exhibits this same interaction {i.e., replace "ALTER TABLE" with
"TRUNCATE"}).

As noted on the TRUNCATE page if you manage to perform the SELECT * FROM t2
prior to the ALTER/TRUNCATE command the attempt to LOCK the table (for
ALTER/TRUNCATE) blocks.

While I can understand avoiding the use of TRUNCATE (you can always just
DELETE) avoid ALTER TABLE is much more difficult since there is no MVCC-safe
alternative to accomplish the same functional goal.

At first glance I'd say at minimum this could use some improved
documentation if the underlying behavior cannot be corrected.

Again, not a hacker, but for repeatable read and serializeable it would seem
that when a re-write of a table occurs that some kind of "table exists as of
xid" value needs to be set and then if that ID is greater than the xid of
the calling transaction the table effectively does not exist and a 'relation
"table" does not exist' error should be thrown.

I'm doubtful it is worthwhile to make it so the SELECT returns the pre-ALTER
table structure and records so exploration should be done on the "how do we
make this fail cheaply" path.  Not fixing these cases (TRUNCATE and ALTER
TABLE) but just documenting it doesn't seem good given that the result is
contrary to the promises these isolation levels make.

I really hope that I am on the right track with all this but if not at least
you get some insight into how a non-hacker (mis-)understands what is
happening behind the scenes.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/ALTER-TABLE-transaction-isolation-problem-tp5769289p5769347.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

DT <kurt023@hotmail.com> wrote:

> I'm reading code of ALTER TABLE, and I found when target table
> needs rewrite, tuple inserted into new heap uses current
> transaction's xid as xmin.

That sure sounds wrong to me.

> Does this behavior satisfy serializable isolation? I wrote some
> test cases:
>
> [ Examples shows that both SERIALIZABLE and REPEATABLE READ
> transactions could see an empty table which was not empty as of
> the point the snapshot was taken.  For that matter, it was not
> empty at any later point, either. ]

Why don't we rewrite tuples with their existing xid in such cases?
The current state of affairs seem to me to be a pretty clear bug.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Kevin Grittner-5 wrote
>> [ Examples shows that both SERIALIZABLE and REPEATABLE READ
>> transactions could see an empty table which was not empty as of
>> the point the snapshot was taken.  For that matter, it was not
>> empty at any later point, either. ]
>
> Why don't we rewrite tuples with their existing xid in such cases?
> The current state of affairs seem to me to be a pretty clear bug.

In the ADD COLUMN scenario this results in the new column being visible when
it technically should not be but that is not likely a huge concern.

In the DROP COLUMN scenario you can no longer see data which should
technically be present.  I guess if you go to use that data and it is not
present you'd get an error which is the technically correct response anyway
so probably not a huge concern either.

The idea of altering a record but not updating its xid sounds unclean but
I'm not able to evaluate any potential pitfalls of such an action.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/ALTER-TABLE-transaction-isolation-problem-tp5769289p5769393.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Kevin Grittner <kgrittn@ymail.com> writes:
> Why don't we rewrite tuples with their existing xid in such cases?
> The current state of affairs seem to me to be a pretty clear bug.

No, it isn't --- the tuple is being modified by the ALTER command.

            regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Kevin Grittner <kgrittn@ymail.com> writes:
>> Why don't we rewrite tuples with their existing xid in such
>> cases?  The current state of affairs seem to me to be a pretty
>> clear bug.
>
> No, it isn't --- the tuple is being modified by the ALTER
> command.

If a REPEATABLE READ or SERIALIZABLE transaction cannot see the
state of a table as of the point it took its snapshot, that is a
bug.  There are reasonable arguments why the right fix isn't to
keep the same xmin value, but that doesn't make the current
situation less of a bug.  A REPEATABLE READ or SERIALIZABLE
transaction can currently see a table as completely empty, even if
it was not in that state at any point since the transaction began.
Heck, it can be seen in that state even if it was *never* visible
in that state -- for example, it was created with rows by SELECT
INTO and has never had all rows deleted or truncated since.

If an UPDATE modified the tuples you could never see things in such
an inconsistent state.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Kevin Grittner <kgrittn@ymail.com> wrote:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Kevin Grittner <kgrittn@ymail.com> writes:
>>> Why don't we rewrite tuples with their existing xid in such
>>> cases?  The current state of affairs seem to me to be a pretty
>>> clear bug.
>>
>> No, it isn't --- the tuple is being modified by the ALTER
>> command.
>
> If a REPEATABLE READ or SERIALIZABLE transaction cannot see the
> state of a table as of the point it took its snapshot, that is a
> bug.  There are reasonable arguments why the right fix isn't to
> keep the same xmin value, but that doesn't make the current
> situation less of a bug.

It seems to me that the only truly correct way to handle this would
be for a REPEATABLE READ or SERIALIZABLE transaction to see the
table definition which matches its snapshot, and access the indexes
and heap which correspond to that.  Would the new MVCC access to
catalogs support such an extreme change?

Next best would be to recognize that the relation is not in a state
which matches the snapshot and throw an error.

After that it's a question of whether it's better to see and try to
use the right number of rows with possibly the wrong columns or the
wrong values (i.e., possibly correct, but possibly wrong in a way
that's hard to notice) or to see zero rows with possibly the wrong
columns (i.e., the results may be more likely to be wrong and more
obviously wrong if a human is looking at them).  That's a choice
between two bad options, either of which could silently return
incorrect data, so I hope we can fix this by making it correct or
by throwing an error.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Tom Lane-2 wrote
>> Why don't we rewrite tuples with their existing xid in such cases?
>> The current state of affairs seem to me to be a pretty clear bug.
>
> No, it isn't --- the tuple is being modified by the ALTER command.
>
>             regards, tom lane

I'm not quite sure what exactly you are referring to.  I agree the behavior
of "ALTER TABLE" is not buggy but the fact that it is not documented as
being MVCC-unsafe (like truncate; sometimes) is a documentation
bug/oversight.

If that is the extent of your comment then we'd still like to hear what you
think of the larger issue that a SERIALIZABLE transaction will not fail in
the presence of an ALTER TABLE command issued during the lifetime of the
transaction.

This does appear to be a bug and since the only way to return the original
data would be to have two copies of the table in memory for an indefinite
time period it makes more sense to use catalog versioning to detect when
table structure has altered and cause the transaction to fail.

It is too dangerous, given the wide variety of alterations possible using
ALTER TABLE, for these transactions - if begun before the ALTER TABLE - to
reference the altered table.  The status-quo, if documented, is always an
option - especially since the exposure size on this is fairly small - but
the only reason not to correct it and throw an error would be if the checks
needed to do so are too expensive.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/ALTER-TABLE-transaction-isolation-problem-tp5769289p5769461.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.