[SCM] PostGIS branch stable-3.5 updated. 3.5.7-97-gf3995d28ec

[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, stable-3.5 has been updated
       via  f3995d28ecd4c23e8bc66ebe8d502a4850bb1104 (commit)
       via  dad473fde4ecd9395e8bc00ee52290037cebf38d (commit)
      from  196973e23c5e330058d25ee44dad8bf6d8b578ff (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 f3995d28ecd4c23e8bc66ebe8d502a4850bb1104
Merge: 196973e23c dad473fde4
Author: Darafei Praliaskouski <komzpa at gmail.com>
Date:   Tue Jul 28 12:55:12 2026 -0700

    Merge pull request 'regress: assert the plan in computed_columns on stable-3.5' (!607) from Komzpa/postgis:fix/computed-columns-plan-stable-3.5-20260728 into stable-3.5
    
    Backports the computed_columns planner assertion from master commit 5740442609069f82eb2384c045929548c665aa50 to stable-3.5.
    
    The stable-3.5 Woodie failure was not an upgrade-script defect. The script soft upgrade unpackaged3.5--:auto path reached the regression suite and failed because computed_columns compared two adjacent wall-clock measurements. The first boolean flipped from t to f while the row-count comparison stayed t, so the generated-column semantics were still correct and the test oracle was stale.
    
    This keeps the expected output shape unchanged and replaces the timing race with an EXPLAIN-based assertion that the stored generated-column query does not call ST_Buffer while the ad-hoc query does. The NEWS entry cites GT-582, the master-side pull request, as required for backports.
    
    Validation: git diff --check passed. In docker image repo.osgeo.org/postgis/build-test:debian13 with PostgreSQL 17.9, ./autogen.sh, configure --with-library-minor-version --enable-lto CFLAGS="-O2 -Wall -fno-omit-frame-pointer -Werror", make -j$(nproc), and make install completed. The exact reported path was then run with RUNTESTFLAGS="-v --extension --upgrade-path=unpackaged3.5--3.5.8dev! ..." and TESTS=/work/checkouts/worktrees/stable-3.5-fix/regress/core/computed_columns; it reported regress/core/computed_columns .. ok in 634 ms, Run tests: 3, Failed: 0, rc=0.
    
    The broader soft-upgrade sweep also passed the packaged 3.3.9, 3.4.5 and 3.5.5 upgrade legs before stopping on a separate interrupt_relate timing failure in unpackaged3.3; that was not the failing stable-3.5 dashboard symptom and is not changed here.
    
    Reviewed-on: https://gitea.osgeo.org/postgis/postgis/pulls/607


commit dad473fde4ecd9395e8bc00ee52290037cebf38d
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.
    
    (cherry picked from commit 5740442609069f82eb2384c045929548c665aa50)

diff --git a/NEWS b/NEWS
index e827053fcd..4319b581d9 100644
--- a/NEWS
+++ b/NEWS
@@ -5,6 +5,9 @@ PostGIS 3.5.8
 
   - GT-569, Initialize GSERIALIZED peek test fixtures so check-unit stays
     clean under Valgrind (Darafei Praliaskouski)
+  - GT-582, 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-575, Scope interrupt regression timeouts to the statements being
     canceled (Darafei Praliaskouski)
   - GT-522, Reject malformed GSERIALIZED payload counts before deserializing
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                              |  3 +++
 regress/core/computed_columns.sql | 22 +++++++++++++---------
 2 files changed, 16 insertions(+), 9 deletions(-)


hooks/post-receive
-- 
PostGIS