[SCM] PostGIS branch stable-3.6 updated. 3.6.4-102-g75a82dfb33

[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.6 has been updated
       via  75a82dfb3387c36ffcd3f93e21c7e991bfeb37a4 (commit)
       via  b2fd6021c53a9c942dc2db8aa367b7a89d83a8e9 (commit)
      from  18c2f992bdea7ffd57ab90109247c00aacfead06 (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 75a82dfb3387c36ffcd3f93e21c7e991bfeb37a4
Merge: 18c2f992bd b2fd6021c5
Author: Darafei Praliaskouski <komzpa at gmail.com>
Date:   Tue Jul 28 12:55:00 2026 -0700

    Merge pull request 'regress: assert the plan in computed_columns on stable-3.6' (!613) from Komzpa/postgis:fix/computed-columns-plan-stable-3.6-20260728 into stable-3.6
    
    Backports the computed_columns planner assertion from master commit 5740442609069f82eb2384c045929548c665aa50 to stable-3.6.
    
    The regression compared two adjacent wall-clock measurements to prove that a stored generated column was read instead of recomputed. Under CI load, unrelated buffer or autovacuum timing could reverse that comparison even when the generated-column behavior was correct.
    
    This 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 expected output shape remains unchanged.
    
    The NEWS entry cites GT-582, the pull request where the fix was recorded on master: https://gitea.osgeo.org/postgis/postgis/pulls/582.
    
    Reviewed-on: https://gitea.osgeo.org/postgis/postgis/pulls/613


commit b2fd6021c53a9c942dc2db8aa367b7a89d83a8e9
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 e720287762..26417e01b4 100644
--- a/NEWS
+++ b/NEWS
@@ -3,6 +3,9 @@ PostGIS 3.6.5
 
 * Fixes *
 
+- 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-569, Initialize GSERIALIZED peek test fixtures so check-unit stays
   clean under Valgrind (Darafei Praliaskouski)
 - GT-575, Scope interrupt regression timeouts to the statements being
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