What type of damage can I do by setting SHMMAX to high?
Shared memory segments don't go away unless they are explicitly
destroyed, so a malicious local user can create orphan shared
memory segments until it fills your RAM and swap. For servers with
no untrusted users though, there isn't much harm you can do by
raising the limit (I have it at 1.5Gb on a Oracle server with 2G of
RAM, and it works fine).
How does one calculate what the highest SHMMAX value for the
system?
The highest value you can set it to is 4Gb (probably more if you
have a kernel with PAE support and more than 4Gb of RAM).
The shared memory segments are created once (at startup) with a
fixed size, and Postgres does the management of that memory zone, so
the size will not change.
You see the massive memory usage because the Postgres backends
need to read the full data generated by the query into memory. Some
of that data might also make it into the shared memory segment
(which is used as a cache), so the error you're getting is not
because the cache runs out (it can't, because old entries are
discarded on a LRU-basis), but because the backend doesn't have
enough memory to read the data it needs.
