[PostGIS] #5682: Bug in geometry_columns view

[PostGIS]

#5682: Bug in geometry_columns view
-----------------------+---------------------
 Reporter:  jonochang  |      Owner:  pramsey
     Type:  defect     |     Status:  new
 Priority:  medium     |  Milestone:
Component:  postgis    |    Version:  3.4.x
 Keywords:             |
-----------------------+---------------------
 We have encountered a bug in the geometry_columns view where the subquery
 which returns ndims for a constraint assumes a certain format that does
 not work with contraints used by other libraries.

 See this section here:
 https://github.com/postgis/postgis/blob/8a41ae8f4bf7fe533f16bda1a998e61a460ed211/postgis/postgis.sql.in#L6422

 and an example constraint here in the attached test-schema.sql:

 {{{
   81   │     job_schema_version integer DEFAULT 1,
   82   │     CONSTRAINT valid_queues CHECK (((array_ndims(queues) = 1) AND
 (array_length(queues, 1) IS NOT NULL))),
   83   │     CONSTRAINT valid_worker_priorities CHECK
 (((array_ndims(worker_priorities) = 1) AND
 (array_length(worker_priorities, 1) IS NOT NULL)))
   84   │ );
 }}}


 An example output from the attached failing query test-query.sql is here:
 {{{
 postgis_full_version
 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  POSTGIS="3.4.1 ca035b9" [EXTENSION] PGSQL="160" GEOS="3.12.1-CAPI-1.18.1"
 PROJ="9.2.1 NETWORK_ENABLED=OFF URL_ENDPOINT=https://cdn.proj.org
 USER_WRITABLE_DIRECTORY=/Users/jonochang/Library/Application Support/proj
 DATABASE_PATH=/Applications/Postgres.app/Contents/Versions/16/share/proj/proj.db"
 LIBXML="2.11.7" LIBJSON="0.17" LIBPROTOBUF="1.4.1" WAGYU="0.5.0
 (Internal)"
 (1 row)

                                                               version
 ------------------------------------------------------------------------------------------------------------------------------------
  PostgreSQL 16.2 (Postgres.app) on aarch64-apple-darwin21.6.0, compiled by
 Apple clang version 14.0.0 (clang-1400.0.29.102), 64-bit
 (1 row)

 ERROR:  invalid input syntax for type integer: "1 AND
 (array_length(queues, 1 IS NOT NULL"
 ❯ psql -h localhost postgis_error_simple < test-query.sql
 ❯ vim test-query.sql
 ❯ psql -a -h localhost postgis_error_simple < test-query.sql
 SELECT postgis_full_version();
 postgis_full_version
 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  POSTGIS="3.4.1 ca035b9" [EXTENSION] PGSQL="160" GEOS="3.12.1-CAPI-1.18.1"
 PROJ="9.2.1 NETWORK_ENABLED=OFF URL_ENDPOINT=https://cdn.proj.org
 USER_WRITABLE_DIRECTORY=/Users/jonochang/Library/Application Support/proj
 DATABASE_PATH=/Applications/Postgres.app/Contents/Versions/16/share/proj/proj.db"
 LIBXML="2.11.7" LIBJSON="0.17" LIBPROTOBUF="1.4.1" WAGYU="0.5.0
 (Internal)"
 (1 row)

 SELECT version();
                                                               version
 ------------------------------------------------------------------------------------------------------------------------------------
  PostgreSQL 16.2 (Postgres.app) on aarch64-apple-darwin21.6.0, compiled by
 Apple clang version 14.0.0 (clang-1400.0.29.102), 64-bit
 (1 row)

 select * from geometry_columns WHERE f_table_name = 'items';
 ERROR:  invalid input syntax for type integer: "1 AND
 (array_length(queues, 1 IS NOT NULL"

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