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(dot)kapila(at)huawei(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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