Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?

On Wed, Aug 7, 2019 at 8:28 PM Bryn Llewellyn <bryn(at)yugabyte(dot)com> wrote:
> 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.

I'm sorry, I still don't get the point in issuing a rollback as first
instruction because it restricts, at least in my opinion, the use case
of your procedure, that in turns restrict the mean of a procedure
(reusability). However, since you are dwealing with it, I'm fine.

>
> 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”.
>

Well, SET TRANSACTION ISOLATION must be the very first instruction of
every transaction, not only within the case you describe.

> About your PS, I’m new to this list—so forgive me if I didn’t follow proper etiquette. But as adrian(dot)klaver(at)aklaver(dot)com pointed out, the functionality under discussion here is part of the core PostgreSQL implementation.

Sorry, but in your original post you placed the sentence: "I’m hoping
that someone from 2ndQuadrant can answer my questions", that's why I
pointed out thrat, as people at 2ndquadrant have already told you,
this has nothing to do with 2ndquadrant specifically. And that's why I
replied that "hoping" for an answer is not as good as asking directly
to them.
And please stop quote posting and jumping to different part of the
message, because it makes reading it very hard.

Luca