Skip site navigation (1) Skip section navigation (2)

Re: limiting resources to users

From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
To: Greg Smith <greg(at)2ndquadrant(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: limiting resources to users
Date: 2009-12-01 03:55:59
Message-ID: 4B1493CF.6060603@postnewspapers.com.au (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

Responses

pgsql-general by date

Next:From: Merlin MoncureDate: 2009-12-01 04:40:45
Subject: Re: using column as 'mutex'
Previous:From: Greg SmithDate: 2009-12-01 03:33:06
Subject: Re: limiting resources to users

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group