<div dir="ltr">FYI all, this ticket is completed and the next patch releases will include the fix.<div><br></div><div><a href="https://trac.osgeo.org/postgis/ticket/5978">https://trac.osgeo.org/postgis/ticket/5978</a><br></div><div><br></div><div>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.</div><div><br></div><div>P.</div></div><br><div class="gmail_quote gmail_quote_container"><div dir="ltr" class="gmail_attr">On Fri, Sep 5, 2025 at 12:39 PM Paul Ramsey <<a href="mailto:pramsey@cleverelephant.ca">pramsey@cleverelephant.ca</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-style:solid;border-left-color:rgb(204,204,204);padding-left:1ex"><div dir="ltr">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. <div><div><br></div><div><a href="https://trac.osgeo.org/postgis/ticket/5978#comment:3" target="_blank">https://trac.osgeo.org/postgis/ticket/5978#comment:3</a><br></div></div><div><a href="https://postgis.net/docs/AddGeometryColumn.html" target="_blank">https://postgis.net/docs/AddGeometryColumn.html</a><br></div><div><br></div><div>So we *did* modernize our code, but for some reason Esri chose to keep the old scheme.</div><div><br></div><div>P.</div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Fri, Sep 5, 2025 at 12:32 PM Paul Ramsey <<a href="mailto:pramsey@cleverelephant.ca" target="_blank">pramsey@cleverelephant.ca</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-style:solid;border-left-color:rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div dir="ltr"><br></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Mon, Aug 25, 2025 at 12:37 PM Regina Obe <<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-style:solid;border-left-color:rgb(204,204,204);padding-left:1ex"><div><div lang="EN-US"><div><p class="MsoNormal">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.</p></div></div></div></blockquote><div><br></div><div>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.</div><div><br></div><div>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.</div><div><br></div><div>P.</div><div> </div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-style:solid;border-left-color:rgb(204,204,204);padding-left:1ex"><div><div lang="EN-US"><div><p class="MsoNormal"><u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal">I confirm this isn’t working so is a bug in the new regex logic of 3.5.3 and have ticketed here:<u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal"><a href="https://trac.osgeo.org/postgis/ticket/5978" target="_blank">https://trac.osgeo.org/postgis/ticket/5978</a><u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal"><u></u> <u></u></p><div style="border-width:medium medium medium 1.5pt;border-style:none none none solid;border-color:currentcolor currentcolor currentcolor blue;padding:0in 0in 0in 4pt"><div><div style="border-width:1pt medium medium;border-style:solid none none;border-color:rgb(225,225,225) currentcolor currentcolor;padding:3pt 0in 0in"><p class="MsoNormal"><b><span style="font-size:11pt;font-family:Calibri,sans-serif">From:</span></b><span style="font-size:11pt;font-family:Calibri,sans-serif"> Regina Obe <<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>> <br><b>Sent:</b> Monday, August 25, 2025 3:15 PM<br><b>To:</b> 'Michael Downey' <<a href="mailto:mdowney@esri.com" target="_blank">mdowney@esri.com</a>>; '<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>' <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br><b>Cc:</b> 'Jonathan Shim' <<a href="mailto:JShim@esri.com" target="_blank">JShim@esri.com</a>>; 'Ale Raza' <<a href="mailto:araza@esri.com" target="_blank">araza@esri.com</a>>; 'Yung-Ting Chen' <<a href="mailto:ychen@esri.com" target="_blank">ychen@esri.com</a>>; 'Neeta Reddy' <<a href="mailto:nreddy@esri.com" target="_blank">nreddy@esri.com</a>>; 'Justin Muise' <<a href="mailto:JMuise@esri.com" target="_blank">JMuise@esri.com</a>><br><b>Subject:</b> 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 <u></u><u></u></span></p></div></div><p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal">We did remove constraint support in geometry_columns where and since you are using constraints that would do it.<u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal">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.<u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal"><a href="https://trac.osgeo.org/postgis/ticket/5829" target="_blank">https://trac.osgeo.org/postgis/ticket/5829</a><u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal">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.<u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal">The workaround for this if you need the old behavior is to use the geometry_columns view definition from 3.5.2<u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal">Thanks,<u></u><u></u></p><p class="MsoNormal">Regina<u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></p><div style="border-width:medium medium medium 1.5pt;border-style:none none none solid;border-color:currentcolor currentcolor currentcolor blue;padding:0in 0in 0in 4pt"><div><div style="border-width:1pt medium medium;border-style:solid none none;border-color:rgb(225,225,225) currentcolor currentcolor;padding:3pt 0in 0in"><p class="MsoNormal"><b><span style="font-size:11pt;font-family:Calibri,sans-serif">From:</span></b><span style="font-size:11pt;font-family:Calibri,sans-serif"> Michael Downey via postgis-users <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>> <br><b>Sent:</b> Monday, August 25, 2025 1:11 PM<br><b>To:</b> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br><b>Cc:</b> Jonathan Shim <<a href="mailto:JShim@esri.com" target="_blank">JShim@esri.com</a>>; Ale Raza <<a href="mailto:araza@esri.com" target="_blank">araza@esri.com</a>>; Yung-Ting Chen <<a href="mailto:ychen@esri.com" target="_blank">ychen@esri.com</a>>; Neeta Reddy <<a href="mailto:nreddy@esri.com" target="_blank">nreddy@esri.com</a>>; Justin Muise <<a href="mailto:JMuise@esri.com" target="_blank">JMuise@esri.com</a>><br><b>Subject:</b> 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 <u></u><u></u></span></p></div></div><p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Consolas"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif">Windows 17.6, 16.10, <u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif">PostGIS 3.5.3<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif">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.<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Consolas"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Consolas">DROP TABLE map.t1;<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Consolas"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Consolas">CREATE TABLE map.t1 (<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Consolas"> OBJECTID SERIAL NOT NULL,<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Consolas"> PKEY INTEGER,<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Consolas"> PRIMARY KEY ( OBJECTID ) );<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Consolas"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Consolas">SELECT public.AddGeometryColumn ('map', 't1', 'shape', 4326, upper('POINT'), 2, false);<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Consolas"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Consolas"> addgeometrycolumn<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Consolas">-------------------------------------------<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Consolas">map.t1.shape SRID:4326 TYPE:POINT DIMS:2<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Consolas"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Consolas"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Consolas">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;<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Consolas"><u></u> <u></u></span></p><p class="MsoNormal"><b><span style="font-size:11pt;font-family:Consolas;color:red">PostGIS 3.5.2<u></u><u></u></span></b></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Consolas">schema | table | column | dimension | srid<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Consolas">--------+------------------------+--------+-----------+------<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Consolas">map | t1 | shape | 2 | <b><span style="color:red">4326<u></u><u></u></span></b></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Consolas"><u></u> <u></u></span></p><p class="MsoNormal"><b><span style="font-size:11pt;font-family:Consolas;color:red">PostGIS 3.5.3<u></u><u></u></span></b></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Consolas">schema | table | column | dimension | srid<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Consolas">--------+------------------------+--------+-----------+------<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Consolas">map | t1 | shape | 2 | <b><span style="color:red"> 0</span></b><u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Consolas"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Consolas"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Consolas">But describe table looks fine: <u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Consolas">\d t1<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Consolas"> Table "map.t1"<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Consolas"> Column | Type | Collation | Nullable | Default<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Consolas">----------+----------+-----------+----------+--------------------------------------<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Consolas">objectid | integer | | not null | nextval('t1_objectid_seq'::regclass)<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Consolas">pkey | integer | | |<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Consolas">shape | geometry | | |<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Consolas">Indexes:<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Consolas"> "t1_pkey" PRIMARY KEY, btree (objectid)<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Consolas">Check constraints:<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Consolas"> "enforce_dims_shape" CHECK (st_ndims(shape) = 2)<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Consolas"> "enforce_geotype_shape" CHECK (geometrytype(shape) = 'POINT'::text OR shape IS NULL)<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Consolas"> "enforce_srid_shape" CHECK (st_srid(shape) = 4326)<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif">Thanks,<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif">Michael<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif"><u></u> <u></u></span></p></div></div></div></div></div></blockquote></div></div>
</blockquote></div>
</blockquote></div>