Re: LOCK TABLE is not allowed in a non-volatile function

On Wed, Apr 18, 2012 at 10:18 AM, Eliot Gable <
egable+pgsql-general(at)gmail(dot)com> wrote:

> On Tue, Apr 17, 2012 at 7:11 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Eliot Gable <egable+pgsql-general(at)gmail(dot)com> writes:
>> > When the trigger fires, I get this in my postgres.log file:
>> > 2012-04-17 16:57:15
>> EDT|test_db|169.254.5.138(56783)|****|[unknown]|30474
>> > WARNING: Failed to materialize the live_user_activity table; code
>> 0A000:
>> > LOCK TABLE is not allowed in a non-volatile function
>>
>> > I cannot find a single non-volatile function in the call path; so I am
>> > baffled on where this error message is coming from.
>>
>> I'm confused too, but I'm not going to guess at details of an incomplete
>> example; the problem could well be related to code you didn't show us.
>> Please provide a self-contained script that triggers this message.
>> Also, what PG version are we talking about?
>>
>> regards, tom lane
>>
>
> Thanks, Tom. I will try to get an entire example put together which I can
> post which will reproduce it. For your reference, the code I cut out was
> just inserts, updates, selects, and if/then/else statements. There were no
> other stored procedure calls or function calls present in any of the code I
> cut out.
>
>
Tom,

While attempting to reproduce this issue in a sanitized set of tables,
functions, and triggers, I was able to locate the issue. Apparently I did
have another function call in there inside my summarize_individuals()
function and that other function was marked as STABLE while trying to grab
a SHARE lock on a table for reading purposes. However, that function will
probably never be called by itself, and since PostgreSQL will grab the
appropriate lock on that table anyway, I was able to just remove the lock
statement to fix it. However, it seems to me there should be some way of
grabbing a read-only lock on a set of tables at the top of a function
marked STABLE simply for the purpose of enforcing the order in which tables
are locked, regardless of which order they are queried.

If VOLATILE function A grabs an EXCLUSIVE lock on Table A while STABLE
Function B grabs a SHARE lock on Table A and then Function A tries to grab
an EXCLUSIVE lock on Table B while Function B tries to grab a SHARE lock on
Table A, then we have a deadlock. Function B won't be able to get the SHARE
lock while Function A has the EXCLUSIVE, and Function A won't be able to
get the EXCLUSIVE while Function B has the SHARE. But if Function B, which
is STABLE, can grab SHARE locks at the top by grabbing the locks in the
same order that Function A tries, then the deadlock is averted.

In my particular case, it will not be an issue because the STABLE function
is being called only by other functions which are VOLATILE and already have
either a SHARE or SHARE ROW EXCLUSIVE lock on the table in question, and
those orders are enforced across all functions.

-Eliot