Question of using COPY on a table with triggers

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?

Responses

Browse pgsql-performance by date

  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