Postgres Pro
Downloads
Home   >   mailing lists

Thread: Question of using COPY on a table with triggers

Question of using COPY on a table with triggers

From
"Benjamin Krajmalnik"
Date:

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?

Re: Question of using COPY on a table with triggers

From
"Pierre C"
Date:
> Essentially, we insert a set of columns into a table, and each row fires
> a trigger function which calls a very large stored procedure


For inserting lots of rows, COPY is much faster than INSERT because it
parses data (a lot) faster and is more "data-stream-friendly". However the
actual inserting into the tbale and trigger-calling has to be done for
both.

If the trigger is a "very large stored procedure" it is very likely that
executing it will take a lot more time than parsing & executing the
INSERT. So, using COPY instead of INSERT will not gain you anything.

Re: Question of using COPY on a table with triggers

From
"Benjamin Krajmalnik"
Date:
That is what I thought.
The trigger calls a 3000 row stored procedure which does all of the calculations to aggregate data into 3 separate
tablesand then insert the raw data point into a 4th table.
 


> -----Original Message-----
> From: Pierre C [mailto:lists@peufeu.com]
> Sent: Thursday, July 15, 2010 4:47 PM
> To: Benjamin Krajmalnik; pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Question of using COPY on a table with triggers
> 
> > Essentially, we insert a set of columns into a table, and each row
> fires
> > a trigger function which calls a very large stored procedure
> 
> 
> For inserting lots of rows, COPY is much faster than INSERT because it
> parses data (a lot) faster and is more "data-stream-friendly". However
> the
> actual inserting into the tbale and trigger-calling has to be done for
> both.
> 
> If the trigger is a "very large stored procedure" it is very likely
> that
> executing it will take a lot more time than parsing & executing the
> INSERT. So, using COPY instead of INSERT will not gain you anything.

Re: Question of using COPY on a table with triggers

From
Tom Lane
Date:
"Benjamin Krajmalnik" <kraj@servoyant.com> writes:
> That is what I thought.
> The trigger calls a 3000 row stored procedure which does all of the calculations to aggregate data into 3 separate
tablesand then insert the raw data point into a 4th table. 

Youch.  Seems like you might want to rethink the idea of doing those
calculations incrementally for each added row.  Wouldn't it be better
to add all the new data and then do the aggregation once?

            regards, tom lane