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