PostgreSQL Administration

Killing long-running queries

This section is about stopping, that is killing, queries that should not be running.

Because users run SQL queries, they may inadvertently execute erroneous queries. These queries usually run for excessive amounts of time and produce extremely large result sets.

Because SokweDB runs in the cloud and cloud billing is usage-based, in addition to slowing down the system, these queries can cost money.

Problem overview

It is relatively easy to write such a query, if you

SELECT ...
  FROM tablea, tableb, tablec ...

and do not supply any WHERE or JOIN conditions, the result will be the cross product of all rows of all tables. In other words, each row of each table will be paired up with every row of every other table, producing A times B times C number of output rows, where A, B, and C are the number of rows in tablea, tableb, and tablec.

Some of the generic database interfaces may have ways to monitor the database backend to discover and kill such bad queries. Alternately, use the manual process below.

Permissions required

No matter the user interface used, the permission requirements are the same: Any user can kill their own queries. Only an administrator can kill other user's queries.

Finding a query to kill

To kill such a query, first find it's process number, it's pid. This is found in the pid column of the following query:

SELECT * FROM pg_stat_activity;

Killing the query

The following statement kills the query, where MYPID is the pid of the query to be killed:

SELECT pg_terminate_backend(MYPID);

The query may not always immediately stop. It is best to check that no mistake was made and the process was actually killed.

Installing Isok, "The Warning System"

SokweDB uses Isok to generate its warnings. Because SokweDB uses the Microsoft Azure cloud database service to run PostgreSQL, Isok cannot be installed as a PostgreSQL extension. It must be installed as "pure SQL".

Follow the SQL-based installation instructions in the Isok documentation to build a SQL file for the current version of Isok. Then execute the following code, using the psql command line program, to install. (Using psql's \i feature, to execute the code from a file, is recommended.)

The exact code to execute, based on the code below, depends on the generated SQL's file name and path. Don't forget to adjust this before execution.

If installing a newer version of Isok, note that this code deletes all the Isok table content, including queries and query results. The content will need to be restored from a data-only dump of the Isok schema.

 -- Update isok to the latest version (ish)
 -- Destroys all data in the schema!
 -- Must be run by an admin user.
 -- A psql input file
 
 set role admin;
 
 drop schema isok cascade;
 
 create schema isok;
 grant usage on schema isok to reader, writer;
 
 -- This is your Isok cloud installation file, generated by:
 -- make TARGET_SCHEMA=isok sql/pg_isok_cloud--1.2.0.sql
 -- https://kop.codeberg.page/pg_isok_docs/pg_isok_html/html_paginated/Installation.html
 \i isok/pg_isok_cloud--1.2.0.sql
 
 grant select on all tables in schema isok to reader;
 grant select on all sequences in schema isok to reader;
 
 grant select, insert, update, delete on all tables in schema isok to writer;
 grant select, update, usage on all sequences in schema isok to writer;
 
 grant execute on all functions in schema isok to reader, writer;