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

On Wed, Apr 18, 2012 at 1:01 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Eliot Gable <egable+pgsql-general(at)gmail(dot)com> writes:
> > 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.
>
> Taking a lock is a side-effect, and stable functions are expected not
> to have side-effects. So I don't agree that this is a bug.
>
> However, there still might be an issue, because the CONTEXT trace that
> you showed certainly seemed to point where you thought it did. So I am
> wondering if there is a bug in the error-location-reporting stuff, or
> if that was an artifact of having stripped out too much information.
>
> regards, tom lane
>

After re-reading the LOCK modes and realizing that ACCESS SHARE is not the
same as SHARE, I believe you are correct; the only issue seems to be in the
CONTEXT trace failing to point out that the error occurred three function
calls deeper than what was reported. It seems it reported it in the first
function where the EXCEPTION handling was set up. It should have said it
was in user_log_slice() inside summarize_user_log() inside
summarize_individuals() inside materialize_live_user_activity(), etc. Going
from inner-most function to outer-most function, the first function with
exception handling was materialize_live_user_activity().