Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?

On 8/7/19 11:46 AM, Bryn Llewellyn wrote:
> Thanks for your response, Kyotaro. I’m happy, now, to accept the rule 
> that “call proc_that_does_txn_control()” is legal only when AUTOCOMMIT 
> is ON. Esp. when I’m told (on twitter, by 2ndQuadrant’s Peter 
> Eisentraut, that this rule is “an implementation restriction, for the 
> most part.” See HERE 
> <https://twitter.com/petereisentraut/status/1158802910865756160>.
> 
> About your “In-procedure transaction control premises that no 
> transaction is active before calling the procedure”… yes. Nevertheless, 
> as the code that Umair Sahid showed us in the blog post that I 
> referenced in my email that started this thread, you can indeed start 
> end end transactions from an executing proc (as long as the session’s 
> AUTOCOMMIT mode s ON).
> 

The key is that the AUTOCOMMIT status is just a specific case of the 
general rule. The general rule being that a PROCEDURE cannot do 
transaction ending commands when it it called within an outer 
transaction. You can run into the same issue in other situations e.g. 
ORM's that start a transaction behind the scenes. In other words this is 
not psql specific.  As long as you understand the general rule then 
things become clearer.


-- 
Adrian Klaver
adrian.klaver@aklaver.com