[postgis-tickets] r16241 - Simplify syntax for brin opclasses so upgrade script recognizes it

Sandro Santilli strk at kbt.io
Tue Jan 9 10:36:31 PST 2018


Author: strk
Date: 2018-01-09 10:36:31 -0800 (Tue, 09 Jan 2018)
New Revision: 16241

Modified:
   branches/2.4/NEWS
   branches/2.4/postgis/geography_brin.sql.in
   branches/2.4/postgis/postgis_brin.sql.in
Log:
Simplify syntax for brin opclasses so upgrade script recognizes it

Fixes #3956 in 2.4 branch

Modified: branches/2.4/NEWS
===================================================================
--- branches/2.4/NEWS	2018-01-09 18:36:18 UTC (rev 16240)
+++ branches/2.4/NEWS	2018-01-09 18:36:31 UTC (rev 16241)
@@ -8,7 +8,7 @@
   - #3930, Minimum bounding circle issues on 32-bit platforms
   - #3965, ST_ClusterKMeans used to lose some clusters on initialization
            (Darafei Praliaskouski)
-
+  - #3956, Brin opclass object does not upgrade properly (Sandro Santilli)
  * Enhancements *
   - #3944, Update to EPSG register v9.2 (Even Rouault)
 

Modified: branches/2.4/postgis/geography_brin.sql.in
===================================================================
--- branches/2.4/postgis/geography_brin.sql.in	2018-01-09 18:36:18 UTC (rev 16240)
+++ branches/2.4/postgis/geography_brin.sql.in	2018-01-09 18:36:31 UTC (rev 16241)
@@ -20,18 +20,7 @@
 LANGUAGE 'c' IMMUTABLE STRICT;
 
 
--- logic to create missing brin operators
--- note we can't use CREATE IF NOT EXISTS since not currently supported for operators or families
--- an earlier 2.3+ might miss this if doing upgrade from < 9.5 to 9.5+
--- so we need to include this always for 9.5+
--- Intentionally not indenting DDL cause our uninstall process can't pick it up
--- also not dollar quoting functions inside DO since gets tripped up by perl upgrade script parser
-DO  language 'plpgsql' $$
-BEGIN
-	-- add geography brin ops if missing and are running a newer than 9.4 --
-	-- use wildcard since if schema postgis is installed is not in search path, will have schema name in front
-	IF NOT EXISTS(SELECT 1 from pg_operator WHERE oprname = '&&' AND 
-		oprleft::regtype::text LIKE '%gidx' AND oprright::regtype::text LIKE '%geography' ) THEN
+
 -- Availability: 2.3.0
 CREATE OPERATOR && (
   LEFTARG    = gidx,
@@ -43,7 +32,7 @@
 -- Availability: 2.3.0
 CREATE OR REPLACE FUNCTION overlaps_geog(geography, gidx)
 RETURNS boolean
-AS 
+AS
   'SELECT $2 OPERATOR(@extschema at .&&) $1;'
  LANGUAGE SQL IMMUTABLE STRICT;
 
@@ -68,9 +57,6 @@
 --------------------------------
 
 -- Availability: 2.3.0
-CREATE OPERATOR FAMILY brin_geography_inclusion_ops USING brin;
-
--- Availability: 2.3.0
 CREATE OR REPLACE FUNCTION geog_brin_inclusion_add_value(internal, internal, internal, internal) RETURNS boolean
         AS 'MODULE_PATHNAME','geog_brin_inclusion_add_value'
         LANGUAGE 'c';
@@ -78,27 +64,15 @@
 -- Availability: 2.3.0
 CREATE OPERATOR CLASS brin_geography_inclusion_ops
   DEFAULT FOR TYPE geography
-  USING brin
-  FAMILY brin_geography_inclusion_ops AS
+  USING brin AS
+    FUNCTION      1        brin_inclusion_opcinfo(internal),
+    FUNCTION      2        geog_brin_inclusion_add_value(internal, internal, internal, internal),
+    FUNCTION      3        brin_inclusion_consistent(internal, internal, internal),
+    FUNCTION      4        brin_inclusion_union(internal, internal, internal),
     OPERATOR      3        &&(geography, geography),
-    FUNCTION      1        brin_inclusion_opcinfo(internal) ,
-    FUNCTION      2        geog_brin_inclusion_add_value(internal, internal, internal, internal) ,
-    FUNCTION      3        brin_inclusion_consistent(internal, internal, internal) ,
-    FUNCTION      4        brin_inclusion_union(internal, internal, internal) ,
+    OPERATOR      3        &&(geography, gidx),
+    OPERATOR      3        &&(gidx, geography),
+    OPERATOR      3        &&(gidx, gidx),
   STORAGE gidx;
 
-ALTER OPERATOR FAMILY brin_geography_inclusion_ops USING brin ADD
-    OPERATOR      3         &&(gidx, geography),
-
-    OPERATOR      3         &&(geography, gidx),
-
-    OPERATOR      3         &&(gidx, gidx);
-
-    END IF;
-  
-END;
-$$;
----------------------------------------------------------------
--- END
----------------------------------------------------------------
-#endif
\ No newline at end of file
+#endif

Modified: branches/2.4/postgis/postgis_brin.sql.in
===================================================================
--- branches/2.4/postgis/postgis_brin.sql.in	2018-01-09 18:36:18 UTC (rev 16240)
+++ branches/2.4/postgis/postgis_brin.sql.in	2018-01-09 18:36:31 UTC (rev 16241)
@@ -44,17 +44,6 @@
 AS 'MODULE_PATHNAME','gserialized_contains_box2df_box2df_2d'
 LANGUAGE 'c' IMMUTABLE STRICT;
 
--- logic to create missing brin operators
--- note we can't use CREATE IF NOT EXISTS since not currently supported for operators or families
--- an earlier 2.3+ might miss this if doing upgrade from < 9.5 to 9.5+
--- so we need to include this always for 9.5+
--- Intentionally not indenting DDL cause our uninstall process can't pick it up
-DO  language 'plpgsql' $$
-BEGIN
-	-- add geometry brin ops if missing and are running a newer than 9.4 --
-	-- use wildcard since if schema postgis is installed in is not in search path, will have schema name in front
-	IF NOT EXISTS(SELECT 1 from pg_operator WHERE oprname = '~' AND 
-		oprleft::regtype::text LIKE '%box2df' AND oprright::regtype::text LIKE '%geometry' ) THEN
 -- Availability: 2.3.0
 CREATE OPERATOR ~ (
 	LEFTARG    = box2df,
@@ -62,10 +51,6 @@
 	PROCEDURE  = contains_2d,
 	COMMUTATOR = @
 );
-	END IF;
-	
-	IF NOT EXISTS(SELECT 1 from pg_operator WHERE oprname = '@' AND 
-		oprleft::regtype::text LIKE '%box2df' AND oprright::regtype::text LIKE '%geometry' ) THEN
 
 -- Availability: 2.3.0
 CREATE OPERATOR @ (
@@ -86,7 +71,7 @@
 -- Availability: 2.3.0
 CREATE OR REPLACE FUNCTION contains_2d(geometry, box2df)
 RETURNS boolean
-AS 
+AS
 	'SELECT $2 OPERATOR(@extschema at .@) $1;'
 LANGUAGE SQL IMMUTABLE STRICT;
 
@@ -100,7 +85,7 @@
 -- Availability: 2.3.0
 CREATE OR REPLACE FUNCTION overlaps_2d(geometry, box2df)
 RETURNS boolean
-AS 
+AS
 	'SELECT $2 OPERATOR(@extschema at .&&) $1;'
 LANGUAGE SQL IMMUTABLE STRICT;
 
@@ -119,36 +104,35 @@
 	COMMUTATOR = ~,
 	PROCEDURE = is_contained_2d
 );
--- Availability: 2.3.0		
+-- Availability: 2.3.0
 CREATE OPERATOR && (
 	LEFTARG    = geometry,
 	RIGHTARG   = box2df,
 	PROCEDURE  = overlaps_2d,
 	COMMUTATOR = &&
 );
--- Availability: 2.3.0		
+-- Availability: 2.3.0
 CREATE OPERATOR && (
 	LEFTARG   = box2df,
 	RIGHTARG  = box2df,
 	PROCEDURE = overlaps_2d,
 	COMMUTATOR = &&
 );
--- Availability: 2.3.0		
+-- Availability: 2.3.0
 CREATE OPERATOR @ (
 	LEFTARG   = box2df,
 	RIGHTARG  = box2df,
 	PROCEDURE = is_contained_2d,
 	COMMUTATOR = ~
 );
--- Availability: 2.3.0		
+-- Availability: 2.3.0
 CREATE OPERATOR ~ (
 	LEFTARG   = box2df,
 	RIGHTARG  = box2df,
 	PROCEDURE = contains_2d,
 	COMMUTATOR = @
 );
-	END IF;
-	
+
 ----------------------------
 -- nd operators           --
 ----------------------------
@@ -165,25 +149,22 @@
 AS 'MODULE_PATHNAME','gserialized_gidx_gidx_overlaps'
 LANGUAGE 'c' IMMUTABLE STRICT;
 
-	IF NOT EXISTS(SELECT 1 from pg_operator WHERE oprname = '&&&' AND 
-		oprleft::regtype::text LIKE '%gidx' AND oprright::regtype::text LIKE '%geometry' ) THEN
-
--- Availability: 2.3.0		
+-- Availability: 2.3.0
 CREATE OPERATOR &&& (
 	LEFTARG    = gidx,
 	RIGHTARG   = geometry,
 	PROCEDURE  = overlaps_nd,
 	COMMUTATOR = &&&
 );
-	
+
 -- Availability: 2.3.0
 CREATE OR REPLACE FUNCTION overlaps_nd(geometry, gidx)
 RETURNS boolean
-AS 
+AS
 	'SELECT $2 OPERATOR(@extschema at .&&&) $1;'
 LANGUAGE SQL IMMUTABLE STRICT;
 
-	
+
 -- Availability: 2.3.0
 CREATE OPERATOR &&& (
 	LEFTARG    = geometry,
@@ -201,136 +182,88 @@
 	COMMUTATOR = &&&
 );
 
-	END IF;
-	
 	------------------------------
 	-- Create operator families --
 	------------------------------
-	
+
 	-------------
 	-- 2D case --
 	-------------
-	IF NOT EXISTS(SELECT 1 FROM pg_opfamily WHERE opfname = 'brin_geometry_inclusion_ops_2d') THEN
-	
-CREATE OPERATOR FAMILY brin_geometry_inclusion_ops_2d USING brin;
 
-	END IF;
-	
 -- Availability: 2.3.0
 CREATE OR REPLACE FUNCTION geom2d_brin_inclusion_add_value(internal, internal, internal, internal) RETURNS boolean
 	AS 'MODULE_PATHNAME','geom2d_brin_inclusion_add_value'
 	LANGUAGE 'c';
-	
+
 -- Availability: 2.3.0
 CREATE OR REPLACE FUNCTION geom3d_brin_inclusion_add_value(internal, internal, internal, internal) RETURNS boolean
 	AS 'MODULE_PATHNAME','geom3d_brin_inclusion_add_value'
 	LANGUAGE 'c';
-	
+
 -- Availability: 2.3.0
 CREATE OR REPLACE FUNCTION geom4d_brin_inclusion_add_value(internal, internal, internal, internal) RETURNS boolean
 	AS 'MODULE_PATHNAME','geom4d_brin_inclusion_add_value'
 	LANGUAGE 'c';
-		
-	IF NOT EXISTS(SELECT 1 FROM pg_opclass WHERE opcname = 'brin_geometry_inclusion_ops_2d') THEN
-	
+
 -- Availability: 2.3.0
 CREATE OPERATOR CLASS brin_geometry_inclusion_ops_2d
   DEFAULT FOR TYPE geometry
-  USING brin
-  FAMILY brin_geometry_inclusion_ops_2d AS
+  USING brin AS
+    FUNCTION      1        brin_inclusion_opcinfo(internal),
+    FUNCTION      2        geom2d_brin_inclusion_add_value(internal, internal, internal, internal),
+    FUNCTION      3        brin_inclusion_consistent(internal, internal, internal),
+    FUNCTION      4        brin_inclusion_union(internal, internal, internal),
+    OPERATOR      3         &&(box2df, box2df),
+    OPERATOR      3         &&(box2df, geometry),
+    OPERATOR      3         &&(geometry, box2df),
     OPERATOR      3        &&(geometry, geometry),
+    OPERATOR      7         ~(box2df, box2df),
+    OPERATOR      7         ~(box2df, geometry),
+    OPERATOR      7         ~(geometry, box2df),
     OPERATOR      7        ~(geometry, geometry),
+    OPERATOR      8         @(box2df, box2df),
+    OPERATOR      8         @(box2df, geometry),
+    OPERATOR      8         @(geometry, box2df),
     OPERATOR      8        @(geometry, geometry),
-    FUNCTION      1        brin_inclusion_opcinfo(internal) ,
-    FUNCTION      2        geom2d_brin_inclusion_add_value(internal, internal, internal, internal) ,
-    FUNCTION      3        brin_inclusion_consistent(internal, internal, internal) ,
-    FUNCTION      4        brin_inclusion_union(internal, internal, internal) ,
   STORAGE box2df;
 
-ALTER OPERATOR FAMILY brin_geometry_inclusion_ops_2d USING brin ADD
-    OPERATOR      3         &&(box2df, geometry),
-    OPERATOR      7         ~(box2df, geometry),
-    OPERATOR      8         @(box2df, geometry),
-
-    OPERATOR      3         &&(geometry, box2df),
-    OPERATOR      7         ~(geometry, box2df),
-    OPERATOR      8         @(geometry, box2df),
-
-    OPERATOR      3         &&(box2df, box2df),
-    OPERATOR      7         ~(box2df, box2df),
-    OPERATOR      8         @(box2df, box2df);
-
-	END IF;
-	
 		-------------
 		-- 3D case --
 		-------------
-	IF NOT EXISTS(SELECT 1 FROM pg_opfamily WHERE opfname = 'brin_geometry_inclusion_ops_3d') THEN
--- Availability: 2.3.0
-CREATE OPERATOR FAMILY brin_geometry_inclusion_ops_3d USING brin;
 
-	END IF;	
-	
-	IF NOT EXISTS(SELECT 1 FROM pg_opclass WHERE opcname = 'brin_geometry_inclusion_ops_3d') THEN
-	
 -- Availability: 2.3.0
 CREATE OPERATOR CLASS brin_geometry_inclusion_ops_3d
   FOR TYPE geometry
-  USING brin
-  FAMILY brin_geometry_inclusion_ops_3d AS
-    OPERATOR      3        &&&(geometry, geometry),
+  USING brin AS
     FUNCTION      1        brin_inclusion_opcinfo(internal) ,
-    FUNCTION      2        geom3d_brin_inclusion_add_value(internal, internal, internal, internal) ,
-    FUNCTION      3        brin_inclusion_consistent(internal, internal, internal) ,
-    FUNCTION      4        brin_inclusion_union(internal, internal, internal) ,
+    FUNCTION      2        geom3d_brin_inclusion_add_value(internal, internal, internal, internal),
+    FUNCTION      3        brin_inclusion_consistent(internal, internal, internal),
+    FUNCTION      4        brin_inclusion_union(internal, internal, internal),
+    OPERATOR      3        &&&(geometry, geometry),
+    OPERATOR      3        &&&(geometry, gidx),
+    OPERATOR      3        &&&(gidx, geometry),
+    OPERATOR      3        &&&(gidx, gidx),
   STORAGE gidx;
 
-ALTER OPERATOR FAMILY brin_geometry_inclusion_ops_3d USING brin ADD
-    OPERATOR      3         &&&(gidx, geometry),
-
-    OPERATOR      3         &&&(geometry, gidx),
-
-    OPERATOR      3         &&&(gidx, gidx);
-	
-	END IF;
-	
-	
 		-------------
 		-- 4D case --
 		-------------
-	IF NOT EXISTS(SELECT 1 FROM pg_opfamily WHERE opfname = 'brin_geometry_inclusion_ops_4d') THEN
-	
--- Availability: 2.3.0
-CREATE OPERATOR FAMILY brin_geometry_inclusion_ops_4d USING brin;
 
 -- Availability: 2.3.0
 CREATE OPERATOR CLASS brin_geometry_inclusion_ops_4d
   FOR TYPE geometry
-  USING brin
-  FAMILY brin_geometry_inclusion_ops_4d AS
+  USING brin AS
+    FUNCTION      1        brin_inclusion_opcinfo(internal),
+    FUNCTION      2        geom4d_brin_inclusion_add_value(internal, internal, internal, internal),
+    FUNCTION      3        brin_inclusion_consistent(internal, internal, internal),
+    FUNCTION      4        brin_inclusion_union(internal, internal, internal),
     OPERATOR      3        &&&(geometry, geometry),
-    FUNCTION      1        brin_inclusion_opcinfo(internal) ,
-    FUNCTION      2        geom4d_brin_inclusion_add_value(internal, internal, internal, internal) ,
-    FUNCTION      3        brin_inclusion_consistent(internal, internal, internal) ,
-    FUNCTION      4        brin_inclusion_union(internal, internal, internal) ,
+    OPERATOR      3        &&&(geometry, gidx),
+    OPERATOR      3        &&&(gidx, geometry),
+    OPERATOR      3        &&&(gidx, gidx),
   STORAGE gidx;
 
-ALTER OPERATOR FAMILY brin_geometry_inclusion_ops_4d USING brin ADD
-    OPERATOR      3         &&&(gidx, geometry),
-
-    OPERATOR      3         &&&(geometry, gidx),
-
-    OPERATOR      3         &&&(gidx, gidx);
-	
-	END IF;
-		
----------------------------------------------------------------
--- END
----------------------------------------------------------------
-END;
-$$;
-
 -----------------------
 -- BRIN support end
 -----------------------
-#endif
\ No newline at end of file
+#endif



More information about the postgis-tickets mailing list