From: Andres Freund <andres@anarazel.de>
To: pgsql-performance@postgresql.org
Subject: Re: setting configuration values inside a stored proc
Date: Fri, 13 May 2011 10:28:15 +0200
User-Agent: Kmail
Cc: Samuel Gendler <sgendler@ideasculptor.com>
References: <BANLkTimJjQREogk=pD+j6WT+yWEZtjfDWg@mail.gmail.com>
In-Reply-To: <BANLkTimJjQREogk=pD+j6WT+yWEZtjfDWg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="iso-8859-15"
Content-Transfer-Encoding: 7bit
Message-Id: <201105131028.15694.andres@anarazel.de>

Hi,

On Friday, May 13, 2011 01:10:19 AM Samuel Gendler wrote:
> I've got a stored proc that constructs some aggregation queries as strings
> and then executes them.  I'd like to be able to increase work_mem before
> running those queries.  If I set a new value for work_mem within the stored
> proc prior to executing my query string, will that actually have an impact
> on the query or is work_mem basically a constant once the outer statement
> that calls the stored proc has begun?  I'd just test, but it will take
> hours for me to grab a copy of production data and import into a new db
> host for testing. I've already started that process, but I'm betting I'll
> have an answer by the time it completes.  It's just the difference between
> modifying the application which calls the procs (and doing a full software
> release in order to do so or else waiting a month to go in the next
> release) vs modifying the procs themselves, which requires only db a
> update.
I would suggest doing ALTER FUNCTION blub(blarg) SET work_mem = '512MB';

Andres