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

On Sun, Feb  3, 2013 at 07:19:14AM +0000, Amit kapila wrote:
>
> On Saturday, February 02, 2013 9:08 PM Robert Haas wrote:
> On Fri, Feb 1, 2013 at 12:04 AM, Amit Kapila <amit.kapila@huawei.com> wrote:
> >> I think user should be aware of effect before using SET commands, as these are used at various levels
(TRANSACTION,SESSION, ...). 
>
> > Ideally, sure.  But these kinds of mistakes are easy to make.
>
>   You mean to say that after using SET Transaction, user can think below statements will
>   use modified transaction property. But I think if user doesn't understand by default
>   transaction will be committed after the statement execution, he might expect after
>   few statements he can rollback.
>
> > That's why LOCK and DECLARE CURSOR already emit errors in this case - why
> > should this one be any different?
>
> IMO, I think error should be given when it is not possible to execute current statement.
>
> Not only LOCK,DECLARE CURSOR but SAVEPOINT and some other statements also give the same error,
> so if we want to throw error for such behavior, we can find all such similar statements
> (SET TRANSACTION, SET LOCAL, etc) and do it for all.
>
> This can be helpful to some users, but not sure if such behavior (statement can be executed but it will not have any
sense) 
> can be always detectable and maintaible.

I have created the attached patch which issues an error when SET
TRANSACTION and SET LOCAL are used outside of transactions:

    test=> set transaction isolation level serializable;
    ERROR:  SET TRANSACTION can only be used in transaction blocks
    test=> reset transaction isolation level;
    ERROR:  RESET TRANSACTION can only be used in transaction blocks

    test=> set local effective_cache_size = '3MB';
    ERROR:  SET LOCAL can only be used in transaction blocks
    test=> set local effective_cache_size = default;
    ERROR:  SET LOCAL can only be used in transaction blocks

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +