Thread: Re: [SQL] reliable lock inside stored procedure (SOLVED)

Sebastian Böhm <psql@seb.exse.net> writes:
> Am 03.11.2008 um 12:06 schrieb Richard Huxton:
>> It's not possible to have a LOCK statement outside of a
>> transaction. It's just not meaningful to have a transaction that only
>> has a LOCK statement in it.

> as postgres does not warn you about this, this may lead to not so easy  
> to spot bugs.

That's a good point.  We throw error for DECLARE CURSOR outside a
transaction block, since it's obviously a mistake.  I wonder whether
we shouldn't equally throw error for LOCK outside a transaction block.

I can sort of imagine some corner cases where
lock-and-immediately-release would be the intended behavior, but that
sure seems a whole lot less probable than it being user error.
And you could always throw BEGIN/COMMIT into the command if that
really was what you wanted.

Objections anyone?
        regards, tom lane

> That's a good point.  We throw error for DECLARE CURSOR outside a
> transaction block, since it's obviously a mistake.  I wonder whether
> we shouldn't equally throw error for LOCK outside a transaction block.
>
> I can sort of imagine some corner cases where
> lock-and-immediately-release would be the intended behavior, but that
> sure seems a whole lot less probable than it being user error.
> And you could always throw BEGIN/COMMIT into the command if that
> really was what you wanted.
>
> Objections anyone?

No, I've been bitten by this myself.

...Robert

"Robert Haas" <robertmhaas@gmail.com> writes:
>> That's a good point.  We throw error for DECLARE CURSOR outside a
>> transaction block, since it's obviously a mistake.  I wonder whether
>> we shouldn't equally throw error for LOCK outside a transaction block.
>> 
>> Objections anyone?

> No, I've been bitten by this myself.

OK, done in CVS HEAD.
        regards, tom lane