create trigger hangs

Question

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?

Answer 1

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

• https://wiki.postgresql.org/wiki/Todo
• http://www.postgresql.org/message-id/[email protected]