Re: ALTER TABLE transaction isolation problem - Mailing list pgsql-general

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.