Re: procedures and transactions

On 2019-02-20 17:45, Rob Nikander wrote:
>> On Feb 20, 2019, at 10:07 AM, Peter Eisentraut <peter.eisentraut@2ndquadrant.com> 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