[postgis-tickets] r16244 - Simplify syntax for brin opclasses so upgrade script recognizes it
Sandro Santilli
strk at kbt.io
Tue Jan 9 10:41:55 PST 2018
Author: strk
Date: 2018-01-09 10:41:55 -0800 (Tue, 09 Jan 2018)
New Revision: 16244
Modified:
branches/2.3/NEWS
branches/2.3/postgis/geography_brin.sql.in
branches/2.3/postgis/postgis_brin.sql.in
Log:
Simplify syntax for brin opclasses so upgrade script recognizes it
Fixes #3956 in 2.3 branch
Modified: branches/2.3/NEWS
===================================================================
--- branches/2.3/NEWS 2018-01-09 18:41:25 UTC (rev 16243)
+++ branches/2.3/NEWS 2018-01-09 18:41:55 UTC (rev 16244)
@@ -6,6 +6,7 @@
- #3713, Support encodings that happen to output a '\' character
- #3965, ST_ClusterKMeans used to lose some clusters on initialization
(Darafei Praliaskouski)
+ - #3956, Brin opclass object does not upgrade properly (Sandro Santilli)
PostGIS 2.3.5
2017/11/15
Modified: branches/2.3/postgis/geography_brin.sql.in
===================================================================
--- branches/2.3/postgis/geography_brin.sql.in 2018-01-09 18:41:25 UTC (rev 16243)
+++ branches/2.3/postgis/geography_brin.sql.in 2018-01-09 18:41:55 UTC (rev 16244)
@@ -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.3/postgis/postgis_brin.sql.in
===================================================================
--- branches/2.3/postgis/postgis_brin.sql.in 2018-01-09 18:41:25 UTC (rev 16243)
+++ branches/2.3/postgis/postgis_brin.sql.in 2018-01-09 18:41:55 UTC (rev 16244)
@@ -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