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 <[email protected]> wrote: > > > On Mon, Aug 25, 2025 at 12:37 PM Regina Obe <[email protected]> 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 <[email protected]> >> *Sent:* Monday, August 25, 2025 3:15 PM >> *To:* 'Michael Downey' <[email protected]>; '[email protected]' >> <[email protected]> >> *Cc:* 'Jonathan Shim' <[email protected]>; 'Ale Raza' <[email protected]>; >> 'Yung-Ting Chen' <[email protected]>; 'Neeta Reddy' <[email protected]>; >> 'Justin Muise' <[email protected]> >> *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 <[email protected]> >> *Sent:* Monday, August 25, 2025 1:11 PM >> *To:* [email protected] >> *Cc:* Jonathan Shim <[email protected]>; Ale Raza <[email protected]>; >> Yung-Ting Chen <[email protected]>; Neeta Reddy <[email protected]>; Justin >> Muise <[email protected]> >> *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 >> >> >> >
