Re: Synchronized snapshots versus multiple databases

On 10/21/2011 01:06 PM, Tom Lane wrote:
> Andrew Dunstan<andrew@dunslane.net>  writes:
>> On 10/21/2011 12:05 PM, Florian Pflug wrote:
>>> On Oct21, 2011, at 17:36 , Tom Lane wrote:
>>>> 1. Restrict exported snapshots to be loaded only by transactions running
>>>> in the same database as the exporter.  This would fix the problem, but
>>>> it cuts out one of the main use-cases for sync snapshots, namely getting
>>>> cluster-wide-consistent dumps in pg_dumpall.
>>> Isn't the use-case getting consistent *parallel* dumps of a single database
>>> rather than consistent dump of multiple databases? Since we don't have atomic
>>> cross-database commits, what does using the same snapshot to dump multiple
>>> databases buy us?
>> That was my understanding of the use case.
> Um, which one are you supporting?


#1 seemed OK from this POV. Everything else looks ickier and/or more 
fragile, at first glance anyway.

> Anyway, the value of using the same snapshot across all of a pg_dumpall
> run would be that you could be sure that what you'd dumped concerning
> role and tablespace objects was consistent with what you then dump about
> database-local objects.  (In principle, anyway --- I'm not sure how
> much of that happens under SnapshotNow rules because of use of backend
> functions.  But you'll most certainly never be able to guarantee it if
> pg_dumpall can't export its snapshot to each subsidiary pg_dump run.)
>
>             

For someone who is concerned with that, maybe pg_dumpall could have an 
option to take an EXCLUSIVE lock on the shared catalogs?

cheers

andrew