Re: Is there a way to limit CPU usage per user - Mailing list pgsql-general

On 2/9/06, Michael Fuhr <mike@fuhr.org> wrote:
> On Fri, Feb 10, 2006 at 11:30:04AM +0700, Luki Rustianto wrote:
> > So how can we terminate such a long running query ?
> >
> > The idea is to make a crontab to periodicaly do a job to search a
> > typical "SELECT * FROM bigtable" query who has run for some hours then
> > to terminate them...
>
> Are you familiar with the statement_timeout setting?
>
> test=> SET statement_timeout TO 1000; -- milliseconds
> SET
> test=> SELECT <some long-running query>;
> ERROR:  canceling statement due to statement timeout
>
> If that won't work then please explain in general terms what problem
> you're trying to solve, not how you're trying to solve it.

I am also interested in being able to terminate *certain* long running
queries. I didn't want to use statement_timeout because there are some
queries that must run for a long time - in our case some queries that
create summary tables. Other long running queries should be killed. I
was able to get more granular by creating a "kill_pid" function in an
untrusted language and selectively kill ad-hoc queries. I'd suggest
having your non-killable queries run as one user.

That way you can do something like

SELECT * FROM pg_stat_activity
WHERE usename !='some_special_user'
AND query_start < NOW()-INTERVAL '30 minutes';

And then if you were very brave - you could kill those queries off.

I may get flamed for this, but this is how I have killed errant
processes.   I suspect you should pause for a long time before try to
install plperlu.

CREATE FUNCTION kill_pid(INTEGER) RETURNS TEXT AS
$BODY$
  my ($pid) = @_;
  my $out=system("kill -TERM $pid");
  return $out;
$BODY$ language plperlu;

REVOKE ALL ON FUNCTION kill_pid(INTEGER) FROM public;