[postgis-tickets] [SCM] PostGIS branch master updated. 3.1.0alpha2-64-ga15f892

[git at osgeo.org]

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  a15f8921b434454f9264eb4de0dbe12ff79a0711 (commit)
      from  82d31f324dd8fa53446114684b465de43d5653fa (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 a15f8921b434454f9264eb4de0dbe12ff79a0711
Author: Regina Obe <lr at pcorp.us>
Date:   Tue Sep 8 00:35:09 2020 -0400

    Add test for computed columns references #4750 for PostGIS 3.1

diff --git a/regress/core/Makefile.in b/regress/core/Makefile.in
index b9df38b..d10b25e 100644
--- a/regress/core/Makefile.in
+++ b/regress/core/Makefile.in
@@ -175,6 +175,11 @@ TESTS_SLOW = \
 	concave_hull_hard \
 	knn_recheck
 
+ifeq ($(shell expr "$(POSTGIS_PGSQL_VERSION)" ">=" 120),1)
+	TESTS += \
+		computed_columns
+endif
+
 ifeq ($(shell expr "$(POSTGIS_GEOS_VERSION)" ">=" 37),1)
 	# GEOS-3.7 adds:
 	# ST_FrechetDistance
diff --git a/regress/core/computed_columns.sql b/regress/core/computed_columns.sql
new file mode 100644
index 0000000..b60b4e3
--- /dev/null
+++ b/regress/core/computed_columns.sql
@@ -0,0 +1,60 @@
+CREATE SCHEMA testc;
+CREATE OR REPLACE FUNCTION testc.compute_exection_time(param_sql text) RETURNS interval
+AS $$
+DECLARE var_start_time timestamptz; var_end_time timestamptz;
+BEGIN
+var_start_time = clock_timestamp();
+EXECUTE param_sql;
+var_end_time = clock_timestamp();
+RETURN var_end_time - var_start_time;
+END;
+$$ language plpgsql;
+
+
+CREATE TABLE testc.city_boundary AS
+SELECT ST_BuildArea(ST_Collect(geom)) As geom
+	FROM (SELECT ST_Translate(ST_SnapToGrid(ST_Buffer(ST_Point(50 ,generate_series(50,300, 100)
+			),100, 'quad_segs=4'),1), x, 0)  As geom
+			FROM generate_series(1,1000,100) As x)  AS foo;
+
+CREATE TABLE testc.streets AS
+WITH d AS (SELECT dp.geom FROM testc.city_boundary AS c, ST_DumpPoints(c.geom) AS dp)
+SELECT ROW_NUMBER() OVER() AS id, ST_MakeLine(d1.geom, d2.geom) AS geom
+FROM d AS d1, d AS d2
+ORDER BY d1.geom <-> d2.geom DESC LIMIT 1000;
+
+CREATE INDEX ix_streets_geom ON testc.streets USING GIST(geom);
+
+CREATE TABLE testc.random_points AS
+SELECT dp.path[1] AS id, dp.geom
+FROM testc.city_boundary AS c
+	, ST_GeneratePoints(c.geom,500) AS gp
+	, ST_DumpPoints(gp) AS dp;
+
+ALTER TABLE testc.random_points
+    ADD CONSTRAINT PK_random_points
+    PRIMARY KEY (id);
+
+CREATE INDEX gix_random_points_geom
+    ON testc.random_points USING GIST (geom);
+
+ALTER TABLE testc.random_points
+    ADD way_buffer GEOMETRY (POLYGON)
+    GENERATED ALWAYS AS (ST_Buffer(geom, 500)) STORED ;
+
+CREATE INDEX gix_random_way_buffer_geom
+    ON testc.random_points USING GIST (way_buffer);
+
+analyze testc.random_points;
+analyze testc.streets;
+
+-- time using computed column should always be less than adhoc
+SELECT testc.compute_exection_time('SELECT COUNT(*) FROM testc.random_points AS p INNER JOIN testc.streets AS s ON ST_Contains(p.way_buffer, s.geom)') <
+testc.compute_exection_time('SELECT COUNT(*) FROM testc.random_points AS p INNER JOIN testc.streets AS s ON ST_Contains(ST_Buffer(p.geom, 500), s.geom);');
+
+-- confirm results are the same
+SELECT (SELECT COUNT(*) FROM testc.random_points AS p INNER JOIN testc.streets AS s ON ST_Contains(p.way_buffer, s.geom) ) =
+ (SELECT COUNT(*) FROM testc.random_points AS p INNER JOIN testc.streets AS s ON ST_Contains(ST_Buffer(p.geom, 500), s.geom) );
+
+-- cleanup
+DROP SCHEMA testc CASCADE;
diff --git a/regress/core/computed_columns_expected b/regress/core/computed_columns_expected
new file mode 100644
index 0000000..a40bacf
--- /dev/null
+++ b/regress/core/computed_columns_expected
@@ -0,0 +1,3 @@
+t
+t
+NOTICE:  drop cascades to 4 other objects

-----------------------------------------------------------------------

Summary of changes:
 regress/core/Makefile.in               |  5 +++
 regress/core/computed_columns.sql      | 60 ++++++++++++++++++++++++++++++++++
 regress/core/computed_columns_expected |  3 ++
 3 files changed, 68 insertions(+)
 create mode 100644 regress/core/computed_columns.sql
 create mode 100644 regress/core/computed_columns_expected


hooks/post-receive
-- 
PostGIS