Re: [PERFORM] setting configuration values inside a stored proc

[Andres Freund]

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

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] setting configuration values inside a stored proc

[Samuel Gendler]

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.

--sam