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

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.