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
