[SCM] PostGIS branch master updated. 3.6.0rc2-654-g4c65e7d66
git at osgeo.org
git at osgeo.org
Sun Jun 21 13:32:48 PDT 2026
This is an automated email from the git hooks/post-receive script. It was
generated because a ref change was pushed to the repository containing
the project "PostGIS".
The branch, master has been updated
via 4c65e7d664d7fb279c90f80674573b940a662dce (commit)
from 633a4c5663021efbd2ad8fa11f6a0e93ccda226d (commit)
Those revisions listed above that are new to this repository have
not appeared on any other notification email; so we list those
revisions in full, below.
- Log -----------------------------------------------------------------
commit 4c65e7d664d7fb279c90f80674573b940a662dce
Author: Darafei Praliaskouski <me at komzpa.net>
Date: Sat Jun 20 22:16:20 2026 +0400
Infer geometry_columns constraints for direct views
Teach geometry_columns to inherit constraint-derived geometry metadata from direct view and materialized-view columns by reading top-level rewrite-rule origin metadata. Keep expression outputs generic unless they carry an explicit typmod cast.
Parse only the top-level target list so nested subqueries cannot make an expression column inherit base-table constraints.
Closes #1705
Closes https://github.com/postgis/postgis/pull/1065
diff --git a/NEWS b/NEWS
index 117a044bc..b3d2d98a0 100644
--- a/NEWS
+++ b/NEWS
@@ -100,6 +100,8 @@ To take advantage of all postgis_sfcgal extension features SFCGAL 2.3+ is needed
(Darafei Praliaskouski)
- #3103, Add regression coverage for exact-schema find_srid and
geometry_columns lookups (Darafei Praliaskouski)
+ - #1705, Infer constraint metadata for direct view and materialized view
+ geometry columns (Darafei Praliaskouski)
- #6038, Avoid stale relation lookups in geometry_columns after topology
objects are dropped (Darafei Praliaskouski)
- #5655, [doc] Skip XML validation during `make check` when xsltproc is
diff --git a/doc/using_postgis_dataman.xml b/doc/using_postgis_dataman.xml
index b902f54af..d5b91fc75 100644
--- a/doc/using_postgis_dataman.xml
+++ b/doc/using_postgis_dataman.xml
@@ -1768,9 +1768,10 @@ SELECT populate_geometry_columns('myschema.my_special_pois'::regclass);
-- set optional use_typmod argument to false
SELECT populate_geometry_columns('myschema.my_special_pois'::regclass, false); </programlisting>
-<para>Although the old-constraint based method is still supported, a constraint-based geometry column used directly
-in a view, will not register correctly in geometry_columns, as will a typmod one.
-In this example we define a column using typmod and another using constraints.</para>
+<para>Although the old constraint-based method is still supported, direct
+pass-through view columns register correctly in <varname>geometry_columns</varname>
+for both typmod-based and constraint-based source columns. In this example we
+define a column using typmod and another using constraints.</para>
<programlisting>CREATE TABLE pois_ny(gid SERIAL PRIMARY KEY, poi_name text, cat text, geom geometry(POINT,4326));
SELECT AddGeometryColumn('pois_ny', 'geom_2160', 2160, 'POINT', 2, false);</programlisting>
<para>If we run in psql</para>
@@ -1809,20 +1810,21 @@ SELECT *
SELECT f_table_name, f_geometry_column, srid, type
FROM geometry_columns
WHERE f_table_name = 'vw_pois_ny_parks';</programlisting>
-<para>The typmod based geom view column registers correctly,
-but the constraint based one does not.</para>
+<para>Both the typmod based geom view column and the direct constraint based
+view column register correctly.</para>
<screen> f_table_name | f_geometry_column | srid | type
------------------+-------------------+------+----------
vw_pois_ny_parks | geom | 4326 | POINT
- vw_pois_ny_parks | geom_2160 | 0 | GEOMETRY</screen>
+ vw_pois_ny_parks | geom_2160 | 2160 | POINT</screen>
-<para>This may change in future versions of PostGIS, but for now
-to force the constraint-based view column to register correctly, you need to do this:</para>
+<para>If the view applies a spatial function to the geometry, such as
+<function>ST_Transform</function>, you still need to cast the transformed
+column explicitly so the view can expose the correct type and SRID:</para>
<programlisting>DROP VIEW vw_pois_ny_parks;
CREATE VIEW vw_pois_ny_parks AS
SELECT gid, poi_name, cat,
geom,
- geom_2160::geometry(POINT,2160) As geom_2160
+ ST_Transform(geom, 2160)::geometry(POINT,2160) As geom_2160
FROM pois_ny
WHERE cat = 'park';
SELECT f_table_name, f_geometry_column, srid, type
diff --git a/postgis/postgis.sql.in b/postgis/postgis.sql.in
index 027170b68..d4025cda8 100644
--- a/postgis/postgis.sql.in
+++ b/postgis/postgis.sql.in
@@ -6575,6 +6575,99 @@ LANGUAGE 'sql' STABLE STRICT PARALLEL SAFE _COST_MEDIUM;
-- Changed: 2.4.0 List also Parent partitioned tables
-- Changed: 2.5.2 replace use of pg_constraint.consrc with pg_get_constraintdef, consrc removed pg12
+-- Extract base-column origin metadata only from a view rule's top-level target list.
+-- pg_node_tree text also contains nested Query target lists for subqueries; treating
+-- those as outer view columns would make expression outputs inherit constraints.
+CREATE OR REPLACE FUNCTION _postgis_geometry_columns_view_column_origin(
+ view_rule pg_node_tree,
+ view_attnum smallint)
+RETURNS TABLE(base_relid oid, base_attnum smallint)
+AS $$
+DECLARE
+ rule_text text := view_rule::text;
+ rule_len integer := length(rule_text);
+ i integer := 1;
+ token text := ':targetList (';
+ token_len integer := length(':targetList (');
+ ch text;
+ prev_ch text;
+ in_quote boolean := false;
+ paren_depth integer := 0;
+ brace_depth integer := 0;
+ in_top_targetlist boolean := false;
+ entry_start integer := 0;
+ entry_text text;
+ match text[];
+BEGIN
+ WHILE i <= rule_len LOOP
+ IF NOT in_quote AND NOT in_top_targetlist
+ AND paren_depth = 1
+ AND brace_depth = 1
+ AND substr(rule_text, i, token_len) = token
+ THEN
+ in_top_targetlist := true;
+ paren_depth := paren_depth + 1;
+ i := i + token_len;
+ CONTINUE;
+ END IF;
+
+ ch := substr(rule_text, i, 1);
+
+ IF ch = '"' AND prev_ch IS DISTINCT FROM chr(92) THEN
+ in_quote := NOT in_quote;
+ ELSIF NOT in_quote THEN
+ IF in_top_targetlist
+ AND entry_start = 0
+ AND paren_depth = 2
+ AND brace_depth = 1
+ AND substr(rule_text, i, 12) = '{TARGETENTRY'
+ THEN
+ entry_start := i;
+ END IF;
+
+ IF ch = '(' THEN
+ paren_depth := paren_depth + 1;
+ ELSIF ch = ')' THEN
+ paren_depth := paren_depth - 1;
+ IF in_top_targetlist AND paren_depth = 1 THEN
+ RETURN;
+ END IF;
+ ELSIF ch = '{' THEN
+ brace_depth := brace_depth + 1;
+ ELSIF ch = '}' THEN
+ brace_depth := brace_depth - 1;
+ IF in_top_targetlist
+ AND entry_start > 0
+ AND brace_depth = 1
+ AND paren_depth = 2
+ THEN
+ entry_text := substr(rule_text, entry_start, i - entry_start + 1);
+ entry_start := 0;
+ match := regexp_match(
+ entry_text,
+ $re$^\{TARGETENTRY :expr \{VAR [^}]*\} :resno ([0-9]+) :resname [^:]+ :ressortgroupref [0-9]+ :resorigtbl ([0-9]+) :resorigcol ([0-9]+) :resjunk false\}$re$
+ );
+ IF match IS NOT NULL
+ AND match[1]::smallint = view_attnum
+ AND match[2] <> '0'
+ AND match[3] <> '0'
+ THEN
+ base_relid := match[2]::oid;
+ base_attnum := match[3]::smallint;
+ RETURN NEXT;
+ RETURN;
+ END IF;
+ END IF;
+ END IF;
+ END IF;
+
+ prev_ch := ch;
+ i := i + 1;
+ END LOOP;
+END
+$$
+LANGUAGE 'plpgsql' STABLE STRICT PARALLEL SAFE;
+
CREATE OR REPLACE VIEW geometry_columns AS
WITH constraint_defs AS (
-- Trim trailing NOT VALID so metadata inference works before constraint validation (#4828).
@@ -6595,6 +6688,17 @@ SELECT current_database()::character varying(256) AS f_table_catalog,
JOIN pg_attribute a ON a.attrelid = c.oid AND NOT a.attisdropped
JOIN pg_namespace n ON c.relnamespace = n.oid
JOIN pg_type t ON a.atttypid = t.oid
+ -- Direct view columns have rewrite-rule target entries with origin metadata.
+ -- Use only top-level bare VAR target entries, so expressions keep needing explicit casts.
+ LEFT JOIN LATERAL (
+ SELECT origin.base_relid,
+ origin.base_attnum
+ FROM pg_rewrite AS r
+ CROSS JOIN LATERAL _postgis_geometry_columns_view_column_origin(r.ev_action, a.attnum) AS origin
+ WHERE c.relkind = ANY (ARRAY['v'::"char", 'm'::"char"])
+ AND r.ev_class = c.oid
+ AND r.rulename = '_RETURN'
+ ) AS vco ON true
LEFT JOIN (
SELECT s.connamespace,
s.conrelid,
@@ -6602,7 +6706,7 @@ SELECT current_database()::character varying(256) AS f_table_catalog,
(regexp_match(s.consrc, $$geometrytype\(\w+\)\s*=\s*'(\w+)'$$, 'i'))[1]::text AS type
FROM constraint_defs AS s
WHERE s.consrc ~* $$geometrytype\(\w+\)\s*=\s*'\w+'$$::text
- ) st ON st.connamespace = n.oid AND st.conrelid = c.oid AND (a.attnum = ANY (st.conkey))
+ ) st ON st.conrelid = COALESCE(vco.base_relid, c.oid) AND (COALESCE(vco.base_attnum, a.attnum) = ANY (st.conkey))
LEFT JOIN (
SELECT s.connamespace,
s.conrelid,
@@ -6610,7 +6714,7 @@ SELECT current_database()::character varying(256) AS f_table_catalog,
(regexp_match(s.consrc, $$ndims\(\w+\)\s*=\s*(\d+)$$, 'i'))[1]::integer AS ndims
FROM constraint_defs AS s
WHERE s.consrc ~* $$ndims\(\w+\)\s*=\s*\d+$$::text
- ) sn ON sn.connamespace = n.oid AND sn.conrelid = c.oid AND (a.attnum = ANY (sn.conkey))
+ ) sn ON sn.conrelid = COALESCE(vco.base_relid, c.oid) AND (COALESCE(vco.base_attnum, a.attnum) = ANY (sn.conkey))
LEFT JOIN (
SELECT s.connamespace,
s.conrelid,
@@ -6618,7 +6722,7 @@ SELECT current_database()::character varying(256) AS f_table_catalog,
(regexp_match(s.consrc, $$srid\(\w+\)\s*=\s*(\d+)$$, 'i'))[1]::integer As srid
FROM constraint_defs AS s
WHERE s.consrc ~* $$srid\(\w+\)\s*=\s*\d+$$::text
- ) sr ON sr.connamespace = n.oid AND sr.conrelid = c.oid AND (a.attnum = ANY (sr.conkey))
+ ) sr ON sr.conrelid = COALESCE(vco.base_relid, c.oid) AND (COALESCE(vco.base_attnum, a.attnum) = ANY (sr.conkey))
WHERE (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'm'::"char", 'f'::"char", 'p'::"char"]))
AND NOT c.relname = 'raster_columns'::name AND t.typname = 'geometry'::name
AND NOT pg_is_other_temp_schema(c.relnamespace)
diff --git a/regress/core/tickets.sql b/regress/core/tickets.sql
index b31490528..c8a6e69cb 100644
--- a/regress/core/tickets.sql
+++ b/regress/core/tickets.sql
@@ -1654,6 +1654,49 @@ SELECT f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, t
ORDER BY f_table_name, f_geometry_column;
DROP TABLE IF EXISTS test5829, test5978;
+-- #1705, constraint-based geometry metadata for direct view columns
+CREATE TABLE test1705 (
+ gid integer,
+ geom geometry(Point, 4326),
+ geom_2160 geometry);
+ALTER TABLE test1705
+ ADD CONSTRAINT enforce_dims_geom_2160
+ CHECK (st_ndims(geom_2160) = 2);
+ALTER TABLE test1705
+ ADD CONSTRAINT enforce_srid_geom_2160
+ CHECK (st_srid(geom_2160) = 2160);
+ALTER TABLE test1705
+ ADD CONSTRAINT enforce_geotype_geom_2160
+ CHECK (geometrytype(geom_2160) = 'POINT'::text OR geom_2160 IS NULL);
+CREATE VIEW test1705_view AS
+ SELECT *
+ FROM test1705
+ WHERE gid > 0;
+CREATE VIEW test1705_alias_view AS
+ SELECT geom_2160 AS shape
+ FROM test1705;
+CREATE VIEW test1705_buffer_view AS
+ SELECT ST_Buffer(geom_2160, 1) AS geom_2160
+ FROM test1705;
+CREATE VIEW test1705_nested_buffer_view AS
+ SELECT ST_Buffer(geom_2160, 1) AS geom_2160
+ FROM (SELECT geom_2160 FROM test1705) AS nested;
+CREATE VIEW test1705_scalar_buffer_view AS
+ SELECT ST_Buffer((SELECT geom_2160 FROM test1705 LIMIT 1), 1) AS geom_2160;
+CREATE VIEW test1705_mixed_view AS
+ SELECT ST_Buffer(geom_2160, 1) AS buffered,
+ geom_2160
+ FROM test1705;
+CREATE MATERIALIZED VIEW test1705_matview AS
+ SELECT geom_2160
+ FROM test1705;
+SELECT '#1705', f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, type
+ FROM geometry_columns WHERE f_table_name IN ('test1705', 'test1705_view', 'test1705_alias_view', 'test1705_buffer_view', 'test1705_nested_buffer_view', 'test1705_scalar_buffer_view', 'test1705_mixed_view', 'test1705_matview')
+ ORDER BY f_table_name, f_geometry_column;
+DROP MATERIALIZED VIEW test1705_matview;
+DROP VIEW test1705_alias_view, test1705_buffer_view, test1705_nested_buffer_view, test1705_scalar_buffer_view, test1705_mixed_view, test1705_view;
+DROP TABLE test1705;
+
-- -------------------------------------------------------------------------------------
-- #3103, geometry_columns/find_srid exact-match behavior
CREATE SCHEMA test3103a;
diff --git a/regress/core/tickets_expected b/regress/core/tickets_expected
index 9e968e2dd..93529c7b5 100644
--- a/regress/core/tickets_expected
+++ b/regress/core/tickets_expected
@@ -499,6 +499,17 @@ public.test5978.geometry SRID:4326 TYPE:POINT DIMS:2
public|test5829|geom|2|4326|GEOMETRY
public|test5978|geometry|2|4326|POINT
public|test5978|shape|2|4326|POINT
+#1705|public|test1705|geom|2|4326|POINT
+#1705|public|test1705|geom_2160|2|2160|POINT
+#1705|public|test1705_alias_view|shape|2|2160|POINT
+#1705|public|test1705_buffer_view|geom_2160|2|0|GEOMETRY
+#1705|public|test1705_matview|geom_2160|2|2160|POINT
+#1705|public|test1705_mixed_view|buffered|2|0|GEOMETRY
+#1705|public|test1705_mixed_view|geom_2160|2|2160|POINT
+#1705|public|test1705_nested_buffer_view|geom_2160|2|0|GEOMETRY
+#1705|public|test1705_scalar_buffer_view|geom_2160|2|0|GEOMETRY
+#1705|public|test1705_view|geom|2|4326|POINT
+#1705|public|test1705_view|geom_2160|2|2160|POINT
#3103.1|4326
#3103.2|3857
#3103.3|test3103b|3857
-----------------------------------------------------------------------
Summary of changes:
NEWS | 2 +
doc/using_postgis_dataman.xml | 20 ++++----
postgis/postgis.sql.in | 110 ++++++++++++++++++++++++++++++++++++++++--
regress/core/tickets.sql | 43 +++++++++++++++++
regress/core/tickets_expected | 11 +++++
5 files changed, 174 insertions(+), 12 deletions(-)
hooks/post-receive
--
PostGIS
More information about the postgis-tickets
mailing list