On 2014-11-04 13:51:23 -0500, Tom Lane wrote: > Bernd Helmle <mailings@xxxxxxxxxxx> writes: > > --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. > > Not sure. The OP's point is that in a SELECT, you do get unsurprising > results, because a SELECT will acquire its execution snapshot after it's > gotten AccessShareLock on the table. Arguably COPY should behave likewise. > Or to be even more concrete, COPY (SELECT * FROM tab) TO ... probably > already acts like he wants, so why isn't plain COPY equivalent to that? Even a plain SELECT essentially acts that way if I recall correctly if you use REPEATABLE READ+ and force a snapshot to be acquired beforehand. It's imo not very surprising. All ALTER TABLE rewrites just disregard visibility of existing tuples. Only CLUSTER/VACUUM FULL do the full hangups to keep all the necessary tuples + ctid chains around. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
