Re: limiting resources to users

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?

> 4) Massage data from netstat, lsof, or similar tools to figure out
> which process you want.

IOW, how is this distinct from (1) ?

>> It makes me wonder if it'd be handy to have a command-line option for
>> psql that caused it to spit the backend pid out on stderr.
> Inspired by this idea, I just thought of yet another approach. Put this
> at the beginning of something you want to track:
>
> COPY (SELECT pg_backend_pid()) TO '/place/to/save/pid';

Good point. It's a pity the filename arg to copy can't be an expression,
since then it'd be easy to write to a unique file name, but it's obvious
why iy can't. A PL/PgSQL EXECUTE statement gets around that, though.

That way you can write a file with the backend pid as its file name into
a directory watched by a helper process. When a file appears in the
watched dir the helper renices (and ionices if appropriate) the process
with the same pid as the file name, then deletes the file. You could
even include the desired nice level, or other params, in the file
contents that are generated by the query passed to COPY.

I think that's worth setting up here, actually - wrap the COPY up in a
'security definer' PL/PgSQL function named, say, "nice_self(...)" that
restricts the permitted nice level and I/O class, and expose it to batch
jobs. I'll have to play with that.

--
Craig Ringer