From: Tom Lane <tgl@sss.pgh.pa.us>
To: Torsten Zuehlsdorff <mailinglists@toco-domains.de>
cc: pgsql-performance@postgresql.org, Alessandro Ipe <Alessandro.Ipe@meteo.be>
Subject: Re: Excessive memory used for INSERT
In-reply-to: <5491A0A8.7010506@toco-domains.de>
References: <2163931.4eVnXNjHa3@snow.oma.be>
 <5491A0A8.7010506@toco-domains.de>
Comments: In-reply-to Torsten Zuehlsdorff <mailinglists@toco-domains.de>
 message dated "Wed, 17 Dec 2014 16:26:32 +0100"
Date: Wed, 17 Dec 2014 10:41:31 -0500
Message-ID: <7359.1418830891@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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