Re: pg_dump --snapshot

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Mon, May 6, 2013 at 1:07 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> I'm afraid that this is institutionalizing a design deficiency in
>> pg_dump; namely that it takes its snapshot before acquiring locks.
>> Ideally that would happen the other way around. I don't have a good
>> idea how we could fix that --- but a feature that allows imposition
>> of an outside snapshot will permanently foreclose ever fixing it.

I don't even understand how you could take locks for a later snapshot,
it seems to me you're talking about something much harder to design and
code than making the catalogs fully MVCC compliant.

>> What's more, this would greatly widen the risk window between when
>> the snapshot is taken and when we have all the locks and can have
>> some confidence that the DB isn't changing under us.

Rather than take some locks, you can now prevent the database objects
from changing with an event trigger. pg_dump could install that event
trigger in a preparing transaction, then do its work as currently, then
when done either remove or disable the event trigger.

All the event trigger has to do is unconditionnaly raise an exception
with a message explaining that no DDL command is accepted during when a
dump is in progress.

> Now, I'll grant you that this technique couldn't be used together with
> the proposed --snapshot option, but so what? Many people do DDL
> infrequently enough that this is not a problem in practice. But even
> if it is a problem, I don't see why that can't simply be a documented
> limitation of --snapshot.

IME it's a problem in practice, because people tend to want to run their
pg_dump and their rollouts within the same maintenance window. I mean,
those lucky guys out there having such thing as a maintenance window.

Again, it seems to me that the proper long term solution is both fully
MVCC catalogs and reduced locking for most commands.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support