Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure? - Mailing list pgsql-general
| From | Bryn Llewellyn |
|---|---|
| Subject | Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure? |
| Date | |
| Msg-id | EB82F064-FCB4-4101-A511-8ACD7AA5BF76@yugabyte.com Whole thread Raw |
| In response to | Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure? (Luca Ferrari <fluca1978@gmail.com>) |
| Responses |
Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?
|
| List | pgsql-general |
I have a version of the code that I attached with my first email in this thread where I’ve added “raise notice” invocations to show the txid as my p1() and p2() execute. Everything that I see is consistent with what I wrote separately in reply to adrian.klaver@aklaver.com:
<<
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.
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.
>>
I’m interested only in the case that my proc does txn control—i.e. my p2()—and so I’m not interested in how my p1() behaves.
About your “I believe there is no reason ever to begin a procedure with a rollback”, I already explained why I did that. My higher goal is to take advantage of the “serializable” isolation level to safely enforce a multi-row data rule. And I want my pspgsql proc—following the time-honored philosophy for stored procs—to own the complete implementation. I discovered that doing “rollback” as the first executable statement in my proc allowed me to do “set transaction isolation level serializable”. And I’ve found no other way to do this. As I mentioned, the “real” version of my proc, written this way does pass my functionality tests.
B.t.w., I noticed that “set transaction isolation level serializable” must be the very first statement after “rollback” (or “commit”). Even an invocation of txid_current() after the rollback and before the ““set transaction” causes this runtime error: “SET TRANSACTION ISOLATION LEVEL must be called before any query”.
About your PS, I’m new to this list—so forgive me if I didn’t follow proper etiquette. But as adrian.klaver@aklaver.com pointed out, the functionality under discussion here is part of the core PostgreSQL implementation.
On 07-Aug-2019, at 00:15, Luca Ferrari <fluca1978@gmail.com> wrote:
On Wed, Aug 7, 2019 at 12:19 AM Bryn Llewellyn <bryn@yugabyte.com> wrote:
In my opinion this is the point: a procedure must be "owner" of the
transaction to issue transaction control statements. You can watch
different behaviors placing here and there txid_current() before and
within p2 and re-run with autocommit off an on.
On autocmmmit = on the call opens a transaction and the procedure is
the only thing within the transaction, so it has full control. With
autocommit off you gain an implicit begin and the procedure is
possibly not the only thing you have within the transaction.
You can test it with:
testdb=# begin;
BEGIN
testdb=# call p2();
INFO: TXID 1300004994
ERROR: invalid transaction termination
CONTEXT: PL/pgSQL function p2() line 9 at ROLLBACK
testdb=# rollback;
ROLLBACK
Having said that, I believe there is no reason ever to begin a
procedure with a rollback.
Here's a small example that performs a few commits and rollback
<https://github.com/fluca1978/PostgreSQL-11-Quick-Start-Guide/blob/master/Chapter04/Chapter04_Listing29.sql>
Luca
P.S:
I don't believe that asking on a public mailing list for a company to
answer is the right thing to do, you can always ask themselves on your
own.
1. my call p2() starts a txn.
In my opinion this is the point: a procedure must be "owner" of the
transaction to issue transaction control statements. You can watch
different behaviors placing here and there txid_current() before and
within p2 and re-run with autocommit off an on.
On autocmmmit = on the call opens a transaction and the procedure is
the only thing within the transaction, so it has full control. With
autocommit off you gain an implicit begin and the procedure is
possibly not the only thing you have within the transaction.
You can test it with:
testdb=# begin;
BEGIN
testdb=# call p2();
INFO: TXID 1300004994
ERROR: invalid transaction termination
CONTEXT: PL/pgSQL function p2() line 9 at ROLLBACK
testdb=# rollback;
ROLLBACK
Having said that, I believe there is no reason ever to begin a
procedure with a rollback.
Here's a small example that performs a few commits and rollback
<https://github.com/fluca1978/PostgreSQL-11-Quick-Start-Guide/blob/master/Chapter04/Chapter04_Listing29.sql>
Luca
P.S:
I don't believe that asking on a public mailing list for a company to
answer is the right thing to do, you can always ask themselves on your
own.
pgsql-general by date: