Search Postgresql Archives

Re: procedures and transactions

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxx




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux