[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
Resolution:                |   Keywords:  upgrade extensions regexp_replace
---------------------------+-----------------------------------------------
Comment (by robe):

 Looking at this closer, I think it might be the
 standard_conforming_strings setting at fault here.

 I can replicate the issue with the following:


 {{{
 SET standard_conforming_strings = 'off';

 SELECT pg_catalog.regexp_replace(rec.proc::text,
 '_deprecated_by_postgis[^(]*\(.*', '' )
 FROM (SELECT oid, oid::regprocedure AS proc FROM pg_proc) AS rec;
 }}}

 Yields error:


 {{{
 WARNING:  nonstandard use of escape in a string literal

 ERROR:  invalid regular expression: parentheses () not balanced

 SQL state: 2201B
 }}}



 By default, since PostgreSQL I think 9.0, standard_conforming_strings
 setting is set to on.

 Can you do the following on your 13.9


 {{{
 SHOW standard_conforming_strings;
 }}}

 I suspect it's off on your 13.9

 At anyrate we should rewrite our check to work whether or not
 standard_conforming_strings setting is on or off.
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5633#comment:1>
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.