<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Hi Imre,</p>
<p>Thanks. <br>
</p>
<p>However, since I use the VM with PostgreSQL for development
exclusively, and am not to bothered re-importing OpenStreetMap
data, I actually decided to first delete all contents from the
databases I use for testing, as this leads to near instantaneous
upgrade, instead of needing to wait for an extended amount of time
for the upgrade to finish.</p>
<p>I then re-imported the data, so I am sure the data and indexes
are "fresh". I even went as far as dropping one the databases that
showed the issue, but was easily able to reproduce the issue with
my current workflow. Unfortunately, that is a whole bunch of
Python code generating SQL, and isn't easily compacted into a
simple test case. So it is a bit of digging around at first.<br>
</p>
<p>That said, your reference to a possible issue with the indexes,
is a possible lead to explore more.</p>
<p>I run PostgreSQL 14.2 by the way.<br>
</p>
<p>Marco<br>
</p>
<div class="moz-cite-prefix">Op 13-2-2022 om 20:46 schreef Imre
Samu:<br>
</div>
<blockquote type="cite"
cite="mid:CAJnEWwnSjAQOo=Drz+cUf7wws0X+8xekiuvxWOr6WONU9ka4-Q@mail.gmail.com">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<div dir="ltr">> after upgrading to PostgreSQL 14?<br>
<div>> ... If I 'VACUUM (FULL)' the dataset, I get back the
record count without issues.<br>
</div>
<div>> ... Thoughts?<br>
</div>
<div><br>
</div>
<div>imho:</div>
<div>- Maybe the 'VACUUM (FULL)' is rebuilding your indexes -
and solving some corrupted index problem?<br>
</div>
<div>- As I see in 14.2 release notes: "reindexing is
recommended" <a
href="https://www.postgresql.org/docs/release/14.2/"
moz-do-not-send="true" class="moz-txt-link-freetext">https://www.postgresql.org/docs/release/14.2/</a><br>
</div>
<blockquote style="margin:0 0 0 40px;border:none;padding:0px">
<div><i>"However, some bugs have been found that may have
resulted in corrupted indexes, as explained in the first
two changelog entries. If any of those cases apply to you,
<b>it's recommended to reindex possibly-affected indexes
after updating.</b>"</i></div>
</blockquote>
<div> in 14.1 - this is also recommended: <a
href="https://www.postgresql.org/docs/14/release-14-1.html"
moz-do-not-send="true" class="moz-txt-link-freetext">https://www.postgresql.org/docs/14/release-14-1.html</a> </div>
<i> " it's recommended to reindex possibly-affected
indexes after updating." </i>
<div><br>
</div>
<div>I don't know your upgrade process, but in this case, I will
re-run the "reindexdb" utility </div>
<div> ( <a
href="https://www.postgresql.org/docs/14/app-reindexdb.html"
moz-do-not-send="true" class="moz-txt-link-freetext">https://www.postgresql.org/docs/14/app-reindexdb.html</a>
)</div>
<div><br>
</div>
<div>Regards,</div>
<div> Imre</div>
<div><br>
</div>
<div><br>
</div>
</div>
<br>
<div class="gmail_quote">
<div dir="ltr" class="gmail_attr">Marco Boeringa <<a
href="mailto:marco@boeringa.demon.nl" moz-do-not-send="true"
class="moz-txt-link-freetext">marco@boeringa.demon.nl</a>>
ezt írta (időpont: 2022. febr. 13., V, 18:35):<br>
</div>
<blockquote class="gmail_quote" style="margin:0px 0px 0px
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Hi,<br>
<br>
Anyone else running into issues with their process flow after
upgrading <br>
to PostgreSQL 14?<br>
<br>
After my failed attempt to update GEOS and PROJ, as I wrote
about in <br>
recent posts, I restored my backup VM with plain vanilla
PostgreSQL 13 <br>
and PostGIS 3.2.0 from the official apt repository of
PostgreSQL, which <br>
also includes GEOS 3.8.0 and PROJ 6.3.1.<br>
<br>
I then upgraded my cluster to PostgreSQL 14, without any other
changes <br>
or upgrades.<br>
<br>
Now I am running into a weird issue and error I have never
seen before <br>
in the past four years of working with PostgreSQL:<br>
<br>
- On a dataset that has undergone some basic processing in
PostgreSQL / <br>
PostGIS, including things like 'ST_SimplifyVW',
'ST_Transform', <br>
somewhere along the processing chain I get a "variable not
found in <br>
subplan target list" type error. This happens on a very basic
SQL <br>
statement, just:<br>
<br>
"SELECT COUNT(*) FROM <TABLE>"<br>
<br>
- If I view the data in pgAdmin or DBeaver, I can open the
table and <br>
view it spatially without issues. Running the same SQL COUNT
statement <br>
against the dataset from these tools query interfaces, does
give me the <br>
same error though as in my code.<br>
<br>
- If I 'VACUUM' the dataset, the error persists.<br>
<br>
- If I 'VACUUM (FULL)' the dataset, I get back the record
count without <br>
issues.<br>
<br>
Thoughts?<br>
<br>
Marco<br>
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank"
moz-do-not-send="true" class="moz-txt-link-freetext">postgis-users@lists.osgeo.org</a><br>
<a
href="https://lists.osgeo.org/mailman/listinfo/postgis-users"
rel="noreferrer" target="_blank" moz-do-not-send="true"
class="moz-txt-link-freetext">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
</blockquote>
</div>
<br>
<fieldset class="moz-mime-attachment-header"></fieldset>
<pre class="moz-quote-pre" wrap="">_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="https://lists.osgeo.org/mailman/listinfo/postgis-users">https://lists.osgeo.org/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
</body>
</html>