Search Postgresql Archives

LOCK TABLE is not allowed in a non-volatile function

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux