Issue with st_estimatedextent function ??
Bo Victor Thomsen <[email protected]> Wed, 19 Nov 2025 18:21:36 +0100
| Newsgroups | gmane.comp.gis.postgis |
|---|---|
| Message-ID | <[email protected]> |
This is a multi-part message in MIME format.
--------------AM822p9aa6BAGoPnc6MmXz4K
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Hi list -
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:
/*
fdc_results.bygningskade_stormflod_rcp85_2100_t100_ssp3_7_0_20251119164
*/
(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:
/*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)
the error disappears.
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??
(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)
PostgreSQL version: "PostgreSQL 17.4 on x86_64-windows, compiled by
msvc-19.42.34436, 64-bit"
PostGIS version: "3.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1"
--
Med venlig hilsen / Best regards
Bo Victor Thomsen
--------------AM822p9aa6BAGoPnc6MmXz4K
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Hi list - </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>
<i><b><br>
fdc_results.bygningskade_stormflod_rcp85_2100_t100_ssp3_7_0_20251119164 <br>
</b></i><br>
(Schema-name length: 11, table-name length: 59)</p>
<p>I have tracked the issue using the log facility in QGIS. It
shows, that QGIS intermittently executes a SQL command: </p>
<p><i><b>SELECT
st_estimatedextent('fdc_results','bygningskade_stormflod_rcp85_2100_t100_ssp3_7_0_20251119164','geom')</b></i></p>
<p>which results in an error: <b><span
style="color: rgb(34, 34, 34); font-family: "Source Code Pro", SFMono-Regular, Menlo, Monaco, Consolas, "Liberation Mono", "Courier New", monospace; font-size: 12px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; letter-spacing: 0.14994px; text-align: start; text-indent: 0px; text-transform: none; word-spacing: 0px; -webkit-text-stroke-width: 0px; white-space: pre-wrap; background-color: rgb(255, 255, 255); text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial; display: inline !important; float: none;">ERROR: invalid name syntax</span></b></p>
<p>If I change the table name to:</p>
<p><i><b>fdc_results.bygningskade_stormflod_rcp85_2100_t100_ssp3_7_0
</b></i>(Schema-name length: 11, table-name length: 47) </p>
<p>the error disappears. </p>
<p>I've done some testing and could replicate the error (and
non-error) situation in PGAdmin</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:</p>
<p>"BOX(716701.25 6167151,720533.9375 6174220)"</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 <i><b></b></i>PostGIS st_estimatedextent<i><b>
</b></i> function?? </p>
<p>(It might be a coincidence but if you express the tablename like
this : <i><b>"</b></i><i><b>fdc_results"."bygningskade_stormflod_rcp85_2100_t100_ssp3_7_0</b></i><i><b>", </b></i>the
total length is 63) </p>
<p>PostgreSQL version: "PostgreSQL 17.4 on x86_64-windows, compiled
by msvc-19.42.34436, 64-bit"</p>
<p>PostGIS version: "3.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1"</p>
<pre class="moz-signature" cols="72">--
Med venlig hilsen / Best regards
Bo Victor Thomsen</pre>
</body>
</html>
--------------AM822p9aa6BAGoPnc6MmXz4K--