title: Does PostgreSQL count nested BEGIN and END statements, even though it doesn't support autonomous transactions?
body: <p>I'm working on some C++ code that makes use of libpq to interact with a PostgreSQL database, and I've started writing some functions, each of which internally starts a transaction, applies several changes to the DB, then ends the transaction.</p>
  
  <p>I would now like to call one of these functions in combination with another DML statement, all executed in the same transaction. Something like this (note: very simplified):</p>
  
  <pre><code>void doFoo(PGconn* con) {
      PQexec(con, "BEGIN" );
      PQexec(con, "insert into ..." );
      PQexec(con, "delete from ..." );
      PQexec(con, "END" );
  }
  
  void doFooPlus(PGconn* con) {
      PQexec(con, "BEGIN" );
      doFoo(con);
      PQexec(con, "update ..." );
      PQexec(con, "END" );
  }
  
  void main(void) {
      doFooPlus(con);
  }
  </code></pre>
  
  <p>However, based on everything I've read, it looks like PostgreSQL may not honor this kind of nesting of transactions. I want to be clear: I don't need autonomous transactions, which I know PostgreSQL does not support, and I don't need any explicit rollback functionality to a nested (or otherwise) BEGIN statement, which could be accomplished with savepoints, and is obviously not what the above code is trying to do at any point. I just want to confirm whether the above code will do what one would hope it would do from the structure of the code.</p>
  
  <p>Let me try to clarify further. Here is what PostgreSQL will end up seeing from the above C++ (well actually just straight C) code:</p>
  
  <pre><code>BEGIN
  BEGIN
  insert into ...
  delete from ...
  END
  update ...
  END
  </code></pre>
  
  <p>What I'm worried about is the second BEGIN call being <em>completely</em> ignored, and so the first END call would then end the transaction started by the first BEGIN call, and so the update statement would then not be included in the atomicity of the transaction block.</p>
  
  <p>According to <a href="http://www.postgresql.org/docs/9.4/static/sql-begin.html" rel="nofollow">http://www.postgresql.org/docs/9.4/static/sql-begin.html</a>:</p>
  
  <blockquote>
    <p>Issuing BEGIN when already inside a transaction block will provoke a warning message. The state of the transaction is not affected. To nest transactions within a transaction block, use savepoints (see SAVEPOINT).</p>
  </blockquote>
  
  <p>The comment about savepoints seems kind of misleading to me; PostgreSQL does not support nested (autonomous) transactions; savepoints only provide a way to roll back to a point in the middle of an existing transaction. Savepoints are not transactions in themselves, so I cannot replace the BEGIN and END in <code>doFoo()</code> with them, because then I would not be able to call <code>doFoo()</code> by itself (meaning not from <code>doFooPlus()</code>) and still get transaction atomicity for the insert and delete executed by <code>doFoo()</code>.</p>
  
  <p>The comment about the state of the transaction "not [being] affected" by the nested BEGIN does seem to imply that PostgreSQL will not count it, and will in fact completely ignore it, but the quoted phrase doesn't <em>quite</em> make it clear enough to me that I wasn't going to ask this on Stack Overflow. I'm still holding out an ounce of hope that PostgreSQL will still count the nested BEGIN towards some kind of internal "nesting level", which will be decremented by the first END, and then decremented again by the second END, causing the entire sequence of statements to be treated as one atomic transaction.</p>
  
  <p>So, can someone please confirm whether PostgreSQL does this or not? And if not, can you please provide recommendations on how best to solve this problem in my code? I was thinking of adding a bool parameter to allow the caller of <code>doFoo()</code> to specify whether to create a transaction or not, to which <code>doFooPlus()</code> could pass false.</p>
  
  <p><strong>Edit:</strong> For anyone who's interested, I realized today that I could test this question myself fairly easily, simply by writing a program that does something like what the above example code is trying to do, and then examining its effect on the database.</p>
  
  <p>I won't go into too much detail regarding the internals of the program, but the command below basically runs <code>create table t1 (a int, b int, c int ); insert into t1 (a,b,c) values (0,0,0);</code>, then runs each of the given SQL statements in order, and finally prints the resulting table data, so you can see that the second <code>begin</code> and the final <code>rollback</code> were completely ignored:</p>
  
  <pre><code>&gt; pgtestabc begin begin 'update t1 set a=1;' 'update t1 set b=1;' end 'update t1 set c=1;' rollback;
  executing "begin"...done
  executing "begin"...done
  executing "update t1 set a=1;"...done
  executing "update t1 set b=1;"...done
  executing "end"...done
  executing "update t1 set c=1;"...done
  executing "rollback"...done
  1|1|1
  </code></pre>
  
  <p>Also note that you can't do this exact test by simply running the SQL statements in a batch from a GUI client such as pgAdmin III. That particular client seems to do some magic regarding transactions; it seems to enclose the batch in an implicit transaction, so that a <code>rollback</code> statement will cause the previous statements to be rolled back (even though you also get a "NOTICE: there is no transaction in progress" message in the Messages pane...), except it also still somehow respects <code>begin</code>...<code>end</code> blocks (ignoring nested <code>begin</code> statements as demonstrated above) within the batch, which confusingly seems awfully similar to autonomous transactions, which postgres does <em>not</em> support, as I believe we've established in this thread. So, for example, if you were to run the above 7 statements directly in pgAdmin III, the data you'd end up getting is <code>1|1|0</code>.</p>
  
  <p>But regardless of that unintentional obfuscation, the incontrovertible conclusion is that postgres does <em>not</em> count nesting levels of <code>begin</code>...<code>end</code> blocks, so you must take care to only ever place yourself within one top-level <code>begin</code>...<code>end</code> block.</p>