procedures and transactions

On Tuesday, February 19, 2019, Rob Nikander <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.

On 2/19/19 12:31 PM, Rob Nikander wrote:

Hi,

I’m trying to understand how procedures work with transactions. I tried the code below - it’s a simple procedure to print some notices and commit a transaction. If I call it from psql after a `begin`, then it gives an error. What does that error mean? Are procedures not allowed to commit/rollback if they are called within in an outer transaction?

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?

thanks,
Rob

create or replace procedure t_test(n integer)
as $$
begin
raise notice 'current isolation level: %', (select current_setting('transaction_isolation'));
raise notice 'current txid: %', (select txid_current());
raise notice '---';
commit;
raise notice 'current isolation level: %', (select current_setting('transaction_isolation'));
raise notice 'current txid: %', (select txid_current());
end;
$$ language plpgsql;

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

Don't use the begin;

call t_test(1);
NOTICE: current isolation level: read committed
NOTICE: current txid: 592
NOTICE: ---
NOTICE: current isolation level: read committed
NOTICE: current txid: 593
CALL

A function already starts in a transaction.

NOTICE: current isolation level: read committed
NOTICE: current txid: 111490
NOTICE: ---
ERROR: invalid transaction termination

--
Adrian Klaver
adrian.klaver@aklaver.com

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’)`.

Rob

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

On 2019-02-19 22:48, 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.

You can run SET TRANSACTION ISOLATION LEVEL in a procedure.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Feb 20, 2019, at 10:07 AM, Peter Eisentraut <peter.eisentraut@2ndquadrant.com> wrote:

You can run SET TRANSACTION ISOLATION LEVEL in a procedure.

I tried that before but I get this error:

create or replace procedure t_test(n integer)
as $$
begin
set transaction isolation level serializable;
raise notice 'current isolation level: %', (select current_setting('transaction_isolation'));
raise notice 'current txid: %', (select txid_current());
end;
$$ language plpgsql;

mydb=# call t_test(1);
ERROR: SET TRANSACTION ISOLATION LEVEL must be called before any query
CONTEXT: SQL statement "SET transaction isolation level serializable"

On 2019-02-20 17:45, Rob Nikander wrote:

On Feb 20, 2019, at 10:07 AM, Peter Eisentraut <peter.eisentraut@2ndquadrant.com> wrote:

You can run SET TRANSACTION ISOLATION LEVEL in a procedure.

I tried that before but I get this error:

create or replace procedure t_test(n integer)
as $$
begin

You need to commit or rollback the preceding transaction here. Yeah I
know it's a bit weird.

set transaction isolation level serializable;
raise notice 'current isolation level: %', (select current_setting('transaction_isolation'));
raise notice 'current txid: %', (select txid_current());
end;
$$ language plpgsql;

mydb=# call t_test(1);
ERROR: SET TRANSACTION ISOLATION LEVEL must be called before any query
CONTEXT: SQL statement "SET transaction isolation level serializable"

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services