0

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?

  • 1
    Are you able to check on the server side if anything is happening to your connection during this time? – dezso Jul 1 '16 at 12:00
  • @deszo, 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. – Andy Nichols Jul 1 '16 at 12:27
  • It sounds like the cancellation signal does not get to the server somehow. – dezso Jul 1 '16 at 13:12
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.