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:

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

[robe]

I have replicated the issue with your example.

Thanks

[robe]

Okay I tested this against 3.4.4dev and 3.5.1dev and I can't replicate the issue, so I think this issue mighh be fixed already.

Output in both cases is:

 f_table_catalog | f_table_schema | f_table_name | f_geometry_column | coord_dimension | srid |   type
-----------------+----------------+--------------+-------------------+-----------------+------+----------
 test_postgis    | public         | items        | location          |               2 |    0 | GEOMETRY
(1 row)

I'm guessing we fixed this when we had to redo geometry_columns because of the drop of a column, I forget which PostgreSQL version.

[robe]

Seems to work okay too in 3.3.8dev

[robe]

Ticket retargeted after milestone closed