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]

FYI all, this ticket is completed and the next patch releases will include
the fix.

https://trac.osgeo.org/postgis/ticket/5978

Legacy tables that use constraints instead of typmod to encode metadata
will be supported again, but it would be nice if usage could be modernized.

P.

On Fri, Sep 5, 2025 at 12:39 PM Paul Ramsey <pramsey at cleverelephant.ca>
wrote:

> Looking more closely at the example it seems that the code creating the
> table is deliberately forcing PostGIS to use the ancient constraint
> behaviour, by setting the use_typmod value in AddGeometryColumn to 'false'.
> So this problem is going to be restricted to "only" all of the Esri user
> base.
>
> https://trac.osgeo.org/postgis/ticket/5978#comment:3
> https://postgis.net/docs/AddGeometryColumn.html
>
> So we *did* modernize our code, but for some reason Esri chose to keep the
> old scheme.
>
> P.
>
> On Fri, Sep 5, 2025 at 12:32 PM Paul Ramsey <pramsey at cleverelephant.ca>
> wrote:
>
>>
>>
>> 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/20250916/962ef09e/attachment.htm>