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

On Wed, Sep 11, 2013 at 4:19 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> 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

Shouldn't we do it for Set Constraints as well?

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com