Postgres Pro
Downloads
Home   >   mailing lists

Thread: SET SESSION TRANSACTION command

SET SESSION TRANSACTION command

From
"Qingqing Zhou"
Date:
test=# SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET
test=# show transaction_isolation;
 transaction_isolation
-----------------------
 read committed
(1 row)

Is this a bug by any chance or we do it intentionally?

Regards,
Qingqing

Re: SET SESSION TRANSACTION command

From
Tom Lane
Date:
"Qingqing Zhou" <zhouqq@cs.toronto.edu> writes:
> test=# SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SESSION is taken as a noise word here.  Possibly we should disallow this
syntax altogether, since it isn't SQL-spec ... but I'm not sure it's
worth contorting the grammar enough to do that.

The converse case is

    SET LOCAL SESSION CHARACTERISTICS AS ...

which is also allowed but probably is going to do something confusing.

            regards, tom lane

Re: SET SESSION TRANSACTION command

From
Qingqing Zhou
Date:

On Sun, 1 Jan 2006, Tom Lane wrote:

> "Qingqing Zhou" <zhouqq@cs.toronto.edu> writes:
> > test=# SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
>
> SESSION is taken as a noise word here.  Possibly we should disallow this
> syntax altogether, since it isn't SQL-spec ... but I'm not sure it's
> worth contorting the grammar enough to do that.
>

Other GUC variables work well of the SET SESSION after a few tests. How
about we try to support it? There are 3 levels (not including LOCAL
SESSION which I don't understand) of setting isoLevel and list them by
their priority:

isoLevel[0]: Transaction level : BEGIN TRANSACTION ISOLATION
isoLevel[1]: Session level:  SET SESSION TRANSACTION
isoLevel[2]: global level: default_transaction_isolation in postgres.conf
or by SET;

So the algorithm of setting XactIsoLevel at beginning of transaction will
be:

/* StartTransaction() */
for (i=3; i>0; i--)
    if (isoLevel[i] has a value)
        XactIsoLevel = isoLevel[i];

Or is there a consideration that we can't do this (means which will cause
broken transaction semantics something)?

Regards,
Qingqing

Re: SET SESSION TRANSACTION command

From
Tom Lane
Date:
Qingqing Zhou <zhouqq@cs.toronto.edu> writes:
> On Sun, 1 Jan 2006, Tom Lane wrote:
>> "Qingqing Zhou" <zhouqq@cs.toronto.edu> writes:
>>> test=# SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
>>
>> SESSION is taken as a noise word here.  Possibly we should disallow this
>> syntax altogether, since it isn't SQL-spec ... but I'm not sure it's
>> worth contorting the grammar enough to do that.

> Other GUC variables work well of the SET SESSION after a few tests.

This one works fine too: you misunderstand what the semantics are.
There is default_transaction_isolation which is the session-level
setting, and there is transaction_isolation which is, by definition,
reset to default_transaction_isolation at the start of every
transaction.

The only way the above command should do anything different than what
it does now is to give an error, on the grounds that it's a
contradiction in terms.  The SQL-spec spelling of what you are
interested in is
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;

            regards, tom lane