Re: pg_dump --snapshot

On Mon, May 6, 2013 at 1:07 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Exported snapshots allow you to coordinate a number of actions
>> together, so they all see a common view of the database. So this patch
>> allows a very general approach to this, much more so than pg_dump
>> allows currently since the exact timing of the snapshot is not
>> controlled by the user.
>
> 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.
>
> 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.

I don't find this argument very convincing. The way you could fix the
ordering problem is:

1. take locks on all the objects you think you need to dump
2. update your snapshot and check whether the list of objects you
think you need to dump has changed
3. if yes, go to step 1, else done

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.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company