Re: limiting resources to users

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
>
This is a very common question and I'm glad to have somewhere we just
point people to when it comes up again.  I just spent some time
reviewing/improving your article, and I pulled the disclaimer off when I
was done.  It's a solid intro to this topic, and I linked it into the
wiki infrastructure a bit better too (i.e. adding it to
http://wiki.postgresql.org/wiki/Frequently_Asked_Questions and cleaning
up where it shows up on the main Performance Optimization page).

The main thing your discussion missed, and that some more detail could
be provided on beyond what I just added, is how to answer the regular
requests we see for "how can I lower the priority of this big batch job
that's monopolizing the system?".  People don't necessarily want to do
that from the backend itself, which is what your comments focused on.
It can be enough to find the process via the script that launched the
batch job, and then having it issue the nice call.  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.

--
Greg Smith    2ndQuadrant   Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com  www.2ndQuadrant.com