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();
It is roughly structured like this:
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; code0A000:
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.
On Wed, Apr 18, 2012 at 10:18 AM, Eliot Gable <
egable+pgsql-general@gmail.com> wrote:
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:15EDT|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
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:
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 tograb
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 whichtables
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().
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
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
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
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
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?