Re: Synchronized snapshots versus multiple databases

On Fri, Oct 21, 2011 at 4:36 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> I can see a few alternatives, none of them very pleasant:
>
> 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.

> 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.

1 *and* 4 please.

So, unless explicitly requested, an exported snapshot is limited to
just one database. If explicitly requested to be transportable, we can
use the snapshot in other databases.

This allows us to do parallel pg_dump in both 1+ databases, as well as
allowing pg_dumpall to be fully consistent across all dbs.

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