[postgis-tickets] [PostGIS] #5335: Specific query hangs and can't be terminated when using ST_MVT/ST_AsMVTGeom

[PostGIS]

#5335: Specific query hangs and can't be terminated when using ST_MVT/ST_AsMVTGeom
---------------------+--------------------------------
 Reporter:  seabre   |      Owner:  pramsey
     Type:  defect   |     Status:  new
 Priority:  medium   |  Milestone:  PostGIS PostgreSQL
Component:  postgis  |    Version:  2.5.x
 Keywords:           |
---------------------+--------------------------------
 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
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5335>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.