Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Y1evW-0004Ah-6G for pgsql-performance@arkaria.postgresql.org; Thu, 18 Dec 2014 17:31:50 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Y1evV-0001XE-Kv for pgsql-performance@arkaria.postgresql.org; Thu, 18 Dec 2014 17:31:49 +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 1Y1evU-0001UO-6i for pgsql-performance@postgresql.org; Thu, 18 Dec 2014 17:31:48 +0000 Received: from smtp2.oma.be ([193.190.230.20]) by magus.postgresql.org with esmtps (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1Y1evP-0006cb-MM for pgsql-performance@postgresql.org; Thu, 18 Dec 2014 17:31:46 +0000 Received: from snow.oma.be (snow.oma.be [193.190.249.132]) by smtp2.oma.be (8.14.3/8.14.3/SuSE Linux 0.8) with ESMTP id sBIHVgEF028079 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO); Thu, 18 Dec 2014 17:31:42 GMT From: Alessandro Ipe To: Tom Lane Cc: pgsql-performance@postgresql.org, Torsten Zuehlsdorff Subject: Re: Excessive memory used for INSERT Date: Thu, 18 Dec 2014 18:31:42 +0100 Message-ID: <2870224.f0CdzmR1Qa@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: <17790.1418922345@sss.pgh.pa.us> References: <2163931.4eVnXNjHa3@snow.oma.be> <6053205.R7VxckQEVY@snow.oma.be> <17790.1418922345@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="nextPart45786985.igLVGE5pPx" 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. --nextPart45786985.igLVGE5pPx Content-Transfer-Encoding: 7Bit Content-Type: text/plain; charset="iso-8859-15" Hi, I can send a full dump of my database (< 2MB) if it is OK for you. Thanks, A. On Thursday 18 December 2014 12:05:45 Tom Lane wrote: > Alessandro Ipe writes: > > 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... > > If you want to provide a self-contained test case, possibly we could look > into it, but these fragmentary bits of what you're doing don't really > constitute an investigatable problem statement. > > I will note that EXCEPTION blocks aren't terribly cheap, so if you're > reaching the "EXECUTE sql_insert" a lot of times that might have something > to do with it. > > regards, tom lane --nextPart45786985.igLVGE5pPx Content-Transfer-Encoding: 7Bit Content-Type: text/html; charset="iso-8859-15"

Hi,

I can send a full dump of my database (< 2MB) if it is OK for you.

Thanks,

On Thursday 18 December 2014 12:05:45 Tom Lane wrote:

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

> > 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...

> If you want to provide a self-contained test case, possibly we could look

> into it, but these fragmentary bits of what you're doing don't really

> constitute an investigatable problem statement.

> I will note that EXCEPTION blocks aren't terribly cheap, so if you're

> reaching the "EXECUTE sql_insert" a lot of times that might have something

> to do with it.

> regards, tom lane

--nextPart45786985.igLVGE5pPx--