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@xxxxxxxxx <mailto:david.g.johnston@xxxxxxxxx>> wrote:
On Tuesday, February 19, 2019, Rob Nikander <rob.nikander@xxxxxxxxx
<mailto:rob.nikander@xxxxxxxxx>> 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@xxxxxxxxxxx
