Question of using COPY on a table with triggers

От: Benjamin Krajmalnik
Тема: Question of using COPY on a table with triggers
Дата: ,
Msg-id: F4E6A2751A2823418A21D4A160B68988614706@fletch.stackdump.local
(см: обсуждение, исходный текст)
Ответы: Re: Question of using COPY on a table with triggers  ("Pierre C")
Список: pgsql-performance

Скрыть дерево обсуждения

Question of using COPY on a table with triggers  ("Benjamin Krajmalnik", )
 Re: Question of using COPY on a table with triggers  ("Pierre C", )
  Re: Question of using COPY on a table with triggers  ("Benjamin Krajmalnik", )
   Re: Question of using COPY on a table with triggers  (Tom Lane, )

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?


В списке pgsql-performance по дате сообщения:

От: "Benjamin Krajmalnik"
Дата:
Сообщение: Re: Question of using COPY on a table with triggers
От: "Joshua D. Drake"
Дата:
Сообщение: Re: Identical query slower on 8.4 vs 8.3