Re: procedures and transactions

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
printsome notices and commit a transaction. If I call it from psql after a `begin`, then it gives an error. What does
thaterror 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
commandin 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