LOCK TABLE is not allowed in a non-volatile function

From: Eliot Gable <egable(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: LOCK TABLE is not allowed in a non-volatile function
Date: 2012-04-17 21:20:06
Message-ID: CAD-6L_WJ6WaE-YXyxRWAD2GCWtCBz_5fm=KHzGz0y11tLoTV0A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Eliot Gable 2012-04-17 21:20:55 LOCK TABLE is not allowed in a non-volatile function
Previous Message amador alvarez 2012-04-17 19:36:30 Re: Recreate primary key without dropping foreign keys?