RE: Issue with st_estimatedextent function ??
"Regina Obe" <[email protected]> Wed, 19 Nov 2025 14:43:56 -0500
| Newsgroups | gmane.comp.gis.postgis |
|---|---|
| Message-ID | <[email protected]> |
This is a multipart message in MIME format. ------=_NextPart_000_0008_01DC5962.EF4E5EB0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Bo, =20 FWIW I=E2=80=99m in the middle of packaging windows PostGIS 3.6.1 Bundle = package ofr PG13-18. I should have that done this week. I=E2=80=99ll send an update to the mailing list once I have that ready. =20 I confirm that in PostGIS 3.6.1 I can=E2=80=99t replicate this error, = but I can in=20 =20 POSTGIS=3D"3.5.2 3.5.2" [EXTENSION] PGSQL=3D"150" = GEOS=3D"3.13.0-CAPI-1.19.0" PROJ=3D"8.2.1 ..=E2=80=9D =20 =20 From: Paul Ramsey via postgis-users <[email protected]>=20 Sent: Wednesday, November 19, 2025 1:36 PM To: Bo Victor Thomsen <[email protected]> Cc: [email protected] Subject: Re: Issue with st_estimatedextent function ?? =20 I cannot replicate this bug on my PostGIS 3.5.5dev environment, probably = because the issue is already fixed as of 3.5.3 =20 https://github.com/postgis/postgis/blob/stable-3.5/NEWS#L66 https://trac.osgeo.org/postgis/ticket/5819 =20 I think you probably need a maintenance upgrade. =20 ATB, P =20 On Wed, Nov 19, 2025 at 9:21=E2=80=AFAM Bo Victor Thomsen = <[email protected] <mailto:[email protected]> > = wrote: Hi list -=20 I'm using Postgres/Postgis as a data repository for QGIS - and it = normally just works.. However I'm getting errors with tables having long = names, for example:=20 fdc_results.bygningskade_stormflod_rcp85_2100_t100_ssp3_7_0_20251119164=20 (Schema-name length: 11, table-name length: 59) I have tracked the issue using the log facility in QGIS. It shows, that = QGIS intermittently executes a SQL command:=20 SELECT = st_estimatedextent('fdc_results','bygningskade_stormflod_rcp85_2100_t100_= ssp3_7_0_20251119164','geom') which results in an error: ERROR: invalid name syntax If I change the table name to: fdc_results.bygningskade_stormflod_rcp85_2100_t100_ssp3_7_0 = (Schema-name length: 11, table-name length: 47)=20 the error disappears.=20 I've done some testing and could replicate the error (and non-error) = situation in PGAdmin Further testing shows that if the sum of the character lengths of the = schema- and table-name is larger than 58 the function will stop with an = error. Otherwise it returns a result like: "BOX(716701.25 6167151,720533.9375 6174220)" It's my understanding, that the maximum length of a tablename in = PostgreSQL is 63. This same limit applies for every type of identifier = in Postgres. Am I wrong to assume this? Or is there an issue with name = lengths in the PostGIS st_estimatedextent function??=20 (It might be a coincidence but if you express the tablename like this : = "fdc_results"."bygningskade_stormflod_rcp85_2100_t100_ssp3_7_0", the = total length is 63)=20 PostgreSQL version: "PostgreSQL 17.4 on x86_64-windows, compiled by = msvc-19.42.34436, 64-bit" PostGIS version: "3.5 USE_GEOS=3D1 USE_PROJ=3D1 USE_STATS=3D1" --=20 Med venlig hilsen / Best regards =20 Bo Victor Thomsen ------=_NextPart_000_0008_01DC5962.EF4E5EB0 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable <html xmlns:o=3D"urn:schemas-microsoft-com:office:office" = xmlns:w=3D"urn:schemas-microsoft-com:office:word" = xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" = xmlns=3D"http://www.w3.org/TR/REC-html40"><head><meta = http-equiv=3DContent-Type content=3D"text/html; charset=3Dutf-8"><meta = name=3DGenerator content=3D"Microsoft Word 15 (filtered = medium)"><style><!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;} @font-face {font-family:Aptos;} @font-face {font-family:Consolas; panose-1:2 11 6 9 2 2 4 3 2 4;} @font-face {font-family:"Source Code Pro";} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0in; font-size:12.0pt; font-family:"Aptos",sans-serif;} a:link, span.MsoHyperlink {mso-style-priority:99; color:blue; text-decoration:underline;} pre {mso-style-priority:99; mso-style-link:"HTML Preformatted Char"; margin:0in; font-size:10.0pt; font-family:"Courier New";} span.HTMLPreformattedChar {mso-style-name:"HTML Preformatted Char"; mso-style-priority:99; mso-style-link:"HTML Preformatted"; font-family:"Consolas",serif; mso-ligatures:none;} span.EmailStyle21 {mso-style-type:personal-reply; font-family:"Aptos",sans-serif; color:windowtext;} .MsoChpDefault {mso-style-type:export-only;} @page WordSection1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in;} div.WordSection1 {page:WordSection1;} --></style></head><body lang=3DEN-US link=3Dblue vlink=3Dpurple = style=3D'word-wrap:break-word'><div class=3DWordSection1><p = class=3DMsoNormal>Bo,<o:p></o:p></p><p = class=3DMsoNormal><o:p> </o:p></p><p class=3DMsoNormal>FWIW = I=E2=80=99m in the middle of packaging windows PostGIS 3.6.1 Bundle = package ofr PG13-18.=C2=A0 I should have that done this = week.<o:p></o:p></p><p class=3DMsoNormal>I=E2=80=99ll send an update to = the mailing list once I have that ready.<o:p></o:p></p><p = class=3DMsoNormal><o:p> </o:p></p><p class=3DMsoNormal>I confirm = that in PostGIS 3.6.1 I can=E2=80=99t replicate this error, but I can in = <o:p></o:p></p><p class=3DMsoNormal><o:p> </o:p></p><p = class=3DMsoNormal>POSTGIS=3D"3.5.2 3.5.2" [EXTENSION] = PGSQL=3D"150" GEOS=3D"3.13.0-CAPI-1.19.0" = PROJ=3D"8.2.1 ..=E2=80=9D<o:p></o:p></p><p = class=3DMsoNormal><o:p> </o:p></p><p = class=3DMsoNormal><o:p> </o:p></p><div = style=3D'border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in = 4.0pt'><div><div style=3D'border:none;border-top:solid #E1E1E1 = 1.0pt;padding:3.0pt 0in 0in 0in'><p class=3DMsoNormal><b><span = style=3D'font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span><= /b><span style=3D'font-size:11.0pt;font-family:"Calibri",sans-serif'> = Paul Ramsey via postgis-users <[email protected]> = <br><b>Sent:</b> Wednesday, November 19, 2025 1:36 PM<br><b>To:</b> Bo = Victor Thomsen <[email protected]><br><b>Cc:</b> = [email protected]<br><b>Subject:</b> Re: Issue with = st_estimatedextent function ??<o:p></o:p></span></p></div></div><p = class=3DMsoNormal><o:p> </o:p></p><div><p class=3DMsoNormal>I = cannot replicate this bug on my PostGIS 3.5.5dev environment, probably = because the issue is already fixed as of 3.5.3<o:p></o:p></p><div><p = class=3DMsoNormal><o:p> </o:p></p></div><div><p = class=3DMsoNormal><a = href=3D"https://github.com/postgis/postgis/blob/stable-3.5/NEWS#L66">http= s://github.com/postgis/postgis/blob/stable-3.5/NEWS#L66</a><o:p></o:p></p= ></div><div><p class=3DMsoNormal><a = href=3D"https://trac.osgeo.org/postgis/ticket/5819">https://trac.osgeo.or= g/postgis/ticket/5819</a><o:p></o:p></p></div><div><p = class=3DMsoNormal><o:p> </o:p></p></div><div><p class=3DMsoNormal>I = think you probably need a maintenance = upgrade.<o:p></o:p></p></div><div><p = class=3DMsoNormal><o:p> </o:p></p></div><div><p = class=3DMsoNormal>ATB,<o:p></o:p></p></div><div><p = class=3DMsoNormal>P<o:p></o:p></p></div></div><p = class=3DMsoNormal><o:p> </o:p></p><div><div><p class=3DMsoNormal>On = Wed, Nov 19, 2025 at 9:21<span = style=3D'font-family:"Arial",sans-serif'>=E2=80=AF</span>AM Bo Victor = Thomsen <<a = href=3D"mailto:[email protected]">[email protected]</= a>> wrote:<o:p></o:p></p></div><blockquote = style=3D'border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in = 6.0pt;margin-left:4.8pt;margin-right:0in'><div><p>Hi list = - <o:p></o:p></p><p>I'm using Postgres/Postgis as a data repository = for QGIS - and it normally just works.. However I'm getting errors with = tables having long names, for = example: <br><b><i><br>fdc_results.bygningskade_stormflod_rcp85_2100= _t100_ssp3_7_0_20251119164 <br></i></b><br>(Schema-name length: 11, = table-name length: 59)<o:p></o:p></p><p>I have tracked the issue using = the log facility in QGIS. It shows, that QGIS intermittently executes a = SQL command: <o:p></o:p></p><p><b><i>SELECT = st_estimatedextent('fdc_results','bygningskade_stormflod_rcp85_2100_t100_= ssp3_7_0_20251119164','geom')</i></b><o:p></o:p></p><p>which results in = an error: <b><span style=3D'font-size:9.0pt;font-family:"Source = Code Pro";color:#222222;letter-spacing:.1pt;background:white'>ERROR: = invalid name syntax</span></b><o:p></o:p></p><p>If I change the table = name = to:<o:p></o:p></p><p><b><i>fdc_results.bygningskade_stormflod_rcp85_2100_= t100_ssp3_7_0 </i></b>(Schema-name length: 11, table-name = length: 47) <o:p></o:p></p><p>the error = disappears. <o:p></o:p></p><p>I've done some testing and could = replicate the error (and non-error) situation in = PGAdmin<o:p></o:p></p><p>Further testing shows that if the sum of the = character lengths of the schema- and table-name is larger than 58 the = function will stop with an error. Otherwise it returns a result = like:<o:p></o:p></p><p>"BOX(716701.25 6167151,720533.9375 = 6174220)"<o:p></o:p></p><p>It's my understanding, that the maximum = length of a tablename in PostgreSQL is 63. This same limit applies for = every type of identifier in Postgres. Am I wrong to assume this? Or is = there an issue with name lengths in = the PostGIS st_estimatedextent<b><i> = </i></b> function?? <o:p></o:p></p><p>(It might be a = coincidence but if you express the tablename like this = : <b><i>"fdc_results"."bygningskade_stormflod_rcp85_2= 100_t100_ssp3_7_0", </i></b>the total length is = 63) <o:p></o:p></p><p>PostgreSQL version: "PostgreSQL 17.4 on = x86_64-windows, compiled by msvc-19.42.34436, = 64-bit"<o:p></o:p></p><p>PostGIS version: "3.5 USE_GEOS=3D1 = USE_PROJ=3D1 USE_STATS=3D1"<o:p></o:p></p><pre>-- = <o:p></o:p></pre><pre>Med venlig hilsen / Best = regards<o:p></o:p></pre><pre><o:p> </o:p></pre><pre>Bo Victor = Thomsen<o:p></o:p></pre></div></blockquote></div></div></div></body></htm= l> ------=_NextPart_000_0008_01DC5962.EF4E5EB0--