Re: pg_dump --snapshot

On 2013-05-06 13:07:17 -0400, Tom Lane wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> > On 6 May 2013 16:02, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> >> On 05/06/2013 10:56 AM, Simon Riggs wrote:
> >>> This overrides the internally generated snapshot in parallel pg_dump.
>
> >> Could you be a bit more expansive about the use case, please?
>
> > 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.

The initial transaction that exports the transaction would need to hold
locks until pg_dump started :/.

> Or in short: -1 for the very concept of letting the user control
> pg_dump's snapshot.

Its rather useful if you e.g. want to instantiate a new replica without
rebuilding pg_dump/pg_restore's capabilities wrt. ordering, parallelism,
separating initial data load from index creation and all that. Which
already has been incompletely reinvented by several solutions :(.

So besides the above and real problems you point out this seems
worthwile to me...

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services