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

On Wed, Sep 11, 2013 at 4:19 AM, Bruce Momjian <bruce@momjian.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.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

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


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