[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
 Keywords:                  |
----------------------------+---------------------------
 Hi,

 Below was using PostgreSQL 13 and PostGIS 3.2.1.

 I believe that the automatic conversion of the geometry field to geojson
 when using to_json or to_jsonb is causing a query of ours to fail.

 Below query has been trimmed down a lot to try and show the issue.
 In the below case 'a_large_table' needs to be big enough to cause the
 query planner to use parallel queries (or force it using other settings).

 {{{
 -- Does not work
 WITH object_list AS (
         SELECT guid,'a_large_table' AS table_name,
 to_jsonb((a_large_table.geom)) AS json_data
         FROM a_large_table
         WHERE reference like 'A00171%'
 )
 SELECT * INTO TEMPORARY TABLE object_list_temp
 FROM object_list;
 }}}

 This gives the output:
 ERROR: cannot start commands during a parallel operation
   Where: SQL statement "SELECT auth_name||':'||auth_srid FROM
 public.spatial_ref_sys WHERE srid='27700'"


 {{{
 -- If casting the geometry to text the query then works as expected
 WITH object_list AS (
         SELECT guid,'a_large_table' AS table_name,
 to_jsonb((a_large_table.geom::text)) AS json_data
         FROM a_large_table
         WHERE reference like 'A00171%'
 )
 SELECT * INTO TEMPORARY TABLE object_list_temp
 FROM object_list;
 }}}

 Above works correctly for comparison, only difference being the geometry
 casting to text inside the to_jsonb.
 It is also worth noting that the part inside the CTE does work correctly
 when run on its own in either case.

 I believe the selection into the temporary table causes it to be parallel
 restricted (? I am not a DBA, please correct me if incorrect) which is why
 after surrounding the other query something inside it is not marked as
 parallel safe and fails.

 Any help appreciated, but I believe this a bug.

 Thanks

-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5139>
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.