Re: limiting resources to users

On 1/12/2009 12:30 AM, Greg Smith wrote:
> Craig Ringer wrote:
>> I've also added a general explanation of the issues with prioritizing
>> users/queries/connections/databases:
>>
>> http://wiki.postgresql.org/wiki/Priorities

> I just spent some time
> reviewing/improving your article, and I pulled the disclaimer off when I
> was done.

Thanks. I really appreciate your taking the time. The article is clearly
improved.

Looking at the diffs, it's clear I need to cut the fluff from my
writing, as your edits make the whole thing a lot clearer. Keeping it
succinct is important, and I tend to forget that.

> I added some brief comments
> about how you can look at pg_stat_activity to find the actual backend
> pid of something from outside of the client itself, to start documenting
> that process. It would be nice (ouch) to provide a better example of how
> to do that at some point. Sometimes for example I'll save the pid of the
> spawned psql process and use that to lookup the backend pid assigned;
> not hard to do if you've seen an example or know how this all fits
> together, but not really an obvious technique either.

Good point.

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?

eg:

$ lsof -F 'n' -P -i -n -a -p 29951
p29951
n127.0.0.1:39996->127.0.0.1:5432

... from which you can query pg_stat_activity to get the backend pid:

$ psql -c "select procpid from pg_stat_activity \
where client_addr = '127.0.0.1' AND client_port = '39996';"

procpid
---------
29957
(1 row)

I'm sure there must be a nicer way to get a list of the local ip and
port of all a process's connected sockets without having to rely on
lsof, though, surely?

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.

--
Craig Ringer