Set Transaction Isolation level bug

Alois Maier <almaier@yahoo.com> writes:

test=> SHOW TRANSACTION ISOLATION LEVEL;
NOTICE: TRANSACTION ISOLATION LEVEL is READ COMMITTED
SHOW VARIABLE
test=> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET VARIABLE
test=> SHOW TRANSACTION ISOLATION LEVEL;
NOTICE: TRANSACTION ISOLATION LEVEL is READ COMMITTED
SHOW VARIABLE

The code is currently set up to reset to READ COMMITTED mode at
the start of each transaction. So you can do

regression=> BEGIN;
BEGIN
regression=> SHOW TRANSACTION ISOLATION LEVEL;
NOTICE: TRANSACTION ISOLATION LEVEL is READ COMMITTED
SHOW VARIABLE
regression=> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET VARIABLE
regression=> SHOW TRANSACTION ISOLATION LEVEL;
NOTICE: TRANSACTION ISOLATION LEVEL is SERIALIZABLE
SHOW VARIABLE
...

but the SET's effect goes away at END. If you're not inside a
transaction block then the SET's effect goes away immediately.

This may well be a dumb decision from a user-interface point of view,
but someone designed the code to work that way.

The SQL92 spec seems to say that SET TRANSACTION can only be executed
outside of transaction blocks, which would imply that the present
behavior is not compatible with the standard.

My own inclination is to think that if SET TRANSACTION ISOLATION LEVEL
is executed *inside* a BEGIN block, then it should set the IsoLevel for
that transaction block only, but if executed as a freestanding
transaction then it ought to set the default IsoLevel for subsequent
transactions. Comments?

regards, tom lane

Tom Lane wrote:

My own inclination is to think that if SET TRANSACTION ISOLATION LEVEL
is executed *inside* a BEGIN block, then it should set the IsoLevel for
that transaction block only, but if executed as a freestanding
transaction then it ought to set the default IsoLevel for subsequent

^^^^^^^^^^

transactions. Comments?

^^^^^^^^^^^^

My thoughts were to use ALTER SESSION for this.

Vadim

From bouncefilter Thu Sep 9 06:57:42 1999
Received: from orion.SAPserv.Hamburg.dsh.de (Tpolaris2.sapham.debis.de
[53.2.131.8]) by hub.org (8.9.3/8.9.3) with SMTP id GAA23521
for <pgsql-hackers@postgreSQL.org>; Thu, 9 Sep 1999 06:57:31 -0400 (EDT)
(envelope-from wieck@debis.com)
Received: by orion.SAPserv.Hamburg.dsh.de for pgsql-hackers@postgreSQL.org
id m11P1pG-0003kLC; Thu, 9 Sep 99 12:53 MET DST
Message-Id: <m11P1pG-0003kLC@orion.SAPserv.Hamburg.dsh.de>
From: wieck@debis.com (Jan Wieck)
Subject: Re: [HACKERS] Problem enabling pltcl
To: lamar.owen@wgcr.org (Lamar Owen)
Date: Thu, 9 Sep 1999 12:53:10 +0200 (MET DST)
Cc: patrickdlogan@home.com, pgsql-hackers@postgreSQL.org
Reply-To: wieck@debis.com (Jan Wieck)
In-Reply-To: <37D69D22.8876C0C4@wgcr.org> from "Lamar Owen" at Sep 8,
99 01:30:10 pm
X-Mailer: ELM [version 2.4 PL25]
Content-Type: text

Patrick Logan wrote:

ERROR: Unrecognized language specified in a CREATE FUNCTION:
'pltcl'. Recognized languages are sql, C, internal and the
created procedural languages.

The docs say that pltcl is enabled if it is built with the TCL
option. What am I missing?

CREATE LANGUAGE (command line utility 'createlang'). See the regression
test shell script (src/test/regress/regress.sh) for an example using
plpgsql. The PL's are not created and installed by default, apparently.

Yepp - it's a doc mistake because first I made it that way
and we decided later not to install by default into template1
and provide createlang instead.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #

Tom Lane wrote:

My own inclination is to think that if SET TRANSACTION ISOLATION LEVEL
is executed *inside* a BEGIN block, then it should set the IsoLevel for
that transaction block only, but if executed as a freestanding
transaction then it ought to set the default IsoLevel for subsequent
transactions. Comments?

This would seem logical (from my point of view) but I don't know whether
this is compatible with SQL92. In "A Guide to THE SQL STANDARD" , 4th edition
p. 59 C.J Date and Hugh Darwen state that SET TRANSACTION affects only the
next transaction; "If a transaction is is initiated for which no
corresponding SET TRANSACTION has been performed, the effect is as if such a
SET TRANSACTION had been performed with all options set to their default
value (presumably though the standard does not actually say as much)."
It seems that the standard does not forbid really your suggestion;but
I dont't know if this would be in the sense of the standard. Perhaps
one could say that a freestanding SET TRANSACTION sets the default
for subsequent transactions (But that might be stretching things a lot).
What do other databases do ?

Alois

__________________________________________________
Do You Yahoo!?
Bid and sell for free at http://auctions.yahoo.com