[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
Resolution:             |   Keywords:
------------------------+---------------------
Description changed by jonochang:

Old description:

> 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"
>
> }}}

New description:

 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"

 }}}

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