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

Re: Suggestion: Issue warning when calling SET TRANSACTION outside transaction block

From: Amit Kapila <amit(dot)kapila(at)huawei(dot)com>
To: "'Morten Hustveit'" <morten(at)eventures(dot)vc>, <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Suggestion: Issue warning when calling SET TRANSACTION outside transaction block
Date: 2013-02-01 05:04:33
Message-ID: 004001ce0039$a078f2e0$e16ad8a0$@kapila@huawei.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Wednesday, January 30, 2013 6:53 AM Morten Hustveit wrote:
> Hi!
> 
> Calling "SET TRANSACTION ISOLATION LEVEL ..." outside a transaction
> block has no effect.  This is unlike "LOCK ..." and "DECLARE foo
> CURSOR FOR ...", which both raise an error.  This is also unlike
> MySQL, where such a statement will affect the next transaction
> performed.  There's some risk of data corruption, as a user might
> assume he's working on a snapshot, while in fact he's not.

The behavior of "SET TRANSACTION ISOLATION LEVEL ..." needs to be compared with "SET LOCAL ..".
These commands are used to set property of current transaction in which they are executed.

The usage can be clear with below function, where it is used to set the current transaction property.

Create or Replace function temp_trans() Returns boolean AS $$ 
Declare sync_status boolean; 
Begin 
Set LOCAL synchronous_commit=off; 
show synchronous_commit into sync_status; 
return sync_status; 
End; 
$$ Language plpgsql;
 
> I suggest issuing a warning, notice or error message when "SET
> TRANSACTION ..." is called outside a transaction block, possibly
> directing the user to the "SET SESSION CHARACTERISTICS AS TRANSACTION
> ..." syntax.
 
It is already mentioned in documentation that SET Transaction command is used to set characteristics of current transaction (http://www.postgresql.org/docs/9.2/static/sql-set-transaction.html). 

I think user should be aware of effect before using SET commands, as these are used at various levels (TRANSACTION, SESSION, ...).

With Regards,
Amit Kapila.



In response to

pgsql-hackers by date

Next:From: Pavel StehuleDate: 2013-02-01 05:22:59
Subject: Re: proposal - assign result of query to psql variable
Previous:From: Tom LaneDate: 2013-02-01 04:17:07
Subject: Re: parameter info?

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