Thanks, Paul, I'll be happy to share the data (will adjust a bit just to make it more ambiguous re: what the data actually are). I think it'll be pretty large - in DBeaver it's showing as 18GB for the table - I think generally just due to complex geometries. Is there a preferred way to output it? Or just get the data out in an open format, zip it, and put it somewhere it can be downloaded?
Re: postgis_full_version - here ya go: POSTGIS="3.1.1 3.1.1" [EXTENSION] PGSQL="130" GEOS="3.9.0-CAPI-1.14.1" SFCGAL="1.3.8" PROJ="7.1.1" GDAL="GDAL 3.2.1, released 2020/12/29" LIBXML="2.9.9" LIBJSON="0.12" LIBPROTOBUF="1.2.1" WAGYU="0.5.0 (Internal)" TOPOLOGY RASTER Best, Mike On Mon, Jun 7, 2021 at 11:43 AM Paul Ramsey <pram...@cleverelephant.ca> wrote: > 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 <mtreg...@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@lists.osgeo.org > > https://lists.osgeo.org/mailman/listinfo/postgis-users > > _______________________________________________ > postgis-users mailing list > postgis-users@lists.osgeo.org > https://lists.osgeo.org/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
