[postgis-users] Spatial Query (st_union) causing database to crash

[Mike Treglia]

Hi All,

I've attempted the following query that seems to be causing my database to
crash. Wondering if anyone is able to suggest anything actionable from the
postgis log, pasted below.

The query is fairly straightforward - trying to union/dissolve polygons
based on a few fields. But I know these are large geometries so maybe
that's causing memory issues? (If that's likely the case, any suggestions
on alternative approaches to handle the data as-is?)

Query:
create table whymap_gadm_wdpa_esriunion_land_reducedvars_dissolve as
select name_0, rock_type, rtypelabel, protected_binary, st_union(geom_4326)
as geom_4326
from whymap_gadm_wdpa_esriunion_land_reducedvars
group by name_0, rock_type, rtypelabel, protected_binary

Of course, happy to hear if it looks like I'm doing something more
obviously wrong, and happy to share a bit more info if there's something
specific that would be helpful.

Thank you!
Mike


####
Log (Note - the exception seems to correspond to STATUS_STACK_OVERFLOW):
####

2021-06-07 05:51:45.652 EDT [6764] LOG:  server process (PID 28304) was
terminated by exception 0xC00000FD
2021-06-07 05:51:45.652 EDT [6764] DETAIL:  Failed process was running:
create table test.whymap_gadm_wdpa_esriunion_land_reducedvars_dissolve as

select name_0, rock_type, rtypelabel, protected_binary, st_union(geom_4326)
as geom_4326

from test.whymap_gadm_wdpa_esriunion_land_reducedvars

group by name_0, rock_type, rtypelabel, protected_binary
2021-06-07 05:51:45.652 EDT [6764] HINT:  See C include file "ntstatus.h"
for a description of the hexadecimal value.
2021-06-07 05:51:45.656 EDT [6764] LOG:  terminating any other active
server processes
2021-06-07 05:51:45.672 EDT [3568] WARNING:  terminating connection because
of crash of another server process
2021-06-07 05:51:45.672 EDT [3568] DETAIL:  The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2021-06-07 05:51:45.672 EDT [3568] HINT:  In a moment you should be able to
reconnect to the database and repeat your command.
2021-06-07 05:51:45.676 EDT [23284] WARNING:  terminating connection
because of crash of another server process
2021-06-07 05:51:45.676 EDT [23284] DETAIL:  The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2021-06-07 05:51:45.676 EDT [23284] HINT:  In a moment you should be able
to reconnect to the database and repeat your command.
2021-06-07 05:51:45.687 EDT [10924] WARNING:  terminating connection
because of crash of another server process
2021-06-07 05:51:45.687 EDT [10924] DETAIL:  The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2021-06-07 05:51:45.687 EDT [10924] HINT:  In a moment you should be able
to reconnect to the database and repeat your command.
2021-06-07 05:51:45.687 EDT [10924] CONTEXT:  while inserting index tuple
(11,65) in relation "pg_type_typname_nsp_index"
2021-06-07 05:51:45.688 EDT [27456] WARNING:  terminating connection
because of crash of another server process
2021-06-07 05:51:45.688 EDT [27456] DETAIL:  The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2021-06-07 05:51:45.688 EDT [27456] HINT:  In a moment you should be able
to reconnect to the database and repeat your command.
2021-06-07 05:51:45.698 EDT [6764] LOG:  all server processes terminated;
reinitializing
2021-06-07 05:51:45.763 EDT [14076] LOG:  database system was interrupted;
last known up at 2021-06-07 05:51:43 EDT
2021-06-07 05:51:45.783 EDT [28864] FATAL:  the database system is in
recovery mode
2021-06-07 05:51:45.894 EDT [29124] FATAL:  the database system is in
recovery mode
2021-06-07 05:51:46.010 EDT [29408] FATAL:  the database system is in
recovery mode
2021-06-07 05:51:46.057 EDT [13456] FATAL:  the database system is in
recovery mode
2021-06-07 05:51:46.102 EDT [12804] FATAL:  the database system is in
recovery mode
2021-06-07 05:51:46.142 EDT [14816] FATAL:  the database system is in
recovery mode
2021-06-07 05:51:46.187 EDT [30724] FATAL:  the database system is in
recovery mode
2021-06-07 05:51:46.240 EDT [13448] FATAL:  the database system is in
recovery mode
2021-06-07 05:51:46.282 EDT [16876] FATAL:  the database system is in
recovery mode
2021-06-07 05:51:46.321 EDT [16756] FATAL:  the database system is in
recovery mode
2021-06-07 05:51:46.366 EDT [3848] FATAL:  the database system is in
recovery mode
2021-06-07 05:51:46.412 EDT [34192] FATAL:  the database system is in
recovery mode
2021-06-07 05:51:46.470 EDT [22996] FATAL:  the database system is in
recovery mode
2021-06-07 05:51:46.513 EDT [33136] FATAL:  the database system is in
recovery mode
2021-06-07 05:51:46.552 EDT [29440] FATAL:  the database system is in
recovery mode
2021-06-07 05:51:46.592 EDT [25372] FATAL:  the database system is in
recovery mode
2021-06-07 05:51:46.634 EDT [17288] FATAL:  the database system is in
recovery mode
2021-06-07 05:51:52.989 EDT [14076] LOG:  database system was not properly
shut down; automatic recovery in progress
2021-06-07 05:51:53.005 EDT [14076] LOG:  redo starts at 8E/2D4B8300
2021-06-07 05:51:53.008 EDT [14076] LOG:  invalid record length at
8E/2D4B83F0: wanted 24, got 0
2021-06-07 05:51:53.010 EDT [14076] LOG:  redo done at 8E/2D4B8378
2021-06-07 05:51:53.113 EDT [6764] LOG:  database system is ready to accept
connections
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20210607/f5ceb6fc/attachment.html>