Skip site navigation (1) Skip section navigation (2)

Re: SET TRANSACTION not compliant with SQL:2003

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: SET TRANSACTION not compliant with SQL:2003
Date: 2008-04-09 00:41:03
Message-ID: 10598.1207701663@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
[ 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 ;-)

In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2008-04-09 00:44:07
Subject: pgsql: Remove mention of the Berkeley origins of the alias "Postgres"
Previous:From: Andrew ChernowDate: 2008-04-09 00:31:05
Subject: Re: [PATCHES] libpq type system 0.9a

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group