Bug in geometry_columns view

[jonochang]

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"

[jonochang]

example database schema

[jonochang]

example query