On Wed, Aug 7, 2019 at 8:28 PM Bryn Llewellyn <bryn@yugabyte.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.
AndI want my pspgsql proc—following the time-honored philosophy for stored procs—to own the complete implementation. I
discoveredthat doing “rollback” as the first executable statement in my proc allowed me to do “set transaction
isolationlevel serializable”. And I’ve found no other way to do this. As I mentioned, the “real” version of my proc,
writtenthis 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”
causesthis 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.klaver@aklaver.compointed 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