[postgis-tickets] [PostGIS] #5139: PostGIS causes to_jsonb to no longer be parallel safe

[PostGIS]

#5139: PostGIS causes to_jsonb to no longer be parallel safe
-----------------------------+---------------------------
  Reporter:  samuelspurling  |      Owner:  pramsey
      Type:  defect          |     Status:  new
  Priority:  medium          |  Milestone:  PostGIS 3.2.2
 Component:  postgis         |    Version:  3.2.x
Resolution:                  |   Keywords:
-----------------------------+---------------------------
Comment (by robe):

 I was able to trigger the same error -

 {{{
 ERROR:  cannot start commands during a parallel operation
 CONTEXT:  SQL statement "SELECT auth_name||':'||auth_srid
 FROM public.spatial_ref_sys WHERE srid='27700'"
 }}}

 on both
 {{{ PostgreSQL 13.4, compiled by Visual C++ build 1914, 64-bit
 POSTGIS="3.2.0 3.2.0" [EXTENSION] PGSQL="130" GEOS="3.10.1-CAPI-1.16.0"
 PROJ="7.2.1" LIBXML="2.9.9" LIBJSON="0.12" LIBPROTOBUF="1.2.1"
 WAGYU="0.5.0 (Internal)"
 }}}

 and:


 {{{
 PostgreSQL 14.1, compiled by Visual C++ build 1914, 64-bit
 POSTGIS="3.2.1 3.2.1" [EXTENSION] PGSQL="140" GEOS="3.10.2-CAPI-1.16.0"
 PROJ="7.2.1" GDAL="GDAL 3.4.2, released 2022/03/08" LIBXML="2.9.9"
 LIBJSON="0.12" LIBPROTOBUF="1.2.1" WAGYU="0.5.0 (Internal)" RASTER
 }}}


 Using the below script

 {{{
 -- create a fake table
 --  28 secs 21 msec.
 DROP TABLE IF EXISTS a_large_table;
 CREATE TABLE a_large_table AS
 SELECT x AS id, ST_Point(40000 + random()*10000, -100000 + random()*10000,
 27700) AS geom
 FROM generate_series(1,10000000) AS x;

 }}}

 -- this triggers the error
 {{{
 set max_parallel_workers_per_gather=4;
 DROP TABLE IF EXISTS object_list_temp;
 WITH object_list AS (
         SELECT id,'a_large_table' AS table_name,
 to_jsonb((a_large_table.geom)) AS json_data
         FROM a_large_table
         WHERE id::text LIKE '10000'
 )
 SELECT * INTO TEMPORARY TABLE object_list_temp
 FROM object_list;
 }}}

 The explain looks like this:


 {{{
 Gather  (cost=1000.00..133115.60 rows=50000 width=68)
   Workers Planned: 4
   ->  Parallel Seq Scan on a_large_table  (cost=0.00..127115.60 rows=12500
 width=68)
         Filter: ((id)::text ~~ '10000'::text)
 }}}

 It also fails for me with 2 parallel workers with same error.
 Using id > 9900000 also triggers the error.

 @pramsey are you able to replicate with that above?


 I thought maybe we had a short-circuit for 4326 so tried with the same in
 the example. I confirmed I can trigger with 4326 also.

 However, if the dataset has no srid, then the error doesn't happen.

 e.g.

 {{{
 -- create a fake table
 --  28 secs 21 msec.
 DROP TABLE IF EXISTS a_large_table;
 CREATE TABLE a_large_table AS
 SELECT x AS id, ST_Point(40000 + random()*10000, -100000 + random()*10000)
 AS geom
 FROM generate_series(1,10000000) AS x;

 DROP TABLE IF EXISTS object_list_temp;
 -- no error
 set max_parallel_workers_per_gather=4;
 WITH object_list AS (
         SELECT id,'a_large_table' AS table_name,
 to_jsonb(a_large_table.geom) AS json_data
         FROM a_large_table
         WHERE id::text LIKE '10000'
 )
 SELECT * INTO TEMPORARY TABLE object_list_temp
 FROM object_list;
 }}}

 The explain of that looks the same as the failing ones


 So the issue is the srid call that is causing the problem.  Using
 a_large_table.geom::text simply avoids using ST_AsGeoJSON and the output
 is not a geojson object and no srid query needs to be done to pull the
 spatial ref sys metadata.
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5139#comment:2>
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.