Обсуждение: 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
On Tue, Apr 17, 2012 at 5:20 PM, Eliot Gable <egable@gmail.com> wrote:
According to the documentation, the current_timestamp family of functions is stable, could that be the cause? Better yet, should it?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.
--
Mike Nolan
No, I have lots of calls to current_timestamp inside volatile functions which lock tables without complaints. I am beginning to think I hit some sort of bug. This is PostgreSQL 9.0.1.
--
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
On Tue, Apr 17, 2012 at 5:55 PM, Michael Nolan <htfoot@gmail.com> wrote:
According to the documentation, the current_timestamp family of functions is stable, could that be the cause? Better yet, should it?On Tue, Apr 17, 2012 at 5:20 PM, Eliot Gable <egable@gmail.com> wrote: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.
--
Mike Nolan
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