1

I have a trigger function that just copies inserted values to another table. Then, each night I need to drop and recreate this trigger for some export operation.

--trigger function 
CREATE OR REPLACE FUNCTION mev_copy() RETURNS trigger AS $mev_copy$
        BEGIN   INSERT INTO measurement_events_copy SELECT NEW.*;
            RETURN NEW;
        END;

The problem is that sometimes the trigger cannot start: CREATE TRRIGGER command never returns, and the DB seems locked-up and puts all queries in "PARSE waiting" status. Yesterday it was like that for 6 hours until I killed the create trigger.

The problem manifests consistently when table measurement_events is being autovacuumed. At the same time there is a new pending AccessExclusiveLock request for this table.

This is the create trigger, nothing special:

--CREATE TRIGGER that hangs sometimes
CREATE TRIGGER mev_copy AFTER INSERT ON measurement_events FOR EACH ROW EXECUTE PROCEDURE mev_copy();

Postgres version is 8.4. Any insights?

0

Cannot be done with PG 8.4. It's actually on the TODO of postgres to reduce trigger requirements:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.