From: | Rob Nikander <rob(dot)nikander(at)gmail(dot)com> |
---|---|
To: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | procedures and transactions |
Date: | 2019-02-19 20:31:16 |
Message-ID: | FB0D74E9-6CAE-4FDD-BAD6-8AA31169F363@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2019-02-19 20:38:55 | Re: procedures and transactions |
Previous Message | Stephen Frost | 2019-02-19 17:25:24 | Re: PG Upgrade with hardlinks, when to start/stop master and replicas |