Re: Built-in support for a memory consumption ulimit?

On 06/16/2014 11:56 AM, Amit Kapila wrote:
> On Sat, Jun 14, 2014 at 8:07 PM, Tom Lane <tgl@sss.pgh.pa.us
> <mailto:tgl@sss.pgh.pa.us>> wrote:
>>
>> After giving somebody advice, for the Nth time, to install a
>> memory-consumption ulimit instead of leaving his database to the tender
>> mercies of the Linux OOM killer, it occurred to me to wonder why we don't
>> provide a built-in feature for that, comparable to the "ulimit -c max"
>> option that already exists in pg_ctl.
> 
> Considering that we have quite some stuff which is backend local (prepared
> statement cache, pl compiled body cache, etc..) due to which memory
> usage can increase and keep on increasing depending on operations
> performed by user

AFTER trigger queues, anybody?

Though they're bad enough that they really need to spill to disk, adding
a limit for them would be at best a temporary workaround.

> Providing such a feature via GUC is a good idea, but I think changing
> limit for usage of system resources should be allowed to privileged
> users.

I don't think we have the facility to do what I'd really like to: Let
users lower it, but not raise it above the system provided max. Just
like ulimit its self.

So SUSET seems OK to me. I don't think it should be PGC_BACKEND, not
least because I can see the utility of a superuser-owned SECURITY
DEFINER procedure applying system specific policy to who can set what limit.

-- Craig Ringer                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services