Opened 4 years ago

#4828 new defect

geometry_columns view breaks when SRID constraint marked NOT VALID

Reported by: til Owned by: pramsey
Priority: medium Milestone:
Component: postgis Version: 2.5.x -- EOL
Keywords: Cc:

Description

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.

Attachments (1)

0001-Fix-geometry_columns-srid-for-NOT-VALID-check-constr.patch (1.1 KB ) - added by til 4 years ago.

Download all attachments as: .zip

Change History (1)

Note: See TracTickets for help on using tickets.