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

On Wed, 2008-03-12 at 15:51 -0400, Bruce Momjian wrote:
> Tom's comment on this from the patch queue is that the standard assume
> autocommit off, which affect some of your analysis below.

This isn't an important area for me, but I don't think we follow the
standard in the way we do it now and we should at least note that.

The standard specifically refers to "the next transaction", so the
setting of autocommit is irrelevant here.

> 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
> 
--  Simon Riggs 2ndQuadrant  http://www.2ndQuadrant.com 
 PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk