Re: SET TRANSACTION not compliant with SQL:2003

[ back to this patch ]

Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> The SQL:2003 standard definition of SET TRANSACTION differs in major
> ways from PostgreSQL's, which produces some interesting behaviour.

> We currently claim conformance, though this is not accurate.

I'm still of the opinion that this analysis is based on a faulty
analogy.

The standard's discussion is based on autocommit-off behavior.
The expected usage pattern, starting from a not-in-a-transaction
state, is

set transaction something-or-other;
sql-command;
sql-command;
...
commit;

Because we are non-compliant in having autocommit-on behavior, the
expected usage pattern for us is

begin;
set transaction something-or-other;
sql-command;
sql-command;
...
commit;

Simon's proposed patch would allow

set transaction something-or-other;
begin;
sql-command;
sql-command;
...
commit;

but there is no precedent in either the standard or our historical
behavior for supporting that, and I don't think it's useful enough
to justify introducing a potential backward-compatibility problem.

I believe the reason the spec is written in the particular way that
it is is that they wanted to allow, e.g.,

set transaction isolation level serializable;
set transaction read only;
sql-command;
sql-command;
...
commit;

and if SET TRANSACTION actually started a transaction then the second of
these would have to fail. So they're forced into pretending that SET
TRANSACTION should be a non-transaction-starting command that affects
the next transaction. (BTW, they had ample reason to regret that
decision when they added 2PC, and were forced into the ungraceful
position of requiring mutually contradictory behavior between the two
cases.) We arrive at the same behavior (modulo BEGIN) in a different
way, but the user-visible result is the same.

So I'm of the opinion that there's no good reason to change either our
code or our docs. The standard-incompatibility is with BEGIN, not
SET TRANSACTION, and it's already documented.

regards, tom lane

PS: the proposed patch is buggy as can be anyway: it applies the change
even if !doit, and it causes START TRANSACTION ISOLATION LEVEL xxx
to affect not only the current but the next transaction, which surely
cannot be justified by any reading of the spec ;-)