PostGIS reports: ERROR: function cannot execute on a QE slice because it accesses relation "public.spatial_ref_sys"

Products

• VMware Tanzu Greenplum
• VMware Tanzu Data Suite
• VMware Tanzu Data Suite

Issue/Introduction

When running function "st_transform(geometry, integer)" function against a row in a table it reports an error:

SELECT st_transform(geometry_col, 5490) AS new_location FROM my_geometries;
ERROR:  function cannot execute on a QE slice because it accesses relation "public.spatial_ref_sys"  (seg1 slice1 10.159.240.157:6017 pid=123415)
CONTEXT:  SQL statement "SELECT proj4text FROM public.spatial_ref_sys WHERE srid = 5490 LIMIT 1"

 

Cause

The main query requires dispatching because it contains a distributed user table. The function st_transform(), on the other hand, despite being declared as immutable, takes an argument that’s only available at runtime. This prevents the evaluation of the function during planning. Instead, it occurs on the segments during runtime. Since the function references another distributed table spatial_ref_sys, it also needs to be dispatched. However, QE dispatching is not permitted by the Greenplum architecture, resulting in the error.

Resolution

Change the table public.spatial_ref_sys to a replicated table:

ALTER TABLE public.spatial_ref_sys SET WITH (REORGANIZE=true) DISTRIBUTED REPLICATED;