Re: procedures and transactions

On 2/19/19 1:48 PM, Rob Nikander wrote:
> I thought if I had some application logic that needed a certain kind of 
> transaction (maybe a non-default isolation level), I could hide that 
> fact in a procedure. App code (Java/Python/whatever) could remain 
> unaware of transactions (except maybe needing to retry after a failure) 
> and simply send `call foo(?, ?)` to the DB. But maybe that kind of 
> design is not supported, and application code needs to start 
> transactions and set isolation levels. Is that accurate?  I supposed a 
> procedure could throw an exception if it doesn’t like the value in 
> `current_setting('transaction_isolation’)`.

Per the docs in the link David posted:

https://www.postgresql.org/docs/11/sql-call.html

"If CALL is executed in a transaction block, then the called procedure 
cannot execute transaction control statements. Transaction control 
statements are only allowed if CALL is executed in its own transaction."

So:

psql> begin;
psql> call t_test(1);


will not work.

> 
> Rob
> 
>> On Feb 19, 2019, at 2:38 PM, David G. Johnston 
>> <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:
>>
>> On Tuesday, February 19, 2019, Rob Nikander <rob.nikander@gmail.com 
>> <mailto:rob.nikander@gmail.com>> wrote:
>>
>>      Are procedures not allowed to commit/rollback if they are called
>>     within in an outer transaction?
>>
>>
>> https://www.postgresql.org/docs/11/sql-call.html
>>
>>     Also, I tried putting a `start transaction` command in the
>>     procedure. I got another error: `unsupported transaction command
>>     in PL/pgSQL`. Are procedures not allowed to start transactions? Or
>>     is there another command?
>>
>>
>> https://www.postgresql.org/docs/11/plpgsql-transactions.html
>>
>> David J.
>>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com