Cancelling query with ST_Union crashes PostgreSQL server

[dbaston]

When I cancel a query with ST_Union after realizing that it will take a long time, my Windows PostgreSQL server crashes.

"POSTGIS="2.0.1 r9979" GEOS="3.3.5-CAPI-1.7.5" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.9.1, released 2012/05/15" LIBXML="2.7.8" LIBJSON="UNKNOWN" TOPOLOGY RASTER"

"PostgreSQL 9.2.1, compiled by Visual C++ build 1600, 64-bit"

An example query is something like SELECT city, ST_Union(geom) FROM cities GROUP BY city

Geometries in the table are multipolygons.

Please let me know if there is more useful information that I can provide.

[robe]

You should upgrade to the latests minor 2.0.1 is pretty old. Latest stable is 2.0.3

[robe]

If you still have issue after upgrading to 2.0.3, you may want to check your shared memory setting. PostgreSQL particular on windows gets pretty unstable if that is set too high. Even for 64-bit I haven't tried going beyond 2GB (so not sure the upper on that) or but would depend on amount of memory you have on your box available.

[dbaston]

Thanks - I am embarrassed for not realizing the version was out-of-date before submitting this.

shared_buffers is set to 500 M after the upgrade.

I upgraded my laptop to 2.0.3 and although I was not able to cancel the query, attempting this did not crash the server. I will put in a request to have the production server upgraded to 2.0.3.

Thanks

[robe]

The cancel the query feature is implemented in upcoming 2.1 that we'll be releasing soon. Admittedly I haven't really tested it but its part of geos 3.4.0dev.

[robe]

dbaston,

Forgot to mention if you want to experiment with the upcoming cancel the query feature we have windows builds that get built whenever a change in PostGIS code base.

Upgrading your windows 7 will just require copying over the binaries and running

ALTER EXTENSION postgis UPDATE TO "2.1.0beta2";

I'll be tagging a beta3 in next day or 2 so you might want to wait. Details on windows page — ​http://postgis.net/windows_downloads

We also have pgRouting 2.0 (which gets built whenever a change in pgRouting development branch). If you have a need for that pgRouting folks would appreciate a lot more beta testers.