CODEWITHSUNDEEP
postgresqldatabase-administration
Raju
Raju

Reputation: 69

kill the long running queries automatically

I want to kill the queries which are running more then 2 hours in automatic way.

I tried creating trigger like below

create or replace function stop_query()
RETURNS trigger
language plpgsql
as $$
begin
with pid_tbl as
   (
 SELECT                     
  pid
FROM pg_stat_activity                       
WHERE (now() - pg_stat_activity.query_start) > interval '120 minutes';

    )
    select * from pid_tbl;
    
SELECT pg_cancel_backend(var_pid);
end;$$

CREATE TRIGGER stop_query 
FOR EACH ROW EXECUTE FUNCTION stop_query();

please advice me how can i achieve this. is there any way I can achieve it without writing functions trigger

Upvotes: 1

Views: 7096

Answers (1)

Zegarek
Zegarek

Reputation: 26467

You don't need this trigger at all. As I mentioned in the comment, it should be enough for you to run one of these queries:

SET LOCAL   statement_timeout='2 h';--applies only until the end of the current transaction within the current session
SET SESSION statement_timeout='2 h';--only in the current session/connection
ALTER ROLE     your_user_name SET statement_timeout='2 h';--all new sessions of this user
ALTER DATABASE your_db_name   SET statement_timeout='2 h';--all new sessions on this db
ALTER SYSTEM                  SET statement_timeout='2 h';--all new sessions on all dbs on this system

They all set the statement_timeout setting that's by default 0 (meaning "no limit") to '2 h' ("2 hours"). It's best to apply this only to the specific context where it's required, i.e. for a specific user that tends to run queries you don't want hanging for too long.

Documentation:

statement_timeout (integer)

Abort any statement that takes more than the specified amount of time. If log_min_error_statement is set to ERROR or lower, the statement that timed out will also be logged. If this value is specified without units, it is taken as milliseconds. A value of zero (the default) disables the timeout.

The timeout is measured from the time a command arrives at the server until it is completed by the server. If multiple SQL statements appear in a single simple-Query message, the timeout is applied to each statement separately. (PostgreSQL versions before 13 usually treated the timeout as applying to the whole query string.) In extended query protocol, the timeout starts running when any query-related message (Parse, Bind, Execute, Describe) arrives, and it is canceled by completion of an Execute or Sync message.

Setting statement_timeout in postgresql.conf is not recommended because it would affect all sessions.

If you try to use unsupported units, you'll get a hint with your error:

ERROR: invalid value for parameter "statement_timeout": "2 hours"

HINT: Valid units for this parameter are "us", "ms", "s", "min", "h", and "d".

Which are microseconds, milliseconds, seconds, minutes, hours and days respectively.

To see the users/roles that have a query running for more than 2 hours right now, check statuses in pg_stat_activity:

select usename,
       state,
       now()-xact_start as duration,
       query 
from pg_stat_activity 
where now()-xact_start>'2 hours'::interval;

To see the current value of the setting for each user/role that has it configured, check pg_user:

select usename,
       config 
from pg_user,
     unnest(useconfig) as a(config) 
where config ilike '%statement_timeout%';

Upvotes: 7

Related Questions