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

[Paul Ramsey]

Hi Mike,
Nothing do you should ever cause a crash, so you've (a) found some data that exercises the code in a new and exciting way, or (b) encountered a system install issue, that is leading to a crash, and that's either (a) a bug or (b) a system issue on your end (probably (a)). The first thing we need is your data so we can reproduce the condition. Also, the output from postgis_full_version().
Thanks!
P

> On Jun 7, 2021, at 6:43 AM, Mike Treglia <mtreglia at gmail.com> wrote:
> 
> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users