SET TRANSACTION not compliant with SQL:2003

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.

...
<SQL2003>
If a <set transaction statement> that does not specify LOCAL is
executed, then
Case:
i) If an SQL-transaction is currently active, then an exception
condition is raised: invalid transaction
state — active SQL-transaction.
</SQL2003>
...
<SQL2003>
Case:
a) If LOCAL is not specified, then let TXN be the next SQL-transaction
for the SQL-agent.
b) Otherwise, let TXN be the branch of the active SQL-transaction at the
current SQL-connection.
</SQL2003>

The standard behaviour is that SET TRANSACTION defines the mode used in
the *next* transaction, not the current one. We should allow this
meaning, since programs written to spec will act differently with the
current implementation. We currently only change the *current*
transaction. Executing within the current transaction is supposed to
throw an error; that's probably too late to change, but the standard
does give some clues for other errors.

Proposed changes:

1. Solo SET TRANSACTION statements produce no WARNING, nor do anything.
This isn't the way the SQL:2003 standard specifies it should work.
We should take the values from SET TRANSACTION and apply them to the
*next* transaction:
- these will apply to next TXN, unless specifically overridden during
the START TRANSACTION command
- these values apply for one transaction only, after which we revert
back to the session default.

2. Duplicate calls to SET TRANSACTION are allowed within a transaction.
=> Should be ERROR: Transaction mode already set.

postgres=# begin;
BEGIN
postgres=# set transaction read only;
SET
postgres=# set transaction read only;
SET
postgres=# commit;
COMMIT

3. Multiple conflicting calls to SET TRANSACTION are allowed within a
transaction.
=> Should be ERROR: Transaction mode already set.

postgres=# begin;
BEGIN
postgres=# set transaction isolation level read committed;
SET
postgres=# set transaction isolation level serializable;
SET
postgres=# commit;
COMMIT

4. SET TRANSACTION can be called after a SAVEPOINT, i.e. it can be
called in a subtransaction.
=> Should be ERROR: SET TRANSACTION must not be called in a
subtransaction.
(Calling SET TRANSACTION ISOLATION LEVEL already throws this error, so
change should be small)

5. The standard uses the keyword LOCAL like this:
SET LOCAL TRANSACTION ...
which in this context means the part of a distributed (two-phased)
commit on this database.
We should accept, but ignore this keyword.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com