Re: Synchronized snapshots versus multiple databases

On Fri, Oct 21, 2011 at 11:36 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I've thought of another nasty problem for the sync-snapshots patch.
>
> 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.
>
> 2. Allow a snapshot exported from another database to be loaded so long
> as this doesn't cause the DB-local value of GetOldestXmin to go
> backwards.  However, in scenarios such as the above, C is certain to
> fail such a test.  To make it work, pg_dumpall would have to start
> "advance guard" transactions in each database before it takes the
> intended-to-be-shared snapshot, and probably even wait for these to be
> oldest.  Ick.
>
> 3. Remove the optimization that lets GetOldestXmin ignore XIDs outside
> the current database.  This sounds bad, but OTOH I don't think there's
> ever been any proof that this optimization is worth much in real-world
> usage.  We've already had to lobotomize that optimization for walsender
> processes, anyway.
>
> 4. Somehow mark the xmin of a process that has exported a snapshot so
> that it will be honored in all DBs not just the current one.  The
> difficulty here is that we'd need to know *at the time the snap is
> taken* that it's going to be exported.  (Consider the scenario above,
> except that A doesn't get around to exporting the snapshot it took in
> step 3 until between steps 5 and 6.  If the xmin wasn't already marked
> as globally applicable when vacuum looked at it in step 5, we lose.)
> This is do-able but it will contort the user-visible API of the sync
> snapshots feature.  One way we could do it is to require that
> transactions that want to export snapshots set a transaction mode
> before they take their first snapshot.

I am unexcited by #2 on usability grounds. I agree with you that #3
might end up being a fairly small pessimization in practice, but I'd
be inclined to just do #1 for now and revisit the issue when and if
someone shows an interest in revamping pg_dumpall to do what you're
proposing (and hopefully a bunch of other cleanup too).

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