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]

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/20250905/f412a559/attachment.htm>