Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Y1dza-0001Q7-1u for pgsql-performance@arkaria.postgresql.org; Thu, 18 Dec 2014 16:31:58 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Y1dzZ-0006ue-Ia for pgsql-performance@arkaria.postgresql.org; Thu, 18 Dec 2014 16:31:57 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1Y1dzY-0006uT-8l for pgsql-performance@postgresql.org; Thu, 18 Dec 2014 16:31:56 +0000 Received: from smtp1.oma.be ([193.190.144.13]) by magus.postgresql.org with esmtps (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1Y1dzQ-0005Qc-AM for pgsql-performance@postgresql.org; Thu, 18 Dec 2014 16:31:52 +0000 Received: from snow.oma.be (snow.oma.be [193.190.249.132]) by smtp1.oma.be (8.14.3/8.14.3/SuSE Linux 0.8) with ESMTP id sBIGVkxS005236 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO); Thu, 18 Dec 2014 16:31:46 GMT From: Alessandro Ipe To: pgsql-performance@postgresql.org Cc: Tom Lane , Torsten Zuehlsdorff Subject: Re: Excessive memory used for INSERT Date: Thu, 18 Dec 2014 17:31:46 +0100 Message-ID: <6053205.R7VxckQEVY@snow.oma.be> Organization: Royal Meteorological Institute of Belgium User-Agent: KMail/4.14.3 (Linux/3.7.10-1.1-desktop; KDE/4.14.3; x86_64; ; ) In-Reply-To: <6645627.5dTte5MPLk@snow.oma.be> References: <2163931.4eVnXNjHa3@snow.oma.be> <4171.1418838543@sss.pgh.pa.us> <6645627.5dTte5MPLk@snow.oma.be> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="nextPart4667444.kAfRL6pneW" Content-Transfer-Encoding: 7Bit X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multi-part message in MIME format. --nextPart4667444.kAfRL6pneW Content-Transfer-Encoding: 7Bit Content-Type: text/plain; charset="iso-8859-15" Hi, I tried also with an upsert function CREATE FUNCTION upsert_func(sql_insert text, sql_update text) RETURNS void LANGUAGE plpgsql AS $$ BEGIN EXECUTE sql_update; IF FOUND THEN RETURN; END IF; BEGIN EXECUTE sql_insert; EXCEPTION WHEN OTHERS THEN EXECUTE sql_update; END; RETURN; END; $$; with the same result on the memory used... The tables hold 355000 rows in total. Regards, A. On Thursday 18 December 2014 12:16:49 Alessandro Ipe wrote: > Hi, > > > A grep in a nightly dump of this database did not return any AFTER trigger. > The only keys are primary on each daily table, through > ADD CONSTRAINT "MSG_YYYY-MM-DD_pkey" PRIMARY KEY (slot, msg); > and on the global table > ADD CONSTRAINT msg_pkey PRIMARY KEY (slot, msg); > > > Regards, > > > A. > > On Wednesday 17 December 2014 12:49:03 Tom Lane wrote: > > Alessandro Ipe writes: > > > My dtrigger definition is > > > CREATE TRIGGER msg_trigger BEFORE INSERT ON msg FOR EACH ROW EXECUTE > > > PROCEDURE msg_function(); so it seems that it is a BEFORE trigger. > > > > Hm, no AFTER triggers anywhere? Are there foreign keys, perhaps? > > > > regards, tom lane --nextPart4667444.kAfRL6pneW Content-Transfer-Encoding: 7Bit Content-Type: text/html; charset="iso-8859-15"

Hi,

 

 

I tried also with an upsert function

CREATE FUNCTION upsert_func(sql_insert text, sql_update text) RETURNS void

LANGUAGE plpgsql

AS $$

BEGIN

EXECUTE sql_update;

IF FOUND THEN

RETURN;

END IF;

BEGIN

EXECUTE sql_insert;

EXCEPTION WHEN OTHERS THEN

EXECUTE sql_update;

END;

RETURN;

END;

$$;

with the same result on the memory used...

 

The tables hold 355000 rows in total.

 

 

Regards,

 

 

A.

 

 

On Thursday 18 December 2014 12:16:49 Alessandro Ipe wrote:

> Hi,

>

>

> A grep in a nightly dump of this database did not return any AFTER trigger.

> The only keys are primary on each daily table, through

> ADD CONSTRAINT "MSG_YYYY-MM-DD_pkey" PRIMARY KEY (slot, msg);

> and on the global table

> ADD CONSTRAINT msg_pkey PRIMARY KEY (slot, msg);

>

>

> Regards,

>

>

> A.

>

> On Wednesday 17 December 2014 12:49:03 Tom Lane wrote:

> > Alessandro Ipe <Alessandro.Ipe@meteo.be> writes:

> > > My dtrigger definition is

> > > CREATE TRIGGER msg_trigger BEFORE INSERT ON msg FOR EACH ROW EXECUTE

> > > PROCEDURE msg_function(); so it seems that it is a BEFORE trigger.

> >

> > Hm, no AFTER triggers anywhere? Are there foreign keys, perhaps?

> >

> > regards, tom lane

 

--nextPart4667444.kAfRL6pneW--