Opened 3 months ago

Last modified 4 weeks ago

#4129 new defect

Cannot update SecondarySnapshot during a parallel operation

Reported by: davidp Owned by: Bborie Park
Priority: high Milestone: PostGIS 2.4.6
Component: raster Version: 2.4.x
Keywords: SecondarySnapshot parallel Cc:

Description

Hi, since we upgraded our Postgres/Postgis? stack to version 10/2.4 this error starts to appear in our log:

ERROR: cannot update SecondarySnapshot? during a parallel operation

Most often in this context:

SQL statement: "SELECT proj4text FROM public.spatial_ref_sys WHERE srid = 5514 LIMIT 1"

or

SQL statement "SELECT TRUE" PL/pgSQL function _st_intersects(geometry,raster,integer)

There are many diferent sources of this error (PHP, Python, Mapnik), so i thing, there is marginal chance to be caused by our code (SQL statements). When i wrap problematic statements into PL/pgSQL function (which are parallel restricted by default), then this error disappear. So my suspicion falls on PostGIS functions and how they behave in PostgreSQL parallel mode.

For now i thing there is issue in these PostGIS functions:

  • ST_Transform(geometry, int) which executes "select proj4text from..."
  • ST_Intersects(geometry, raster)

Our system info is:

  • PostgreSQL 10.3 (Debian 10.3-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
  • POSTGIS="2.4.4 r16526" PGSQL="100" GEOS="3.5.1-CAPI-1.9.1 r4246" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.1.2, released 2016/10/24" LIBXML="2.9.4" LIBJSON="0.12.1" LIBPROTOBUF="1.2.1" RASTER

And PostreSQL cluster resides behind PGBouncer in session pooling mode.

Best Regards, David

Change History (4)

comment:1 Changed 3 months ago by Algunenano

Component: postgisraster
Owner: changed from pramsey to Bborie Park

This looks like there is some Postgis function marked as PARALLEL SAFE when it isn't.

ST_Transform might execute a query like the one you've posted above, but it's read only and doesn't do anything strange so it should be ok.

On the other hand ST_Intersects(geometry, raster) is defined as:

CREATE OR REPLACE FUNCTION _st_intersects(geom geometry, rast raster, nband integer DEFAULT NULL)
	RETURNS boolean AS $$
	DECLARE
		hasnodata boolean := TRUE;
		_geom geometry;
	BEGIN
		IF ST_SRID(rast) != ST_SRID(geom) THEN
			RAISE EXCEPTION 'Raster and geometry do not have the same SRID';
		END IF;

		_geom := ST_ConvexHull(rast);
...

And if we look at ST_ConverHull(raster) it is missing the parallel declaration:

-- Availability: 2.0.0
-- Changed: 2.1.4 raised cost
-- used to cast raster -> geometry
CREATE OR REPLACE FUNCTION st_convexhull(raster)
    RETURNS geometry
    AS 'MODULE_PATHNAME','RASTER_convex_hull'
    LANGUAGE 'c' IMMUTABLE STRICT
    COST 300;

This means that, by default rules, it's parallel unsafe, which makes anyone using it also unsafe. Now, I don't know if the parallelism declaration is missing or it really is unsafe, but that's where I would start looking into.

comment:2 in reply to:  1 Changed 3 months ago by davidp

Replying to Algunenano:

This means that, by default rules, it's parallel unsafe, which makes anyone using it also unsafe. Now, I don't know if the parallelism declaration is missing or it really is unsafe, but that's where I would start looking into.

I didn't thing so... PARALLEL UNSAFE apply only downstream (by my observations). So, function marked as parallel safe is executed in parallel mode, even if internally calls parallel unsafe function. Contrary, parallel unsafe function never executes in parallel mode, no matter how internally called functions are labeled.

Ind in our DB, functions are declared as follows:

CREATE OR REPLACE FUNCTION public._st_intersects(geom geometry, rast raster, nband integer DEFAULT NULL::integer)
 RETURNS boolean
 LANGUAGE plpgsql
 IMMUTABLE PARALLEL SAFE COST 1000
 ...

and

CREATE OR REPLACE FUNCTION public.st_intersects(geom geometry, rast raster, nband integer DEFAULT NULL::integer)
 RETURNS boolean
 LANGUAGE sql
 IMMUTABLE PARALLEL SAFE COST 1000
AS $function$ SELECT $1 OPERATOR(public.&&) $2::geometry AND public._st_intersects($1, $2, $3); $function$

And one correction to my original post... function default is PARALLEL UNSAFE, not RESTRICTED. Sorry, my fault.

Last edited 3 months ago by davidp (previous) (diff)

comment:3 Changed 3 months ago by Algunenano

So, function marked as parallel safe is executed in parallel mode, even if internally calls parallel unsafe function.

Yes, Postgresql trust the programmer when he declares a function parallel but, by definition, any function that calls another one that is parallel unsafe should be marked as unsafe too. If you don't, then your unsafe function might get called in parallel which leads to errors like this one or worse.

What I meant is, that if ST_ConverHull is unsafe (should be reviewed and tagged appropriately) then st_intersects(geom, raster, int) should be marked as unsafe too, but I haven't investigated any further.

comment:4 Changed 4 weeks ago by pramsey

Milestone: PostGIS 2.4.5PostGIS 2.4.6
Note: See TracTickets for help on using tickets.