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
