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 flat)
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

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-2014 The PostgreSQL Global Development Group