[postgis-tickets] [PostGIS] #4857: Large ST_Union makes PostgreSQL temporarily unrestartable

[PostGIS]

#4857: Large ST_Union makes PostgreSQL temporarily unrestartable
----------------------+---------------------------
 Reporter:  grassick  |      Owner:  pramsey
     Type:  defect    |     Status:  new
 Priority:  high      |  Milestone:  PostGIS 3.1.2
Component:  postgis   |    Version:  3.1.x
 Keywords:            |
----------------------+---------------------------
 A query containing an ST_Union with a large number of features becomes un-
 killable until it completes. You can't even restart PostgreSQL or reboot
 the server until it's done.

 e.g. "select ST_Union(ST_Buffer(location, 10000)) from water_points" where
 water_points has 400,000 records.

 Server log shows:
 {{{
 2021-02-18 16:16:39.939 UTC [57720] LOG:  received fast shutdown request
 2021-02-18 16:16:39.945 UTC [57720] LOG:  aborting any active transactions
 2021-02-18 16:16:39.947 UTC [264426] postgres at postgres FATAL:  terminating
 connection due to administrator command
 2021-02-18 16:16:39.947 UTC [264427] postgres at mwater_opt FATAL:
 terminating connection due to administrator command
 2021-02-18 16:16:39.950 UTC [57720] LOG:  background worker "logical
 replication launcher" (PID 244337) exited with exit code 1
 2021-02-18 16:18:14.865 UTC [264759] postgres at mwater_opt FATAL:
 terminating connection due to administrator command
 2021-02-18 16:18:14.865 UTC [264759] postgres at mwater_opt STATEMENT:
 select ST_Union(ST_Buffer(location, 10000)) from water_points
 2021-02-18 16:18:14.962 UTC [57764] LOG:  shutting down
 2021-02-18 16:18:16.751 UTC [57720] LOG:  database system is shut down
 2021-02-18 16:18:17.156 UTC [264881] LOG:  starting PostgreSQL 13.2
 (Ubuntu 13.2-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc
 (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
 }}}

 Note the 2 minute delay of the database being completely unavailable!

-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/4857>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.