Thread: LOCK TABLE is not allowed in a non-volatile function
I have a table which has a trigger on it. It is basically a log of user activity. The trigger is created like this:
CREATE TRIGGER user_log_user_activity_call_in_trig AFTER INSERT ON bbx_cdr.user_log FOR EACH ROW WHEN (
NEW.user_log_action = 'ringing'
) EXECUTE PROCEDURE user_log_user_activity_call_in_trigger_func();
CREATE OR REPLACE FUNCTION user_log_user_activity_call_in_trigger_func() RETURNS TRIGGER AS
$$
BEGIN
BEGIN
LOCK TABLE live_user_activity IN SHARE ROW EXCLUSIVE MODE;
LOCK TABLE user_activity_archive IN SHARE ROW EXCLUSIVE MODE;
... -- Do some stuff
PERFORM rotate_live_user_activity_table();
... -- Do some stuff
EXCEPTION WHEN OTHERS THEN
RAISE WARNING 'An exception occurred in user_log_activity_call_in_trigger_func() code %: %', SQLSTATE, SQLERRM;
END;
RETURN NEW;
END;
$$
LANGUAGE plpgsql VOLATILE;
Which calls this function:
CREATE OR REPLACE FUNCTION rotate_live_user_activity_table() RETURNS BOOLEAN AS
$$
BEGIN
BEGIN
LOCK TABLE live_user_activity IN SHARE ROW EXCLUSIVE MODE;
LOCK TABLE user_activity_archive IN SHARE ROW EXCLUSIVE MODE;
... -- Do some stuff, including move records to an archive table, if needed
/* If we don't have records or we already moved the records, then materialize the table */
PERFORM materialize_live_user_activity();
EXCEPTION WHEN OTHERS THEN
RAISE WARNING 'An error occurred while trying to rotate the live user activity records; code %: %', SQLSTATE, SQLERRM;
RETURN FALSE;
END;
RETURN TRUE;
END;
$$
LANGUAGE plpgsql VOLATILE;
Which calls this:
CREATE OR REPLACE FUNCTION materialize_live_user_activity() RETURNS BOOLEAN AS
$$
DECLARE
tmp RECORD;
BEGIN
BEGIN
LOCK TABLE live_user_activity IN SHARE ROW EXCLUSIVE MODE;
TRUNCATE TABLE live_user_activity;
INSERT INTO live_user_activity
SELECT nextval('user_activity_id_seq'),
date_trunc('day', CURRENT_TIMESTAMP)::DATE,
i.*,
NULL::TIMESTAMP WITH TIME ZONE,
FALSE
FROM summarize_individuals(date_trunc('day', CURRENT_TIMESTAMP)::TIMESTAMP, CURRENT_TIMESTAMP) AS i;
EXCEPTION WHEN OTHERS THEN
RAISE WARNING 'Failed to materialize the live_user_activity table; code %: %', SQLSTATE, SQLERRM;
RETURN FALSE;
END;
RETURN TRUE;
END;
$$
LANGUAGE plpgsql VOLATILE;
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
2012-04-17 16:57:15 EDT|test_db|169.254.5.138(56783)|****|[unknown]|30474 CONTEXT: SQL statement "SELECT materialize_live_user_activity()"
PL/pgSQL function "rotate_live_user_activity_table" line 22 at PERFORM
SQL statement "SELECT rotate_live_user_activity_table()"
PL/pgSQL function "user_log_user_activity_call_in_trigger_func" line 22 at PERFORM
SQL statement "<snip>"
PL/pgSQL function "live_stats_channel_trigger_func" line 262 at SQL statement
The "live_stats_channel_trigger_func" is also a VOLATILE trigger function structured the same way as above with a lot more lock table statements in there.
The "summarize_individuals" function there is also VOLATILE and it calls "summarize_user_log" which is also VOLATILE.
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 would be thankful for any ideas anyone might have on where this error message might be coming from or how to locate where it is coming from.
Thanks.
Eliot Gable
"We do not inherit the Earth from our ancestors: we borrow it from our children." ~David Brower
"I decided the words were too conservative for me. We're not borrowing from our children, we're stealing from them--and it's not even considered to be a crime." ~David Brower
"Esse oportet ut vivas, non vivere ut edas." (Thou shouldst eat to live; not live to eat.) ~Marcus Tullius Cicero
Eliot Gable <egable+pgsql-general@gmail.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
On Tue, Apr 17, 2012 at 7:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Eliot Gable <egable+pgsql-general@gmail.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 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
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 Wed, Apr 18, 2012 at 10:18 AM, Eliot Gable <egable+pgsql-general@gmail.com> 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@sss.pgh.pa.us> wrote:Eliot Gable <egable+pgsql-general@gmail.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 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
Eliot Gable <egable+pgsql-general@gmail.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
On Wed, Apr 18, 2012 at 1:01 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Eliot Gable <egable+pgsql-general@gmail.com> writes:Taking a lock is a side-effect, and stable functions are expected not
> 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.
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().
Eliot Gable <egable+pgsql-general@gmail.com> writes:
> On Wed, Apr 18, 2012 at 1:01 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> However, there still might be an issue, because the CONTEXT trace that
>> you showed certainly seemed to point where you thought it did.
> 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.
Oh! Yes, that's to be expected, because so far as Postgres is concerned
it's logging the location of the RAISE WARNING command. You've only
told it to print the SQLERRM string, and nothing else:
RAISE WARNING 'An error occurred while trying to rotate the live user
activity records; code %: %', SQLSTATE, SQLERRM;
As of 9.2 there is a way to get the context string for the original
error (GET STACKED DIAGNOSTICS) which you could then include in the
RAISE message. That hasn't made it to any released versions
unfortunately.
regards, tom lane
On Wed, Apr 18, 2012 at 3:47 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Eliot Gable <egable+pgsql-general@gmail.com> writes:
> On Wed, Apr 18, 2012 at 1:01 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:>> However, there still might be an issue, because the CONTEXT trace that
>> you showed certainly seemed to point where you thought it did.> After re-reading the LOCK modes and realizing that ACCESS SHARE is not theOh! Yes, that's to be expected, because so far as Postgres is concerned
> 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's logging the location of the RAISE WARNING command. You've only
told it to print the SQLERRM string, and nothing else:As of 9.2 there is a way to get the context string for the original
RAISE WARNING 'An error occurred while trying to rotate the live user
activity records; code %: %', SQLSTATE, SQLERRM;
error (GET STACKED DIAGNOSTICS) which you could then include in the
RAISE message. That hasn't made it to any released versions
unfortunately.
regards, tom lane
Thanks, Tom. I will keep that in mind for when we update our Postgres build on our systems.
On Wed, Apr 18, 2012 at 3:47 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Eliot Gable <egable+pgsql-general@gmail.com> writes:
> On Wed, Apr 18, 2012 at 1:01 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:>> However, there still might be an issue, because the CONTEXT trace that
>> you showed certainly seemed to point where you thought it did.> After re-reading the LOCK modes and realizing that ACCESS SHARE is not theOh! Yes, that's to be expected, because so far as Postgres is concerned
> 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's logging the location of the RAISE WARNING command. You've only
told it to print the SQLERRM string, and nothing else:As of 9.2 there is a way to get the context string for the original
RAISE WARNING 'An error occurred while trying to rotate the live user
activity records; code %: %', SQLSTATE, SQLERRM;
error (GET STACKED DIAGNOSTICS) which you could then include in the
RAISE message. That hasn't made it to any released versions
unfortunately.
regards, tom lane
Is there a way to print out context in 9.0.1 when doing exception handling and raising a message? Right now, all I get is my error code, description of the error, and no idea what the specific statement is that is generating the error in the first place. Am I missing something, or is this just not possible in 9.0.1?