Re: changing sort_mem on the fly?

On Sat, Jan 29, 2005 at 09:23:06PM +1100, Neil Conway wrote:
> Tom Lane wrote:
> >The correct place for a sysadmin to limit memory usage would be in the
> >ulimit settings the postmaster starts under. Of course, Neil's argument
> >still holds in general: anyone who can write arbitrary queries is not
> >going to have any difficulty in soaking up unreasonable amounts of
> >resources. Trying to restrict that would probably make the system less
> >useful rather than more so.
>
> I'm not sure if I agree that there's no potential for implementing
> better resource limits/quotas in PG in the future, I was just pointing
> out that it would require a lot more work to prevent resource
> consumption by malicious users than merely limiting who is allowed to
> set sort_mem/work_mem. If you could implement per-user/per-connection
> limits on things like processor usage or disk space consumption, I think
> that would be useful to some users (e.g. people offering PG in a web
> hosting environment).

Since you brought up the future... :)

I'd really like to see an improvement in how sort_mem/work_mem is
handled. It's currently impossible to set it in a way to support
moderately large sorts (say, 5% of available memory) without either
embedding 'set sort_mem = blah' in your code or running the risk that at
some point your database server will start swapping. I wish there was
some way to limit total sort size for the entire system, or at least per
connection/query.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"