Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?
| От | Bryn Llewellyn |
|---|---|
| Тема | Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure? |
| Дата | |
| Msg-id | EE6D19C1-1CA6-424B-91AC-63A1A64A5921@yugabyte.com обсуждение исходный текст |
| Ответ на | Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure? (Adrian Klaver <adrian.klaver@aklaver.com>) |
| Ответы |
Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?
(Luca Ferrari <fluca1978@gmail.com>)
Re: Why must AUTOCOMMIT be ON to do txn control in plpgsqlprocedure? (Kyotaro Horiguchi <horikyota.ntt@gmail.com>) |
| Список | pgsql-general |
Thanks Adrian. My aim with p1() was to show that its behavior, in each AUTOCOMMIT mode, aligns with my intuition. I’ve noticed the system-generated “begin” that you mention when I add this to my “/usr/local/var/postgres/postgresql.conf”:
log_statement = 'all'
log_directory = 'log'
logging_collector = on
HERE, on “AUTOCOMMIT”.
“When on (the default), each SQL command is automatically committed upon successful completion. To postpone commit in this mode, you must enter a BEGIN or START TRANSACTION SQL command. When off or unset, SQL commands are not committed until you explicitly issue COMMIT or END. The autocommit-off mode works by issuing an implicit BEGIN for you, just before any command that is not already in a transaction block and is not itself a BEGIN or other transaction-control command, nor a command that cannot be executed inside a transaction block (such as VACUUM).”
The way I understand it in your first case:
\set AUTOCOMMIT off
-- get clean start
begin;
delete from t;
commit;
call p1();
select n from t order by n;
rollback;
select n from t order by n;
rollback;
You have a implied BEGIN; before the begin; you wrap the delete in. Therefore you can do the rollback;. In the AUTOCOMMIT on there is only one transaction and it ends with the commit; after the delete. So when you attempt the rollback you get the error. REMEMBER in plpgsql Begin is not for transaction control, HERE (43.8. Transaction Management).
Have not worked through the second case yet.
--
Adrian Klaver
adrian.klaver@aklaver.com
log_directory = 'log'
logging_collector = on
and tail the most recent “/usr/local/var/postgres/log/postgresql-*.log” file. I assume that the “begin” is generated server-side—and not by the psql client-side program, other other such clients.
However, the intuition that informs my understanding of the behavior of p1() lets me down for p2(). My staring assumption was that if I want to do txn control in a plpgsql proc, then I must grant it that ability by stopping doing txn control at the outer level. But experiments—and what I’ve been told—tell me that I must do the opposite.
Here’s how I’ve tried to describe what I see for p2() with AUTOCOMMIT ON for myself:
1. my call p2() starts a txn.
2. However, during the execution of the proc, the usual autocommit behavior is programmatically turned off by explicit PostgreSQL code.
3. Other explicit PostgreSQL code makes “start transaction” inside a proc simply cause a runtime error under all circumstances. However, txns can be ended by “commit” or “rollback”. And new ones can be started—but only implicitly by executing a SQL statement that, as a top level SQL, would start a txn.
4. This is why “set transaction isolation level repeatable read” in my p2() is legal immediately after “rollback”—and produces the semantics I’d expect. At top level, and with autocommit turned on, it implicitly starts a txn—and you see the “begin” in the log file.
5. When the proc call ends, normal AUTOCOMMIT mode is turned on again, and a “commit” is issued automatically. This may, or may not, have something to do—as you can see by running p3() with AUTOCOMMIT ON.
create or replace procedure p3()
language plpgsql
as $$
begin
insert into t(n) values(17);
rollback;
insert into t(n) values(42);
end;
$$;
language plpgsql
as $$
begin
insert into t(n) values(17);
rollback;
insert into t(n) values(42);
end;
$$;
After calling it, you see just one row with the value 42—and it’s already durable.
This is why I want the folks who invented this behavior to describe the correct plplsql programmer’s mental model for me with the terminology that they designed.
adrian.klaver@aklaver.com wrote:
HERE, on “AUTOCOMMIT”.
“When on (the default), each SQL command is automatically committed upon successful completion. To postpone commit in this mode, you must enter a BEGIN or START TRANSACTION SQL command. When off or unset, SQL commands are not committed until you explicitly issue COMMIT or END. The autocommit-off mode works by issuing an implicit BEGIN for you, just before any command that is not already in a transaction block and is not itself a BEGIN or other transaction-control command, nor a command that cannot be executed inside a transaction block (such as VACUUM).”
The way I understand it in your first case:
\set AUTOCOMMIT off
-- get clean start
begin;
delete from t;
commit;
call p1();
select n from t order by n;
rollback;
select n from t order by n;
rollback;
You have a implied BEGIN; before the begin; you wrap the delete in. Therefore you can do the rollback;. In the AUTOCOMMIT on there is only one transaction and it ends with the commit; after the delete. So when you attempt the rollback you get the error. REMEMBER in plpgsql Begin is not for transaction control, HERE (43.8. Transaction Management).
Have not worked through the second case yet.
--
Adrian Klaver
adrian.klaver@aklaver.com
В списке pgsql-general по дате отправления:
Предыдущее
От: Adrian KlaverДата:
Сообщение: Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?
Следующее
От: Laura SmithДата:
Сообщение: Guidance needed on an alternative take on common prefix SQL