Re: Allow sorts to use more available memory

Robert Schnabel <schnabelr@missouri.edu> writes:
> On 9/12/2011 3:58 PM, Scott Marlowe wrote:
>> On Mon, Sep 12, 2011 at 11:33 AM, Robert Schnabel
>> <schnabelr@missouri.edu>  wrote:
>>> The recent "data warehouse" thread made me think about how I use work_mem
>>> for some of my big queries.  So I tried SET work_mem = '4GB' for a session
>>> and got
>>> ERROR: 4194304 is outside the valid range for parameter "work_mem" (64 ..
>>> 2097151)

>> Ubuntu 10.10, pgsql 8.4.8:
>> smarlowe=# set work_mem='1000GB';
>> SET

> Ok, so is this a limitation related to the Windows implementation?

Yeah.  If you look into guc.c you'll find this:

/* upper limit for GUC variables measured in kilobytes of memory */
/* note that various places assume the byte size fits in a "long" variable */
#if SIZEOF_SIZE_T > 4 && SIZEOF_LONG > 4
#define MAX_KILOBYTES    INT_MAX
#else
#define MAX_KILOBYTES    (INT_MAX / 1024)
#endif

Since Windows, more or less alone among known 64-bit operating systems,
chose not to make "long" the same width as pointers, these values get
restricted just as if you were on a 32-bit machine.  Few Postgres
developers use Windows enough to get excited about doing all the tedious
(and bug-prone) gruntwork that would be required to fix this.

            regards, tom lane