postgresql statement taking ages to cancel in pgAdmin III

Question

I was running a query in pgAdmin III and it was taking a long time. After 9 minutes I decided to cancel it using the stop button. The UI stated that it was cancelling and stayed in that state for slightly over an hour before it reported as cancelled. For all that time the CPU was around 98-99%.

The statement that was running was a call to a function. It was only part way through the function when it got stuck and all the time was taken on the following statement:

SELECT ST_UNION(shape)
FROM descendant_areas
INTO best_fit_areas;

where descendant_areas is a temporary table with no indexes containing nine records. Shape is of type geography.

Versions:

PostgreSQL=PostgreSQL 9.5.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit

PostGIS=POSTGIS="2.2.2 r14797" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 2.0.2, released 2016年01月26日" LIBXML="2.9.1" LIBJSON="0.12" TOPOLOGY RASTER

My question is:

Is it normal for cancelling a query to take that long or is it indicative that there are config settings set up badly?

• Andy Nichols: It looks OK according to SELECT * FROM pg_stat_activity;, although I don't know exactly what I'm looking for as I'm a dev and our sysadmin is on holiday. state is active and waiting is false.

Answer 1

PostgreSQL generally does not visibly respond to cancellation requests while it is inside an externally-defined function. It will simply set a flag, and then process the request between calls to ST_UNION, but not within them.