Thread: setting configuration values inside a stored proc

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.

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