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

Mon Jun 7 14:02:42 PDT 2021

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 <pramsey at 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 <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
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20210607/448693f8/attachment.html>