Re: limiting resources to users

I think I'm just going to have to run the priority DB on a different server (linux) to make sure it doesn't get abused. I can see no other way.

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Alban Hertroys
Sent: Tuesday, December 01, 2009 4:18 AM
To: Craig Ringer
Cc: Greg Smith; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] limiting resources to users

On 1 Dec 2009, at 4:55, Craig Ringer wrote:

> On 1/12/2009 11:33 AM, Greg Smith wrote:
>> Craig Ringer wrote:
>>> I assume you look up the associated backend by looking up the source
>>> IP and port of the client with `netstat', `lsof', etc, and matching
>>> that to pg_stat_activity?
>> There's a bunch of ways I've seen this done:
>>
>> 1) If you spawn the psql process with bash using "&", you can then find
>> its pid with "$!", then chain through the process tree with ps and
>> pg_stat_activity as needed to figure out the backend pid.
>
> I feel like I'm missing something obvious here. How can walking the process tree starting with the psql pid help you find the backend pid?
> The backend and client have no relationship in the process tree. At some point you have to match the (ip,port) tuple for the client's connection against pg_stat_activity - what you've listed separately as (4). Even that won't help if a unix socket is in use, since client_addr is null and client_port is -1.
>
> So: knowing the client (say, psql) pid, how can you find the backend pid without relying on something like lsof or netstat to identify the source (ip,port) combo used by the particular client instance whose pid you know?

I think Greg intended this to be used from the process that needs re-nicing. If you have a batch job that you always want to run at a different nice-level you can get its pid that way and use that to re-nice the process.

You can also match it up to procpid in pg_stat_activity to get other info about the backend, but I don't think there's anything in there that you'd need to know at that point (you got the pid to re-nice already, after all).

Alban Hertroys

--
Screwing up is the best way to attach something to the ceiling.

!DSPAM:737,4b14df4211731232810455!