Thank-you for the tips about this issue.
I found two things:
1. NOTIFY/LISTEN was causing performance to degrade badly over time. I
have reworked my code to poll the database instead.
2. There was a further function trigger that I didn't include in the
description. I wanted to keep a status of the jobs in the workqueue. I
created two triggers on the job table, that would update a 'status'
table:
> create table fileworkqueue.status(filetype_id smallint not null, num integer not null default 0 check (num >= 0));
> CREATE FUNCTION fileworkqueue.add_status() RETURNS TRIGGER AS
$$
BEGIN
UPDATE fileworkqueue.status SET num=num+1 WHERE filetype_id=NEW.filetype_id;
return NULL;
END;
$$ LANGUAGE plpgsql;
> CREATE FUNCTION fileworkqueue.del_status() RETURNS TRIGGER AS
$$
BEGIN
UPDATE fileworkqueue.status SET num=num-1 WHERE filetype_id=OLD.filetype_id;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER fileworkqueue_add_trigger
AFTER INSERT ON fileworkqueue.job
FOR EACH ROW EXECUTE PROCEDURE fileworkqueue.add_status();
CREATE TRIGGER fileworkqueue_del_trigger
AFTER DELETE ON fileworkqueue.job
FOR EACH ROW EXECUTE PROCEDURE fileworkqueue.del_status();
So there were actually two triggers in the original design:
log.file -> fileworkqueue.job -> fileworkqueue.status
When I removed the second trigger to the 'status' table, performance
jumped tenfold. But now I had no means of monitoring how my workqueue
was performing. I decided to do this in application code instead, via
IPC.
--
Jason Armstrong
