Opened 2 years ago

Last modified 2 years ago

#5192 new defect

Error executing PostGIS function

Reported by: thomasmarti Owned by: pramsey
Priority: medium Milestone: PostGIS PostgreSQL
Component: postgis Version: 3.2.x
Keywords: Cc: thomasmarti

Description

When executing the Query below, an error occurs. Our contactperson at SUSE says "PostGIS is not supported with PostgreSQL on SUSE…. Which combination of packages would you suggest?

Query: SELECT DISTINCT ON (land_use_plans.geometry.public_law_restriction_id) land_use_plans.geometry.id AS land_use_plans_geometry_id, land_use_plans.geometry.law_status AS land_use_plans_geometry_law_status, land_use_plans.geometry.published_from AS land_use_plans_geometry_published_from, land_use_plans.geometry.published_until AS land_use_plans_geometry_published_until, land_use_plans.geometry.geo_metadata AS land_use_plans_geometry_geo_metadata, ST_AsEWKB(land_use_plans.geometry.geom) AS land_use_plans_geometry_geom, land_use_plans.geometry.public_law_restriction_id AS land_use_plans_geometry_public_law_restriction_id FROM land_use_plans.geometry WHERE ST_Intersects(ST_CollectionExtract(land_use_plans.geometry.geom, 1), ST_GeomFromText('MULTIPOLYGON (((2645679.0639999993 1248975.5410000011, 2645672.3139999993 1248966.5100000016, 2645666.5579999983 1248959.2879999988, 2645651.493999999 1248941.3709999993, 2645643.0020000003 1248931.3480000012, 2645635.9560000002 1248923.4910000004, 2645628.1110000014 1248915.1510000005, 2645618.465999998 1248905.5100000016, 2645616.9109999985 1248907.1160000004, 2645601.7930000015 1248922.5969999991, 2645589.984000001 1248910.9409999996, 2645577.561999999 1248923.675999999, 2645564.2939999998 1248936.8249999993, 2645583.0769999996 1248959.6609999985, 2645599.3960000016 1248980.9979999997, 2645606.221999999 1248974.8209999986, 2645626.5489999987 1249008.4019999988, 2645655.7690000013 1248994.0560000017, 2645665.719999999 1248985.120000001, 2645679.0639999993 1248975.5410000011)))', 2056)) OR ST_Intersects(ST_CollectionExtract(land_use_plans.geometry.geom, 2), ST_GeomFromText('MULTIPOLYGON (((2645679.0639999993 1248975.5410000011, 2645672.3139999993 1248966.5100000016, 2645666.5579999983 1248959.2879999988, 2645651.493999999 1248941.3709999993, 2645643.0020000003 1248931.3480000012, 2645635.9560000002 1248923.4910000004, 2645628.1110000014 1248915.1510000005, 2645618.465999998 1248905.5100000016, 2645616.9109999985 1248907.1160000004, 2645601.7930000015 1248922.5969999991, 2645589.984000001 1248910.9409999996, 2645577.561999999 1248923.675999999, 2645564.2939999998 1248936.8249999993, 2645583.0769999996 1248959.6609999985, 2645599.3960000016 1248980.9979999997, 2645606.221999999 1248974.8209999986, 2645626.5489999987 1249008.4019999988, 2645655.7690000013 1248994.0560000017, 2645665.719999999 1248985.120000001, 2645679.0639999993 1248975.5410000011)))', 2056)) OR ST_Intersects(ST_CollectionExtract(land_use_plans.geometry.geom, 3), ST_GeomFromText('MULTIPOLYGON (((2645679.0639999993 1248975.5410000011, 2645672.3139999993 1248966.5100000016, 2645666.5579999983 1248959.2879999988, 2645651.493999999 1248941.3709999993, 2645643.0020000003 1248931.3480000012, 2645635.9560000002 1248923.4910000004, 2645628.1110000014 1248915.1510000005, 2645618.465999998 1248905.5100000016, 2645616.9109999985 1248907.1160000004, 2645601.7930000015 1248922.5969999991, 2645589.984000001 1248910.9409999996, 2645577.561999999 1248923.675999999, 2645564.2939999998 1248936.8249999993, 2645583.0769999996 1248959.6609999985, 2645599.3960000016 1248980.9979999997, 2645606.221999999 1248974.8209999986, 2645626.5489999987 1249008.4019999988, 2645655.7690000013 1248994.0560000017, 2645665.719999999 1248985.120000001, 2645679.0639999993 1248975.5410000011)))', 2056))

Error: File <span class="code-quote">"/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py"</span>, line 1819, in _execute_context self.dialect.do_execute( File <span class="code-quote">"/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/<span class="code-keyword">default</span>.py"</span>, line 732, in do_execute cursor.execute(statement, parameters) psycopg2.errors.InternalError_: failed to load summary <span class="code-quote">"/usr/lib/postgresql14/lib64/bitcode/postgis-3.index.bc"</span>: Invalid summary version 9, 1, 2, 3 or 4 expected server closed the connection unexpectedly This probably means the server terminated abnormally before or <span class="code-keyword">while</span> processing the request.<br><br><br>

Version (full): "PostgreSQL 14.2 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux) 7.5.0, 64-bit POSTGIS=""3.2.1 5fae8e5"" [EXTENSION] PGSQL=""140"" GEOS=""3.10.1-CAPI-1.16.0"" PROJ=""7.0.0"" GDAL=""GDAL 3.4.1, released 2021/12/27 GDAL_DATA not found"" LIBXML=""2.9.7"" LIBJSON=""0.13"" LIBPROTOBUF=""1.3.2"" WAGYU=""0.5.0 (Internal)"" RASTER"

Change History (1)

comment:1 by robe, 2 years ago

1) Where did you get your PostGIS from? Did you compile yourself or get from a package repo?

I would suggest you upgrade to the latest micro of each of these. I think Proj 7.0.0 was pretty buggy so I would be running 7.2.1 or higher.

PostgreSQL 14 - 14.4 is the latest patch release for 14, so I would be running with that if you can, though I doubt your issues are cause by that.

GEOS 3.10.3 is the latest, so you should bump to that.

Everything else looks okay aside from the GDAL_DATA not found. That usually means something wrong with your PROJ configuration, like it can't find the proj.db or other env paths.

At any rate, if you are not using raster functionality, you can run:

DROP EXTENSION postgis_raster;

So one less thing for you to worry about.

2) a) Can you give us a gdb trace of the error

https://trac.osgeo.org/postgis/wiki/DevWikiGettingABackTrace

or b) a small set of data and self-contained example that reproduces the error

If you can't do either on #2, there is unfortunately nothing we can do to troubleshoot where the issue is.

Note: See TracTickets for help on using tickets.