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.kla...@aklaver.com <mailto:adrian.kla...@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.
>>

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.kla...@aklaver.com <mailto:adrian.kla...@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 <fluca1...@gmail.com> wrote:

On Wed, Aug 7, 2019 at 12:19 AM Bryn Llewellyn <b...@yugabyte.com> wrote:
> 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.

Reply via email to