From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Rob Nikander <rob(dot)nikander(at)gmail(dot)com>, Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: procedures and transactions |
Date: | 2019-02-19 22:34:09 |
Message-ID: | 2f23e41c-aeb5-b430-b8a3-48373b6db6d1@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)g(dot)johnston(at)gmail(dot)com <mailto:david(dot)g(dot)johnston(at)gmail(dot)com>> wrote:
>>
>> On Tuesday, February 19, 2019, Rob Nikander <rob(dot)nikander(at)gmail(dot)com
>> <mailto:rob(dot)nikander(at)gmail(dot)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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Julie Nishimura | 2019-02-19 23:02:47 | adding more space to the existing 9.6 cluster |
Previous Message | Rob Nikander | 2019-02-19 21:48:00 | Re: procedures and transactions |