[postgis-tickets] [PostGIS] #4828: geometry_columns view breaks when SRID constraint marked NOT VALID

[PostGIS]

#4828: geometry_columns view breaks when SRID constraint marked NOT VALID
---------------------+---------------------
 Reporter:  til      |      Owner:  pramsey
     Type:  defect   |     Status:  new
 Priority:  medium   |  Milestone:
Component:  postgis  |    Version:  2.5.x
 Keywords:           |
---------------------+---------------------
 When relying on the type inference fallback mechanism of the
 geometry_columns view by defining a geometry column without explicit
 geometry type, and then adding a constraint on SRID which is marked as NOT
 VALID, the view throws an error.

 To reproduce:

 {{{
 repro=# select postgis_full_version();
 postgis_full_version
 --------------------------------------------------------------------------------------------------------------------------------------------------------------------
  POSTGIS="3.0.2 2fb2a18" [EXTENSION] PGSQL="120" GEOS="3.8.0-CAPI-1.13.1 "
 PROJ="6.3.2" LIBXML="2.9.10" LIBJSON="0.15" LIBPROTOBUF="1.3.3"
 WAGYU="0.4.3 (Internal)"
 (1 row)

 repro=# create table test (geom geometry);
 CREATE TABLE
 repro=# ALTER TABLE test ADD CONSTRAINT test_srid CHECK
 (St_SRID(geom)=4326) NOT VALID;
 ALTER TABLE

 repro=# SELECT * FROM geometry_columns;
 ERROR:  invalid input syntax for type integer: "4326 NOT VALID"

 repro=# ALTER TABLE test VALIDATE CONSTRAINT test_srid;
 ALTER TABLE
 repro=# SELECT * FROM geometry_columns;
 -[ RECORD 1 ]-----+---------
 f_table_catalog   | repro
 f_table_schema    | public
 f_table_name      | test
 f_geometry_column | geom
 coord_dimension   | 2
 srid              | 4326
 type              | GEOMETRY
 }}}

 I have postgis 3.0.2 locally, but I've seen the error also occur on 2.5.2.

 Attached is a patch that fixes it, admittedly by making the code even more
 verbose. If you think it's worth it, I can try to change it to use
 regexp_replace or regexp_matches, but I would need some hints up to which
 postgresql versions need to be supported.

-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/4828>
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.