procedures and transactions

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

procedures and transactions

Rob Nikander
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);

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




Reply | Threaded
Open this post in threaded view
|

Re: procedures and transactions

David G Johnston
On Tuesday, February 19, 2019, Rob Nikander <[hidden email]> wrote:
 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?


David J.

Reply | Threaded
Open this post in threaded view
|

Re: procedures and transactions

Adrian Klaver-4
In reply to this post by Rob Nikander
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
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: procedures and transactions

Rob Nikander
In reply to this post by David G Johnston
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 Feb 19, 2019, at 2:38 PM, David G. Johnston <[hidden email]> wrote:

On Tuesday, February 19, 2019, Rob Nikander <[hidden email]> wrote:
 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?


David J.


Reply | Threaded
Open this post in threaded view
|

Re: procedures and transactions

Adrian Klaver-4
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
>> <[hidden email] <mailto:[hidden email]>> wrote:
>>
>> On Tuesday, February 19, 2019, Rob Nikander <[hidden email]
>> <mailto:[hidden email]>> 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
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: procedures and transactions

Peter Eisentraut-6
In reply to this post by Rob Nikander
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

Reply | Threaded
Open this post in threaded view
|

Re: procedures and transactions

Rob Nikander


> On Feb 20, 2019, at 10:07 AM, Peter Eisentraut <[hidden email]> 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"
Reply | Threaded
Open this post in threaded view
|

Re: procedures and transactions

Peter Eisentraut-6
On 2019-02-20 17:45, Rob Nikander wrote:
>> On Feb 20, 2019, at 10:07 AM, Peter Eisentraut <[hidden email]> 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