[SCM] PostGIS branch master updated. 3.7.0beta1-177-g07bdf384af

[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  07bdf384af94565ac3e2ed5774afddea0f5ca548 (commit)
       via  248b014b9805911792e2fcc097ce7c0cde422308 (commit)
      from  08f9832fb36e066a57e53f285cf33914e9061aaf (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 07bdf384af94565ac3e2ed5774afddea0f5ca548
Merge: 08f9832fb3 248b014b98
Author: Darafei Praliaskouski <komzpa at gmail.com>
Date:   Sun Aug 2 12:20:13 2026 -0700

    Merge pull request 'Do not ANALYZE inside the extension upgrade transaction' (!650) from Komzpa/postgis:ci/no-analyze-in-extension-script-20260802 into master
    
    `spatial_ref_sys.sql` ends with
    
        ON CONFLICT (srid) DO NOTHING;
        COMMIT;
        ANALYZE "spatial_ref_sys";
    
    The `ANALYZE` follows `COMMIT` on purpose: run standalone, it refreshes the statistics of a
    table that has just gained thousands of rows, outside any transaction.
    
    `extensions/postgis/Makefile.in` strips `BEGIN` and `COMMIT` — its own comment says why,
    "these are not allowed in extensions" — and leaves the `ANALYZE`. So the statement written to
    run *after* the transaction ends up *inside* the extension upgrade's transaction, takes
    `ShareUpdateExclusiveLock` on `spatial_ref_sys`, and can deadlock against autovacuum analysing
    the same table:
    
        ERROR:  deadlock detected
        DETAIL:  Process 8802 waits for ShareUpdateExclusiveLock on relation 52329 ...;
                 blocked by process 8803.  Process 8803 waits for ShareLock on
                 transaction 9332; blocked by process 8802.
        CONTEXT:  SQL statement "ANALYZE "spatial_ref_sys""
                  extension script file "postgis--ANY--3.7.0dev.sql", near line 45810
    
    **This is not only a CI problem.** `ALTER EXTENSION postgis UPDATE` runs this script on real
    databases, where autovacuum is running by definition, so an upgrade on a busy installation can
    simply abort.
    
    It also explains real CI noise: three of the eleven most recent failed Woodpecker pipelines
    died this way, in `regress/test-install` and `regress/test-upgrades`, on ordinary amd64 rows
    rather than emulated ones. Pull request 626 addressed the same class on armhf by disabling
    autovacuum in that workflow — a CI workaround that protects the fleet and leaves users exposed.
    
    This strips the `ANALYZE` along with the transaction control it was written to follow. The
    standalone `spatial_ref_sys.sql` keeps it; autovacuum analyses the table on its own schedule,
    so the extension script does not need to.
    
    Verified by generating the file both ways: the standalone script still ends with `COMMIT;` and
    `ANALYZE "spatial_ref_sys";`, and the extension copy now ends at `ON CONFLICT (srid) DO NOTHING;`.
    
    Reviewed-on: https://gitea.osgeo.org/postgis/postgis/pulls/650


commit 248b014b9805911792e2fcc097ce7c0cde422308
Author: Darafei Praliaskouski <me at komzpa.net>
Date:   Sun Aug 2 20:03:01 2026 +0400

    Do not ANALYZE inside the extension upgrade transaction
    
    spatial_ref_sys.sql ends with
    
      ON CONFLICT (srid) DO NOTHING;
      COMMIT;
      ANALYZE "spatial_ref_sys";
    
    The ANALYZE sits after COMMIT on purpose: run standalone it refreshes the
    statistics of a table that just gained thousands of rows, outside any
    transaction.  The extension build strips BEGIN and COMMIT because those are
    not allowed in extension scripts, and left the ANALYZE behind — so it ends up
    *inside* the upgrade's transaction, takes ShareUpdateExclusiveLock on
    spatial_ref_sys, and can deadlock against autovacuum analysing the same table:
    
      ERROR:  deadlock detected
      DETAIL:  Process A waits for ShareUpdateExclusiveLock on relation ...;
               blocked by process B.  Process B waits for ShareLock on
               transaction ...; blocked by process A.
      CONTEXT:  SQL statement "ANALYZE "spatial_ref_sys""
                extension script file "postgis--ANY--3.7.0dev.sql"
    
    That aborts ALTER EXTENSION postgis UPDATE, so a user upgrading a busy
    database can simply lose the upgrade.  It also accounts for three of the
    eleven most recent Woodpecker pipeline failures, in regress/test-install and
    regress/test-upgrades, on ordinary amd64 rows rather than emulated ones.
    
    Strip the ANALYZE along with the transaction control it was written to follow.
    The standalone spatial_ref_sys.sql keeps it; autovacuum analyses the table on
    its own schedule, so the extension script does not need to.

diff --git a/NEWS b/NEWS
index 08d1623765..d663d01851 100644
--- a/NEWS
+++ b/NEWS
@@ -8,6 +8,8 @@ These are only changes since 3.7.0beta1.
 
 * Bug Fixes *
 
+ - Stop the extension upgrade script running ANALYZE inside its transaction,
+          where it could deadlock with autovacuum (Darafei Praliaskouski)
  - [liblwgeom] Reject malformed GSERIALIZED NURBS before curve
           evaluation (Darafei Praliaskouski)
  - GT-619, Make interrupt regression tests compare timeout latency to
diff --git a/extensions/postgis/Makefile.in b/extensions/postgis/Makefile.in
index 88ef35afdf..fbf3bd09aa 100644
--- a/extensions/postgis/Makefile.in
+++ b/extensions/postgis/Makefile.in
@@ -104,9 +104,21 @@ sql/$(EXTENSION)--unpackaged.sql: Makefile | sql
 sql:
 	mkdir -p $@
 
-#strip BEGIN/COMMIT since these are not allowed in extensions
+# Strip BEGIN/COMMIT since these are not allowed in extensions, and the trailing
+# ANALYZE with them.  In spatial_ref_sys.sql that ANALYZE deliberately sits *after*
+# COMMIT, so run standalone it refreshes statistics outside any transaction.  Removing
+# only the COMMIT moves it inside the extension's transaction, where it takes
+# ShareUpdateExclusiveLock on spatial_ref_sys and can deadlock against autovacuum
+# doing the same work:
+#
+#   ERROR:  deadlock detected
+#   CONTEXT:  SQL statement "ANALYZE "spatial_ref_sys""
+#             extension script file "postgis--ANY--<version>.sql"
+#
+# That aborts ALTER EXTENSION postgis UPDATE on any busy database.  Autovacuum will
+# analyse the table on its own schedule, so the extension script does not need to.
 sql/spatial_ref_sys.sql: ../../spatial_ref_sys.sql | sql
-	$(PERL) -pe 's/BEGIN\;//g ; s/COMMIT\;//g' $< > $@
+	$(PERL) -pe 's/BEGIN\;//g ; s/COMMIT\;//g ; s/^ANALYZE "spatial_ref_sys";\s*$$//g' $< > $@
 
 sql/spatial_ref_sys_config_dump.sql: ../../spatial_ref_sys.sql ../../utils/create_spatial_ref_sys_config_dump.pl | sql
 	$(PERL) @top_srcdir@/utils/create_spatial_ref_sys_config_dump.pl $< > $@

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

Summary of changes:
 NEWS                           |  2 ++
 extensions/postgis/Makefile.in | 16 ++++++++++++++--
 2 files changed, 16 insertions(+), 2 deletions(-)


hooks/post-receive
-- 
PostGIS