From: Alessandro Ipe <Alessandro.Ipe@meteo.be>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Torsten Zuehlsdorff <mailinglists@toco-domains.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: Excessive memory used for INSERT
Date: Wed, 17 Dec 2014 18:04:42 +0100
Message-ID: <3672040.FVo2HxYOvt@snow.oma.be> (raw)
In-Reply-To: <7359.1418830891@sss.pgh.pa.us>
References: <2163931.4eVnXNjHa3@snow.oma.be>
<5491A0A8.7010506@toco-domains.de>
<7359.1418830891@sss.pgh.pa.us>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Hi,
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.
To be totally honest, I have "really" limited knownledge in SQL and postgresql and all these were gathered from recipes found on the web...
Regards,
Alessandro.
On Wednesday 17 December 2014 10:41:31 Tom Lane wrote:
> Torsten Zuehlsdorff <mailinglists@toco-domains.de> writes:
> > How many rows is "(SELECT * FROM upsert)" returning? Without knowing
> > more i would guess, that the result-set is very big and that could be
> > the reason for the memory usage.
>
> Result sets are not ordinarily accumulated on the server side.
>
> Alessandro didn't show the trigger definition, but my guess is that it's
> an AFTER trigger, which means that a trigger event record is accumulated
> in server memory for each inserted/updated row. If you're trying to
> update a huge number of rows in one command (or one transaction, if it's
> a DEFERRED trigger) you'll eventually run out of memory for the event
> queue.
>
> An easy workaround is to make it a BEFORE trigger instead. This isn't
> really nice from a theoretical standpoint; but as long as you make sure
> there are no other BEFORE triggers that might fire after it, it'll work
> well enough.
>
> Alternatively, you might want to reconsider the concept of updating
> hundreds of millions of rows in a single operation ...
>
> regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Alessandro.Ipe@meteo.be, tgl@sss.pgh.pa.us, mailinglists@toco-domains.de
Subject: Re: Excessive memory used for INSERT
In-Reply-To: <3672040.FVo2HxYOvt@snow.oma.be>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox