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
Date: Wed, 17 Dec 2014 10:41:31 -0500
Message-ID: <7359.1418830891@sss.pgh.pa.us> (raw)
In-Reply-To: <5491A0A8.7010506@toco-domains.de>
References: <2163931.4eVnXNjHa3@snow.oma.be>
<5491A0A8.7010506@toco-domains.de>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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: tgl@sss.pgh.pa.us, mailinglists@toco-domains.de, Alessandro.Ipe@meteo.be
Subject: Re: Excessive memory used for INSERT
In-Reply-To: <7359.1418830891@sss.pgh.pa.us>
* 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