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?