[SCM] PostGIS branch master updated. 3.7.0beta1-118-g7e284f1625

[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  7e284f16258ac53540eb2b7bb9e842cf2752a072 (commit)
       via  5740442609069f82eb2384c045929548c665aa50 (commit)
      from  bad06afc2b7e21631b55765d159c24a08c68c51f (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 7e284f16258ac53540eb2b7bb9e842cf2752a072
Merge: bad06afc2b 5740442609
Author: Darafei Praliaskouski <komzpa at gmail.com>
Date:   Tue Jul 28 00:34:34 2026 -0700

    Merge pull request 'regress: assert the plan, not the clock, in computed_columns' (!582) from Komzpa/postgis:codex/computed-columns-plan-assertion into master
    
    `regress/core/computed_columns` asserted that a query reading a stored generated column finishes in fewer microseconds than one recomputing `ST_Buffer`, timed with `clock_timestamp()` around two `EXECUTE`s. That is a race between two adjacent measurements, sitting in a correctness suite.
    
    Woodie caught it doing exactly that. In one step's log the test both passes and fails:
    
    ```
    [1207]  regress/core/computed_columns .. ok in 1954 ms
    [1617]  regress/core/computed_columns .. failed (diff expected obtained: /tmp/pgis_reg/test_11_diff)
    ```
    
    and the diff shows only the first assertion flipping:
    
    ```
    @@ -1,3 +1,3 @@
    -t
    +t -> f
     t
    ```
    
    The second assertion, comparing the two row counts, is unchanged, so there is no geometry problem here.
    
    Uniform CPU load does not reproduce it -- six runs idle and six runs with 64 hogs on 32 cores all pass -- because contention slows both sides by the same factor and the inequality survives. What loses the race is asymmetry between the two measurements: an autovacuum landing in one of them, a cold buffer state on one side, a checkpoint.
    
    The intent of the test is sound and is a property of the **plan**, not of the clock: a stored generated column should be read rather than recomputed. So this asks the planner instead. Both directions are asserted -- the computed-column query must not call `ST_Buffer`, and the ad-hoc one must -- because checking only the first would still pass if the comparison stopped meaning anything.
    
    Verified on PostgreSQL 18.4 with GEOS 3.15.0beta2: three runs idle and three under a load average of 21 on 32 cores, all passing. It is also about nine times cheaper, 213 ms against 1954 ms, since it no longer executes both heavy queries an extra time purely to time them. The expected output file is unchanged.
    
    Reviewed-on: https://gitea.osgeo.org/postgis/postgis/pulls/582


commit 5740442609069f82eb2384c045929548c665aa50
Author: Darafei Praliaskouski <me at komzpa.net>
Date:   Tue Jul 28 07:11:39 2026 +0400

    regress: assert the plan, not the clock, in computed_columns
    
    The test asserted that a query using a stored generated column finishes in fewer
    microseconds than one recomputing ST_Buffer, timed with clock_timestamp around two
    EXECUTEs. That is a race between two adjacent measurements living in a correctness
    suite: uniform load slows both sides equally and it survives, but an autovacuum or a
    cold buffer state on one side is enough to lose it. Woodie caught it doing exactly
    that -- one step log has the test passing at line 1207 and failing at line 1617 of
    the same run, with only the first t flipping to f.
    
    The intent is sound and is a property of the plan, not of the clock: a stored
    generated column should be read rather than recomputed. So ask the planner. Both
    directions are asserted, because checking only that the computed-column query avoids
    ST_Buffer would still pass if the comparison stopped meaning anything.
    
    Verified on PostgreSQL 18.4 with GEOS 3.15.0beta2: three runs idle and three under a
    load average of 21 on 32 cores, all passing, where the old assertion is exactly the
    thing that varies. It is also nine times cheaper -- 213 ms against 1954 ms -- because
    it no longer runs both heavy queries an extra time purely to time them.

diff --git a/NEWS b/NEWS
index 43aa95bd86..dcbdc975d3 100644
--- a/NEWS
+++ b/NEWS
@@ -8,6 +8,10 @@ These are only changes since 3.7.0beta1.
 
 * Bug Fixes *
 
+ - Make the computed-columns regression test assert the query plan instead of racing two
+          stopwatches, so it no longer fails at random under CI load
+          (Darafei Praliaskouski)
+
  - GT-545, Fix FlatGeobuf geometry, property, and spatial-index byte order on
           big-endian platforms (Darafei Praliaskouski)
  - [raster] Fix invalid reads in raster band initialization and
diff --git a/regress/core/computed_columns.sql b/regress/core/computed_columns.sql
index b60b4e3c0c..417a73de17 100644
--- a/regress/core/computed_columns.sql
+++ b/regress/core/computed_columns.sql
@@ -1,12 +1,16 @@
 CREATE SCHEMA testc;
-CREATE OR REPLACE FUNCTION testc.compute_exection_time(param_sql text) RETURNS interval
+-- Does the plan for this query call the named function? The point of a stored generated column
+-- is that the value is read rather than recomputed, and that is a property of the plan. Timing the
+-- two queries against each other instead makes the test a race between adjacent measurements, which
+-- an autovacuum or a cold buffer on one side is enough to lose.
+CREATE OR REPLACE FUNCTION testc.plan_calls(param_sql text, fname text) RETURNS boolean
 AS $$
-DECLARE var_start_time timestamptz; var_end_time timestamptz;
+DECLARE var_line text; var_found boolean := false;
 BEGIN
-var_start_time = clock_timestamp();
-EXECUTE param_sql;
-var_end_time = clock_timestamp();
-RETURN var_end_time - var_start_time;
+FOR var_line IN EXECUTE 'EXPLAIN (COSTS OFF) ' || param_sql LOOP
+	IF var_line ILIKE '%' || fname || '%' THEN var_found := true; END IF;
+END LOOP;
+RETURN var_found;
 END;
 $$ language plpgsql;
 
@@ -48,9 +52,9 @@ CREATE INDEX gix_random_way_buffer_geom
 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);');
+-- the computed column is read, not recomputed, while the ad-hoc form does call ST_Buffer
+SELECT NOT testc.plan_calls('SELECT COUNT(*) FROM testc.random_points AS p INNER JOIN testc.streets AS s ON ST_Contains(p.way_buffer, s.geom)', 'st_buffer')
+AND testc.plan_calls('SELECT COUNT(*) FROM testc.random_points AS p INNER JOIN testc.streets AS s ON ST_Contains(ST_Buffer(p.geom, 500), s.geom)', 'st_buffer');
 
 -- 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) ) =

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

Summary of changes:
 NEWS                              |  4 ++++
 regress/core/computed_columns.sql | 22 +++++++++++++---------
 2 files changed, 17 insertions(+), 9 deletions(-)


hooks/post-receive
-- 
PostGIS