SELECT geometry_columns returns unexpected error when there are constraints after SRID constraint.

[nbvfgh]

DROP TABLE IF EXISTS test;
create table test (geom geometry);

ALTER TABLE test ADD CONSTRAINT c1 CHECK (ST_SRID(geom)=4326 and ST_IsValid(geom));

SELECT * FROM geometry_columns;

-- ERROR:  invalid type integer input syntax: "4326 AND st_isvalidgeom" 

If the order of the two constraints is reversed:

DROP TABLE IF EXISTS test;
create table test (geom geometry);

ALTER TABLE test ADD CONSTRAINT c1 CHECK (ST_IsValid(geom) and ST_SRID(geom)=4326);

SELECT * FROM geometry_columns;

-- normal result

[pramsey]

I come at this with two impulses:

• One is just to remove the constraint-reading bits of the view, since they date back to pre-2.0 postgis, when we didn't have typmods.
• The other is to replace the string manipulation stuff with a single regex.

# select pg_get_constraintdef(177252);

                 pg_get_constraintdef                  
-------------------------------------------------------
 CHECK (((st_srid(geom) = 4326) AND st_isvalid(geom)))

                                                                    ^
# select regexp_match(
           pg_get_constraintdef(177252), 
           'st_srid\(\w+\)\s*=\s*(\d+)', 'i');

 regexp_match 
--------------
 {4326}

That would be more reliable than the current code and leave in place the constraint support, for those 15 year old legacy systems we care about.

[robe]

Replying to pramsey:

I come at this with two impulses:

• One is just to remove the constraint-reading bits of the view, since they date back to pre-2.0 postgis, when we didn't have typmods.
• The other is to replace the string manipulation stuff with a single regex.

# select pg_get_constraintdef(177252);

                 pg_get_constraintdef                  
-------------------------------------------------------
 CHECK (((st_srid(geom) = 4326) AND st_isvalid(geom)))

                                                                    ^
# select regexp_match(
           pg_get_constraintdef(177252), 
           'st_srid\(\w+\)\s*=\s*(\d+)', 'i');

 regexp_match 
--------------
 {4326}

That would be more reliable than the current code and leave in place the constraint support, for those 15 year old legacy systems we care about.

I suppose removing it entirely would make the view faster. Though I don't like its a breaking change and hard to tell how many rely on that. I know for one my tiger geocoder still uses constraints, but I personally don't rely on that view for anything I don't think, not sure if there are any that do.

I'm not sure your regex is non-standard conforming.

[robe]

I think it's something that if enough people complain we can always put back so +0 to remove the constraint and see how many complain about it. Just make sure to mark it as a breaking change.

[Paul Ramsey <pramsey@…>]

In 9def18c/git:

geometry_columns view fails with non-standard use of postgis functions in constraint definition, references #5829

[Paul Ramsey <pramsey@…>]

In a6b036da/git:

geometry_columns view fails with non-standard use of postgis functions in constraint definition, references #5829

[Paul Ramsey <pramsey@…>]

In 102b938/git:

geometry_columns view fails with non-standard use of postgis functions in constraint definition, references #5829

[pramsey]

Master is updated to remove constraints as an input to geometry_columns, and stable branches updated to use regex approach to fix this bug.

[robe]

Replying to pramsey:

Master is updated to remove constraints as an input to geometry_columns, and stable branches updated to use regex approach to fix this bug.

[robe]

Failing non-standard conforming check — put an E in front should fix it - ​https://woodie.osgeo.org/repos/30/pipeline/2992/6

[robe]

I think this is all fixed now @pramsey right? I'm going to reclose and assume you pushed thru 3.3.