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@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
