On Wed, Apr 18, 2012 at 10:18 AM, Eliot Gable <egable+pgsql-general@xxxxxxxxx> wrote:
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.On Tue, Apr 17, 2012 at 7:11 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:Eliot Gable <egable+pgsql-general@xxxxxxxxx> 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 amI'm confused too, but I'm not going to guess at details of an incomplete
> baffled on where this error message is coming from.
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
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
