[PostGIS] #5633: postgis_extensions_upgrade() breaks and signalize unbalanced parenteses in regex of regexp_replace function

[PostGIS]

#5633: postgis_extensions_upgrade() breaks and signalize unbalanced parenteses in
regex of regexp_replace function
-----------------------------------------------+---------------------------
 Reporter:  hci                                |      Owner:  strk
     Type:  enhancement                        |     Status:  assigned
 Priority:  low                                |  Milestone:  PostGIS
                                               |  PostgreSQL
Component:  upgrade/soft                       |    Version:  3.3.x
 Keywords:  upgrade extensions regexp_replace  |
-----------------------------------------------+---------------------------
 After apt upgrade Postgis 3.1.1 to 3.3.1 by apt pgdg bullseye repo, when
 we trying to use postgis_extensions_upgrade(), the
 instruction ' ALTER EXTENSION UPGRADE postgis TO "3.3.1" ' was breaked by
 a regex expression.

 This issue occured for these versions:
 {{{PostgreSQL 13.9 (Debian 13.9-0+deb11u1) on x86_64-pc-linux-gnu,
 compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit}}}
 and
 {{{POSTGIS="3.3.1 3786b21" [EXTENSION] PGSQL="130"
 GEOS="3.9.0-CAPI-1.16.2" PROJ="7.2.1" GDAL="GDAL 3.2.2, released
 2021/03/05" LIBXML="2.9.10" LIBJSON="0.15" LIBPROTOBUF="1.3.3"
 WAGYU="0.5.0 (Internal)" (core procs from "3.1.1 aaf4c79" **need
 upgrade**) TOPOLOGY (topology procs from "3.1.1 aaf4c79" need upgrade)
 RASTER (raster procs from "3.1.1 aaf4c79" need upgrade)}}}
 Both installed from pgdg bullseye repo.
 Notice the messages 'need upgrade' indicating the need to run 'postgres
 upgrade version()' function.

 This issue not occured for PostgreSQL 13.13, suggesting that the
 regexp_replace function was changed in this most recent PostgreSQL
 version.

 We solved this issue for the PostgreSQL 13.9 by changing the regex
 expression at line 9869 of postgis--ANY--3.4.0.sql
 From:
 {{{
 pg_catalog.regexp_replace(rec.proc::text,
 '_deprecated_by_postgis[^(]*\(.*', '' );
 }}}

 To:
 {{{
 pg_catalog.regexp_replace(rec.proc::text,
 '_deprecated_by_postgis[^(]*\(.*)', '' );
 }}}
 Just by adding the ')' to regex {{{'_deprecated_by_postgis[^(]*\(.*)'}}}
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5633>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.