On Tue, 4 Nov 2014, Bernd Helmle wrote: > --On 3. November 2014 18:15:04 +0100 Sven Wegener <sven.wegener@xxxxxxxxxxx> > wrote: > > > I've check git master and 9.x and all show the same behaviour. I came up > > with the patch below, which is against curent git master. The patch > > modifies the COPY TO code to create a new snapshot, after acquiring the > > necessary locks on the source tables, so that it sees any modification > > commited by other backends. > > Well, i have the feeling that there's nothing wrong with it. The ALTER TABLE > command has rewritten all tuples with its own XID, thus the current snapshot > does not "see" these tuples anymore. I suppose that in SERIALIZABLE or > REPEATABLE READ transaction isolation your proposal still doesn't return the > tuples you'd like to see. No, but with REPEATABLE READ and SERIALIZABLE the plain SELECT case is currently broken too. The issue I'm fixing is that COPY (SELECT ...) TO and SELECT behave differently. So, how does an ALTER TABLE should behave transaction-wise? PostgreSQL claims transactional DDL support. As mentioned above a parallel SELECT with SERIALIZABLE returns an empty result, but it also sees the schema change, at least the change shows up in the result tuple description. The change doesn't show up in pg_catalog, so that bit is handled correctly. The schema change transaction can be rolled back the way it is now, so it's kind of transactional, but other transactions seeing the schema change in query results is broken. The empty result might be fixed by just keeping the old XID of rewritten tuples, as the exclusive lock ALTER TABLE helds should be enough to make sure nobody is actively accessing the rewritten table. But I'm pondering if there is a solution for the visible schema change that doesn't involve keeping the old data files around or to just raise an serialization error. Coming back on my mentioned solution of setting the XID of rewritten tuples to the FrozenXID. That's broken as in the REPEATABLE READ/SERIALIZABLE case there might be tuples that should not be seen by older transactions and moving the tuples to the FrozenXID would make them visible. Sven -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
