On Tue, Dec 11, 2012 at 6:52 PM, Andres Freund <and...@2ndquadrant.com> wrote: > One problem I see is that while exporting a snapshot solves the > visibility issues of the table's contents it does not protect against > schema changes. I am not sure whether thats a problem. > > If somebody runs a CLUSTER or something like that, the table's contents > will be preserved including MVCC semantics. That's fine. > The more problematic cases I see are TRUNCATE, DROP and ALTER > TABLE.
This is why the pg_dump master process executes a lock table <table> in access share mode for every table, so your commands would all block. In fact it's even more complicated because the pg_dump worker processes also need to lock the table. They try to get a similar lock in "NOWAIT" mode right before dumping the table. If they don't get the lock that means that somebody else is waiting for an exclusive lock (this is the case you describe) and the backup will fail. > Problem 2: > > To be able to do a "SET TRANSACTION SNAPSHOT" the source transaction > needs to be alive. That's currently solved by exporting the snapshot in > the walsender connection that did the INIT_LOGICAL_REPLICATION. The > question is how long should we preserve that snapshot? You lost me on this one after the first sentence... But in general the snapshot isn't so much a magical thing: As long the exporting transaction is open, it guarantees that there is a transaction out there that is holding off vacuum from removing data and it's also guaranteeing that the snapshot as is has existed at some time in the past. Once it is applied to another transaction you now have two transactions that will hold off vacuum because they share the same xmin,xmax values. You could also just end the exporting transaction at that point. One thought at the time was to somehow integrate exported snapshots with the prepared transactions feature, then you could export a snapshot, prepare the exporting transaction and have that snapshot frozen forever and it would even survive a server restart. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers