Re: SET TRANSACTION not compliant with SQL:2003 - Mailing list pgsql-hackers

Tom's comment on this from the patch queue is that the standard assume
autocommit off, which affect some of your analysis below.

---------------------------------------------------------------------------

Simon Riggs wrote:
> 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
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://postgres.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +