Re: procedures and transactions

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