From: | "Benjamin Krajmalnik" <kraj(at)servoyant(dot)com> |
---|---|
To: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | Question of using COPY on a table with triggers |
Date: | 2010-07-15 17:27:46 |
Message-ID: | F4E6A2751A2823418A21D4A160B68988614706@fletch.stackdump.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Patrick Donlin | 2010-07-15 18:48:04 | Re: Identical query slower on 8.4 vs 8.3 |
Previous Message | Joshua D. Drake | 2010-07-15 17:17:21 | Re: Identical query slower on 8.4 vs 8.3 |