procedures and transactions

Поиск
Список
Период
Сортировка
От Rob Nikander
Тема procedures and transactions
Дата
Msg-id FB0D74E9-6CAE-4FDD-BAD6-8AA31169F363@gmail.com
обсуждение исходный текст
Ответы Re: procedures and transactions  ("David G. Johnston" <david.g.johnston@gmail.com>)
Re: procedures and transactions  (Adrian Klaver <adrian.klaver@aklaver.com>)
Список pgsql-general
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);

NOTICE:  current isolation level: read committed
NOTICE:  current txid: 111490
NOTICE:  ---
ERROR:  invalid transaction termination





В списке pgsql-general по дате отправления:

Предыдущее
От: Stephen Frost
Дата:
Сообщение: Re: PG Upgrade with hardlinks, when to start/stop master and replicas
Следующее
От: "David G. Johnston"
Дата:
Сообщение: Re: procedures and transactions