Specific query hangs and can't be terminated when using ST_MVT/ST_AsMVTGeom

[seabre]

I am running into an issue where a query will hang forever. It cannot be terminated except using kill -9 or potentially attaching to the process with gdb.

PostGIS version:

POSTGIS="2.5.2 r17328" [EXTENSION] PGSQL="110" GEOS="3.6.2-CAPI-1.10.2 4d2925d6" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.2.3, released 2017/11/20" LIBXML="2.9.4" LIBJSON="0.12.1" LIBPROTOBUF="1.2.1" RASTER

Postgres Version:

PostgreSQL 11.4 (Ubuntu 11.4-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit

Backtrace:

#0  0x00007ff2d337b4eb in geos::operation::intersection::distance(geos::operation::intersection::Rectangle const&, double, double, double, double) ()
   from /usr/lib/x86_64-linux-gnu/libgeos-3.6.2.so
#1  0x00007ff2d337b964 in geos::operation::intersection::distance(geos::operation::intersection::Rectangle const&, std::vector<geos::geom::Coordinate, std::allocator<geos::geom::Coordinate> > const&) () from /usr/lib/x86_64-linux-gnu/libgeos-3.6.2.so
#2  0x00007ff2d337cc15 in geos::operation::intersection::RectangleIntersectionBuilder::reconnectPolygons(geos::operation::intersection::Rectangle const&) ()
   from /usr/lib/x86_64-linux-gnu/libgeos-3.6.2.so
#3  0x00007ff2d337a4c0 in geos::operation::intersection::RectangleIntersection::clip_polygon_to_polygons(geos::geom::Polygon const*, geos::operation::intersection::RectangleIntersectionBuilder&, geos::operation::intersection::Rectangle const&) () from /usr/lib/x86_64-linux-gnu/libgeos-3.6.2.so
#4  0x00007ff2d337a933 in geos::operation::intersection::RectangleIntersection::clip_geom(geos::geom::Geometry const*, geos::operation::intersection::RectangleIntersectionBuilder&, geos::operation::intersection::Rectangle const&, bool) () from /usr/lib/x86_64-linux-gnu/libgeos-3.6.2.so
#5  0x00007ff2d337acc0 in geos::operation::intersection::RectangleIntersection::clip() () from /usr/lib/x86_64-linux-gnu/libgeos-3.6.2.so
#6  0x00007ff2d337ad43 in geos::operation::intersection::RectangleIntersection::clip(geos::geom::Geometry const&, geos::operation::intersection::Rectangle const&) ()
   from /usr/lib/x86_64-linux-gnu/libgeos-3.6.2.so
#7  0x00007ff2d46634c0 in GEOSClipByRect_r () from /usr/lib/x86_64-linux-gnu/libgeos_c.so.1
#8  0x00007ff2d48cdf95 in lwgeom_clip_by_rect () from /usr/lib/liblwgeom-2.5.so.0
#9  0x00007ff2d4b4eb50 in mvt_geom () from /usr/lib/postgresql/11/lib/postgis-2.5.so
#10 0x00007ff2d4b54f7c in ST_AsMVTGeom () from /usr/lib/postgresql/11/lib/postgis-2.5.so
#11 0x000055bafe8845fb in ?? ()
#12 0x000055bafe8a3336 in ?? ()
#13 0x000055bafe8b02fe in ?? ()
#14 0x000055bafe8905a7 in ExecScan ()
#15 0x000055bafe8971bc in ?? ()
#16 0x000055bafe898dfb in ?? ()
#17 0x000055bafe887bad in standard_ExecutorRun ()
#18 0x000055bafe9d7d06 in ?? ()
#19 0x000055bafe9d9358 in PortalRun ()
#20 0x000055bafe9d699d in PostgresMain ()
#21 0x000055bafe96155d in ?? ()
#22 0x000055bafe96259d in PostmasterMain ()
#23 0x000055bafe6ee5f2 in main ()

Query Plan:

 Aggregate  (cost=61.39..61.40 rows=1 width=32)
   ->  Subquery Scan on q  (cost=61.37..61.38 rows=1 width=115)
         ->  Sort  (cost=61.37..61.37 rows=1 width=163)
               Sort Key: (sum(st_area(location_geometries_1.shpdata))) DESC
               ->  Hash Right Join  (cost=57.99..61.36 rows=1 width=163)
                     Hash Cond: (location_geometries_1.zone_id = zones.id)
                     ->  HashAggregate  (cost=52.87..54.28 rows=141 width=12)
                           Group Key: location_geometries_1.zone_id
                           ->  Seq Scan on location_geometries location_geometries_1  (cost=0.00..47.02 rows=195 width=1732)
                                 Filter: (deleted_at IS NULL)
                     ->  Hash  (cost=5.11..5.11 rows=1 width=1826)
                           ->  Nested Loop  (cost=1.52..5.11 rows=1 width=1826)
                                 ->  Index Scan using index_location_geometries_on_shpdata on location_geometries  (cost=0.14..2.61 rows=1 width=1736)
                                       Index Cond: (shpdata && '0103000020110F000001000000050000002444D1F6860261C1C6E2A01098B253412444D1F6860261C147416FA8C3B25341E214EA2A710261C147416FA8C3B25341E214EA2A710261C1C6E2A01098B253412444D1F6860261C1C6E2A01098B25341'::geometry)
                                       Filter: ((deleted_at IS NULL) AND _st_intersects(shpdata, '0103000020110F000001000000050000002444D1F6860261C1C6E2A01098B253412444D1F6860261C147416FA8C3B25341E214EA2A710261C147416FA8C3B25341E214EA2A710261C1C6E2A01098B253412444D1F6860261C1C6E2A01098B25341'::geometry))
                                 ->  Bitmap Heap Scan on zones  (cost=1.38..2.49 rows=1 width=94)
                                       Recheck Cond: (id = location_geometries.zone_id)
                                       Filter: (deleted_at IS NULL)
                                       ->  Bitmap Index Scan on zones_pkey  (cost=0.00..1.38 rows=1 width=0)
                                             Index Cond: (id = location_geometries.zone_id)

I originally posted this issue in the Postgres slack for more context if that helps: ​https://postgresteam.slack.com/archives/C0FS3UTAP/p1675802817860269

[robe]

GEOS 3.6 and PostGIS 2.5 have been End-of-lifed and not only are those end of lifed, but you are running very old versions of those too. While PostgreSQL 11 is not EOL'd, you are running a very old micro which probably has a ton of bugs in it that have already been fixed. Latest match release for PostgreSQL 11 is 11.18.

Any chance you can upgrade to newer versions and test. At the very least newer micros of GEOS 3.6 (GEOS 3.6.5 or higher) and PostGIS 2.5 (2.5.7 or higher)

​https://postgis.net/2022/11/12/postgis-2.5.9-eol/

I think we've had several mvt issues we've fixed since your 2.5.2/3.6.2 so next to impossible to tell if this is still an issue without some data that exercises it.

If you can provide some data, we can at least test on newer versions and tell you if upgrading would help.

[seabre]

The plan is for us to upgrade fairly soon, but running into the issues now and didn't see anything similar reported in the changelogs. I'm able to reproduce consistently on the affected machine but if I load a dump into a machine with the following versions:

POSTGIS="2.5.5" [EXTENSION] PGSQL="110" GEOS="3.7.3-CAPI-1.11.3 b50468f" PROJ="Rel. 5.2.0, September 15th, 2018" GDAL="GDAL 2.4.4, released 2020/01/08" LIBXML="2.9.11" LIBJSON="0.13.1" LIBPROTOBUF="1.3.3" RASTER

PostgreSQL 11.15 on x86_64-apple-darwin16.7.0, compiled by Apple LLVM version 8.1.0 (clang-802.0.42), 64-bit

The query runs fine, so it could be that any of the above mentioned solutions solve the problem. I'll dig more and see if I can provide more than what is in the original ticket.

[robe]

I'm going to close this out since it sounds like just an issue with outdated software.