Difference in behavior of AddGeometryColumn change in behavior between 3.5.2 and 3.5.3? SRID is listed as unknown when querying Geometry_Columns instead of the specified SRID

[Paul Ramsey]

On Mon, Aug 25, 2025 at 12:37 PM Regina Obe <lr at pcorp.us> wrote:

> On further inspection the 3.5.3  change  did not remove constraint
> support.  That was removed in upcoming 3.6.0.  What was changed was the
> regex logic in it which should have resulted in the same answers.
>

Not actually the regex logic, as that wasn't implemented, what was
implemented was a complete removal of expectation that column type and srid
would be stored in constraints. I see no fix except to return the
constraint logic and make sure our functions don't make those constraints
anymore. And then wait *another* 15 years for people to update their
tables, since if Esri still has tables like this being generated by EGDB
scripts *as we speak* we cannot drop support for them for a very long time.

Our fault for not updating the AddGeometryColumn functions 15 years ago
when typmod was introduced. I wonder if there is an email thread explaining
why.

P.


>
>
> I confirm this isn’t working so is a bug in the new regex logic of 3.5.3
> and have ticketed here:
>
>
>
> https://trac.osgeo.org/postgis/ticket/5978
>
>
>
>
>
>
>
>
>
> *From:* Regina Obe <lr at pcorp.us>
> *Sent:* Monday, August 25, 2025 3:15 PM
> *To:* 'Michael Downey' <mdowney at esri.com>; 'postgis-users at lists.osgeo.org'
> <postgis-users at lists.osgeo.org>
> *Cc:* 'Jonathan Shim' <JShim at esri.com>; 'Ale Raza' <araza at esri.com>;
> 'Yung-Ting Chen' <ychen at esri.com>; 'Neeta Reddy' <nreddy at esri.com>;
> 'Justin Muise' <JMuise at esri.com>
> *Subject:* RE: Difference in behavior of AddGeometryColumn change in
> behavior between 3.5.2 and 3.5.3? SRID is listed as unknown when querying
> Geometry_Columns instead of the specified SRID
>
>
>
> We did remove constraint support in geometry_columns where and since you
> are using constraints that would do it.
>
>
>
> Though I thought we did that in upcoming PostGIS 3.6.0 not PostGIS 3.5.3,
> but I guess I was mistaken.  It was done in 3.5.3.
>
>
>
> https://trac.osgeo.org/postgis/ticket/5829
>
>
>
> Any reason you can’t switch to typmod.  The constraint logic has a number
> of issues, it is slower to parse than typmod and has caused people issues
> over the years as it traps other constraints  causing issues as you can see
> in the above ticket.
>
>
>
> The workaround for this if you need the old behavior is to use the
> geometry_columns view definition from 3.5.2
>
>
>
> Thanks,
>
> Regina
>
>
>
> *From:* Michael Downey via postgis-users <postgis-users at lists.osgeo.org>
> *Sent:* Monday, August 25, 2025 1:11 PM
> *To:* postgis-users at lists.osgeo.org
> *Cc:* Jonathan Shim <JShim at esri.com>; Ale Raza <araza at esri.com>;
> Yung-Ting Chen <ychen at esri.com>; Neeta Reddy <nreddy at esri.com>; Justin
> Muise <JMuise at esri.com>
> *Subject:* Difference in behavior of AddGeometryColumn change in behavior
> between 3.5.2 and 3.5.3? SRID is listed as unknown when querying
> Geometry_Columns instead of the specified SRID
>
>
>
>
>
> Windows 17.6, 16.10,
>
> PostGIS 3.5.3
>
> For some reason I did not see this when I started testing 3.5.3 a couple
> of months ago. After I removed the earlier 3.5.2 and reinstalled 3.5.3, saw
> the query return an unknonw SRID instead of the stated one.
>
>
>
> DROP TABLE map.t1;
>
>
>
> CREATE TABLE map.t1 (
>
>   OBJECTID SERIAL NOT NULL,
>
>   PKEY     INTEGER,
>
>   PRIMARY KEY ( OBJECTID )  );
>
>
>
> SELECT public.AddGeometryColumn ('map', 't1', 'shape',  4326,
> upper('POINT'), 2, false);
>
>
>
>              addgeometrycolumn
>
> -------------------------------------------
>
> map.t1.shape SRID:4326 TYPE:POINT DIMS:2
>
>
>
>
>
> SELECT f_table_schema AS schema, f_table_name AS table, f_geometry_column
> AS column, coord_dimension AS Dimension, srid FROM public.geometry_columns;
>
>
>
> *PostGIS 3.5.2*
>
> schema |         table          | column | dimension | srid
>
> --------+------------------------+--------+-----------+------
>
> map    | t1                     | shape  |         2 | *4326*
>
>
>
> *PostGIS 3.5.3*
>
> schema |         table          | column | dimension | srid
>
> --------+------------------------+--------+-----------+------
>
> map    | t1                     | shape  |         2 | *   0*
>
>
>
>
>
> But describe table looks fine:
>
> \d t1
>
>                                   Table "map.t1"
>
>   Column  |   Type   | Collation | Nullable |               Default
>
>
> ----------+----------+-----------+----------+--------------------------------------
>
> objectid | integer  |           | not null |
> nextval('t1_objectid_seq'::regclass)
>
> pkey     | integer  |           |          |
>
> shape    | geometry |           |          |
>
> Indexes:
>
>     "t1_pkey" PRIMARY KEY, btree (objectid)
>
> Check constraints:
>
>     "enforce_dims_shape" CHECK (st_ndims(shape) = 2)
>
>     "enforce_geotype_shape" CHECK (geometrytype(shape) = 'POINT'::text OR
> shape IS NULL)
>
>     "enforce_srid_shape" CHECK (st_srid(shape) = 4326)
>
>
>
> Thanks,
>
> Michael
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20250905/750d2e68/attachment.htm>