Skip site navigation (1) Skip section navigation (2)

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 (view raw or whole 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

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


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)





ALTER TABLE tbltmptests OWNER TO postgres;


-- Trigger: tbltmptests_tr on tbltmptests


-- DROP TRIGGER tbltmptests_tr ON tbltmptests;


CREATE TRIGGER tbltmptests_tr


  ON tbltmptests


  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?


pgsql-performance by date

Next:From: Patrick DonlinDate: 2010-07-15 18:48:04
Subject: Re: Identical query slower on 8.4 vs 8.3
Previous:From: Joshua D. DrakeDate: 2010-07-15 17:17:21
Subject: Re: Identical query slower on 8.4 vs 8.3

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group