First of all, a little background.
We have a table which is used as a trigger table for entering and processing data for a network monitoring system.
Essentially, we insert a set of columns into a table, and each row fires a trigger function which calls a very large stored procedure which aggregates data, etc. At that point, the row is deleted from the temp table.
Currently, records are transferred from the data collector as a series of multi-row inserts.
Before going through the exercise of recoding, and given the fact that each of this inserts fires of a trigger, will I see any noticeable performance?
The table definition follows:
CREATE TABLE tbltmptests
(
tmptestsysid bigserial NOT NULL,
testhash character varying(32),
testtime timestamp with time zone,
statusid integer,
replytxt text,
replyval real,
groupid integer,
CONSTRAINT tbltmptests_pkey PRIMARY KEY (tmptestsysid)
)
WITH (
OIDS=FALSE
);
ALTER TABLE tbltmptests OWNER TO postgres;
-- Trigger: tbltmptests_tr on tbltmptests
-- DROP TRIGGER tbltmptests_tr ON tbltmptests;
CREATE TRIGGER tbltmptests_tr
AFTER INSERT
ON tbltmptests
FOR EACH ROW
EXECUTE PROCEDURE fn_testtrigger();
Another question – is there anything special we need to do to handle the primary constraint field?
Now, on a related note and looking forward to the streaming replication of v9, will this work with it, since we have multiple tables being update by a trigger function?