Thread

1. synchronized snapshots

   Joachim Wieland <joe@mcknight.de> — 2010-01-08T19:36:44Z

   The attached patch implements the idea of Heikki / Simon published in
   
   http://archives.postgresql.org/pgsql-hackers/2009-11/msg00271.php
   
   Since nobody objected to the idea in general, I have implemented it.
   
   As this is not currently used anywhere it doesn't give immediate benefit, it
   is however a prerequisite for a parallel version of pg_dump that quite some
   people (including myself) seem to be interested in.
   
   Here's the comment from the patch explaining it in more detail:
   
   /*
    * This function is for synchronization of snapshots: It can be called by
    * new transactions to get the same snapshots. It's signature is
    *
    * pg_synchronize_snapshots(text, int, int);
    *
    * The first parameter is an identifier so that several groups can request
    * synchronized snapshots concurrently.
    *
    * The second parameter is the number of backends that are expected to connect
    * in the current group (i.e.  same identifier).
    *
    * The third parameter is the timeout in milliseconds.
    *
    * Note that once we are holding the ProcArrayLock in shared mode we are
    * severely hitting the usability of the database server: for example, nobody
    * can commit nontrivial transactions during that time nor can you establish a
    * new connection! This is why you need to be superuser to use this function.
    *
    * The idea is that from one connection you call for example
    *
    * pg_synchronize_snapshot('7bd0320c4ff9252716972e160fb33b8a', 4, 1000)
    *
    * and then have 1000ms to call with some other four (already connected)
    * sessions
    *
    * BEGIN TRANSACTION;
    * SELECT pg_synchronize_snapshot_taken('7bd0320c4ff9252716972e160fb33b8a');
    *
    * If all four pg_synchronize_snapshot_taken() calls return true and the
    * function pg_synchronize_snapshot() returns true as well, you can go on and
    * all four transactions now see the same snapshot (which in general is not the
    * snapshot that the transaction saw that has initially called
    * pg_synchronize_snapshot()).
    */
   
   Thoughts?
   
   
   Joachim
   

2. Re: synchronized snapshots

   Greg Stark <gsstark@mit.edu> — 2010-01-09T01:14:29Z

   On Fri, Jan 8, 2010 at 7:36 PM, Joachim Wieland <joe@mcknight.de> wrote:
   >  * If all four pg_synchronize_snapshot_taken() calls return true and the
   >
   
   If we must have a timeout I think you should throw an error if the
   timeout expires.
   
   -- 
   greg
   
   

3. Re: synchronized snapshots

   Markus Wanner <markus@bluegap.ch> — 2010-01-09T19:37:32Z

   Hi
   
   Joachim Wieland wrote:
   > Since nobody objected to the idea in general, I have implemented it.
   
   Great! I hope to get some spare cycles within the next few days to
   review it.
   
   Regards
   
   Markus Wanner
   
   
   

4. Re: synchronized snapshots

   Marcin Mańk <marcin.mank@gmail.com> — 2010-01-09T20:44:38Z

   
   Dnia 2010-01-09 o godz. 20:37 Markus Wanner <markus@bluegap.ch> napisał 
   (a):
   
   > Hi
   >
   > Joachim Wieland wrote:
   >> Since nobody objected to the idea in general, I have implemented it.
   
   How cool it would be if we could synchronize snapshots between the  
   master and the (sr) standby?
   
   The connection poolers could use that to send read-only queries to the  
   standby, and when the first dml/ddl statement in a transaction comes  
   up, they could switch to the master.
   
   If it is hard to tell from the statement if it writes anything, the  
   pooler could catch the error, and retry on the master
   
   Regards
   Marcin Mańk 
   

5. Re: synchronized snapshots

   Simon Riggs <simon@2ndquadrant.com> — 2010-01-10T11:28:29Z

   On Fri, 2010-01-08 at 20:36 +0100, Joachim Wieland wrote:
   > The attached patch implements the idea of Heikki / Simon published in
   > 
   > http://archives.postgresql.org/pgsql-hackers/2009-11/msg00271.php
   > 
   > Since nobody objected to the idea in general, I have implemented it.
   > 
   > As this is not currently used anywhere it doesn't give immediate benefit, it
   > is however a prerequisite for a parallel version of pg_dump that quite some
   > people (including myself) seem to be interested in.
   
   I'm interested in this, but realistically won't have time to review this
   personally in this release. Sorry about that.
   
   -- 
    Simon Riggs           www.2ndQuadrant.com
   
   
   

6. Re: synchronized snapshots

   Markus Wanner <markus@bluegap.ch> — 2010-02-05T17:29:53Z

   Hello Joachim,
   
   a little daughter eats lots of spare cycles - among other things. Sorry 
   it took that long to review.
   
   On Fri, 8 Jan 2010 20:36:44 +0100, Joachim Wieland <joe@mcknight.de>
   wrote:
   > The attached patch implements the idea of Heikki / Simon published in
   > 
   > http://archives.postgresql.org/pgsql-hackers/2009-11/msg00271.php
   
   I must admit I didn't read that up front, but thought your patch could 
   be useful for implementing parallel querying.
   
   So, let's first concentrate on the intended use case: allowing parallel 
   pg_dump. To me it seems like a pragmatic and quick solution, however, 
   I'm not sure if requiring superuser privileges is acceptable.
   
   The patch currently compiles (modulo some OID changes in pg_proc.h to 
   prevent duplicates) and the test suite runs through fine. I haven't 
   tested the new functions, though.
   
   
   Reading the code, I'm missing the part that actually acquires the 
   snapshot for the transaction(s). After setting up multiple transactions 
   with pg_synchronize_snapshot and pg_synchronize_snapshot_taken, they 
   still don't have a snapshot, do they?
   
   Also, you should probably ensure the calling transactions don't have a 
   snapshot already (let alone a transaction id).
   
   In a similar vein, and answering your question in a comment: yes, I'd 
   say you want to ensure your transactions are in SERIALIZABLE isolation 
   mode. There's no other isolation level for which that kind of snapshot 
   serialization makes sense, is there?
   
   
   Using the exposed functions in a more general sense, I think it's 
   important to note that the patch only intents to synchronize snapshots 
   at the start of the transaction, not contiguously. Thus, normal 
   transaction isolation applies for concurrent writes and each of the 
   transactions can commit or rollback independently.
   
   The timeout is nice, but is it really required? Isn't the normal query
   cancellation infrastructure sufficient?
   
   Hope that helps. Thanks for working on this issue.
   
   Regards
   
   Markus Wanner
   
   

7. Re: synchronized snapshots

   Joachim Wieland <joe@mcknight.de> — 2010-02-10T10:36:41Z

   Hi Markus,
   
   On Fri, Feb 5, 2010 at 6:29 PM, Markus Wanner <markus@bluegap.ch> wrote:
   >
   > So, let's first concentrate on the intended use case: allowing parallel
   > pg_dump. To me it seems like a pragmatic and quick solution, however, I'm
   > not sure if requiring superuser privileges is acceptable.
   
   http://www.postgresql.org/docs/8.4/static/backup-dump.html already
   states about pg_dump: "In particular, it must have read access to all
   tables that you want to back up, so in practice you almost always have
   to run it as a database superuser." so I think there is not a big loss
   here...
   
   
   > Reading the code, I'm missing the part that actually acquires the snapshot
   > for the transaction(s). After setting up multiple transactions with
   > pg_synchronize_snapshot and pg_synchronize_snapshot_taken, they still don't
   > have a snapshot, do they?
   
   They more or less get it "by chance" :-)  They acquire a snapshot when
   they call pg_synchronize_snapshot_taken() and if all the backends do
   it while the other backend holds the lock in shared mode, we know that
   the snapshot won't change, so they all get the same snapshot.
   
   
   > Also, you should probably ensure the calling transactions don't have a
   > snapshot already (let alone a transaction id).
   
   True...
   
   
   > In a similar vein, and answering your question in a comment: yes, I'd say
   > you want to ensure your transactions are in SERIALIZABLE isolation mode.
   > There's no other isolation level for which that kind of snapshot
   > serialization makes sense, is there?
   
   That's probably true but I didn't want to enforce this in the first
   place. As said, all backends just "happen" to get the same snapshot
   but they are still independent of each other so they are free to do
   whatever they want to in their transactions.
   
   
   > Using the exposed functions in a more general sense, I think it's important
   > to note that the patch only intents to synchronize snapshots at the start of
   > the transaction, not contiguously. Thus, normal transaction isolation
   > applies for concurrent writes and each of the transactions can commit or
   > rollback independently.
   >
   > The timeout is nice, but is it really required? Isn't the normal query
   > cancellation infrastructure sufficient?
   
   It seemed more robust and convenient to have an expiration in the
   backend itself. What would happen if you called
   pg_synchronize_snapshots() and if right after that your network
   connection dropped? Without the server noticing, it would continue to
   hold the lock and you could not log in anymore...
   
   But you are right: The proposed feature is a pragmatic and quick
   solution for pg_dump and similar but we might want to have a more
   general snapshot cloning procedure instead. Not having a delay for
   other activities at all and not requiring superuser privileges would
   be a big advantage over what I have proposed.
   
   
   Joachim
   
   

8. Re: synchronized snapshots

   Markus Wanner <markus@bluegap.ch> — 2010-02-10T18:05:38Z

   Hi Joachim,
   
   On Wed, 10 Feb 2010 11:36:41 +0100, Joachim Wieland <joe@mcknight.de>
   wrote:
   > http://www.postgresql.org/docs/8.4/static/backup-dump.html already
   > states about pg_dump: "In particular, it must have read access to all
   > tables that you want to back up, so in practice you almost always have
   > to run it as a database superuser." so I think there is not a big loss
   > here...
   
   Hm.. I doubt somewhat that's common practice. After all, read access to 
   all tables is still a *lot* less than superuser privileges. But yeah, 
   the documentation currently states that.
   
   > They more or less get it "by chance" :-)  They acquire a snapshot when
   > they call pg_synchronize_snapshot_taken()
   
   Oh, I see, calling the function by itself already acquires a snapshot.
   Even in case of a fast path call, it seems. Then your approach is correct.
   
   (I'd still feel more comfortable, it I had seen a 
   GetTransactionSnapshot() or something akin in there).
   
   > and if all the backends do
   > it while the other backend holds the lock in shared mode, we know that
   > the snapshot won't change, so they all get the same snapshot.
   
   Agreed, that works.
   
   (Ab)using the ProcArrayLock for synchronization is probably acceptable 
   for pg_dump, however, I'd rather take another approach for a more 
   general implementation.
   
   >> Also, you should probably ensure the calling transactions don't have a
   >> snapshot already (let alone a transaction id).
   > 
   > True...
   
   Hm.. realizing that a function call per-se acquires a snapshot, I fail 
   to see how we could check if we really acquired a snapshot. Consider the 
   following (admittedly stupid) example:
   
   BEGIN;
   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
   SELECT version();
     ... time goes by ...
   SELECT pg_synchronize_snapshot_taken(..);
   
   As it stands, your function would silently fail to "synchronize" the
   snapshots, if other transactions committed in between the two function 
   calls.
   
   > It seemed more robust and convenient to have an expiration in the
   > backend itself. What would happen if you called
   > pg_synchronize_snapshots() and if right after that your network
   > connection dropped? Without the server noticing, it would continue to
   > hold the lock and you could not log in anymore...
   
   Hm.. that's a point. Given this approach uses the ProcArrayLock, it's
   probably better to use an explicit timeout.
   
   > But you are right: The proposed feature is a pragmatic and quick
   > solution for pg_dump and similar but we might want to have a more
   > general snapshot cloning procedure instead. Not having a delay for
   > other activities at all and not requiring superuser privileges would
   > be a big advantage over what I have proposed.
   
   Agreed.
   
   Regards
   
   Markus Wanner
   
   

9. Re: synchronized snapshots

   Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> — 2010-02-10T18:15:32Z

   Markus Wanner wrote:
   > On Wed, 10 Feb 2010 11:36:41 +0100, Joachim Wieland <joe@mcknight.de>
   > wrote:
   >> http://www.postgresql.org/docs/8.4/static/backup-dump.html already
   >> states about pg_dump: "In particular, it must have read access to all
   >> tables that you want to back up, so in practice you almost always have
   >> to run it as a database superuser." so I think there is not a big loss
   >> here...
   > 
   > Hm.. I doubt somewhat that's common practice. After all, read access to
   > all tables is still a *lot* less than superuser privileges. But yeah,
   > the documentation currently states that.
   
   I think running as database owner gets you pretty far as far as pg_dump
   goes. It would be good to lift the limitation that you have to be superuser.
   
   >> But you are right: The proposed feature is a pragmatic and quick
   >> solution for pg_dump and similar but we might want to have a more
   >> general snapshot cloning procedure instead. Not having a delay for
   >> other activities at all and not requiring superuser privileges would
   >> be a big advantage over what I have proposed.
   > 
   > Agreed.
   
   Yeah, a big advantage of the proposed approach is that it's pretty
   simple to implement as an external module, allowing you to write scripts
   using it for older versions too.
   
   -- 
     Heikki Linnakangas
     EnterpriseDB   http://www.enterprisedb.com