<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Hi Imre,</p>
<p>Thanks very much for this information. I can wait, and will
restore the PostgreSQL 13 VM another time in the mean time to
continue development work. By the time 14.3 gets out, I'll have
another look at a potential PostgreSQL 14 upgrade.<br>
</p>
<p>Marco<br>
</p>
<div class="moz-cite-prefix">Op 13-2-2022 om 23:08 schreef Imre
Samu:<br>
</div>
<blockquote type="cite"
cite="mid:CAJnEWwm7U8nNnmZ3YhCUY08ztvaTddeStobn9+a8AX72w3qxsA@mail.gmail.com">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<div dir="ltr">Hi Marco,
<div><br>
<div>> Postgresql 14 bug?: "variable not found in subplan
target list"<br>
</div>
<div><br>
</div>
<div>This is similar to your issue:</div>
<div><a
href="https://www.postgresql.org/message-id/flat/4c347490-d734-5fdd-d613-1327601b4e7e%40mit.edu"
moz-do-not-send="true" class="moz-txt-link-freetext">https://www.postgresql.org/message-id/flat/4c347490-d734-5fdd-d613-1327601b4e7e%40mit.edu</a><br>
</div>
<blockquote style="margin:0 0 0 40px;border:none;padding:0px">
<div><i>"With yesterday’s release of PostgreSQL 11.15,
12.10, and 13.6 (presumably 10.20 and 14.2 as well),
Zulip’s test suite started failing</i></div>
<i>with <b>“variable not found in subplan target list”
errors</b> from PostgreSQL on a table that has a
PGroonga index. I found the following reproduction recipe
from a fresh database:"</i></blockquote>
<div><br>
</div>
<div>good news: probably it has been fixed; commit:
2022Feb11: </div>
<div> <a
href="https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=e5691cc9170bcd6c684715c2755d919c5a16fea2"
moz-do-not-send="true">https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=e5691cc9170bcd6c684715c2755d919c5a16fea2</a> </div>
<div>bad news: need more than 3 months to the next minor
release. ( May 12th, 2022. )<br>
</div>
<div><br>
</div>
<div>kind regards,</div>
<div> Imre</div>
<div><br>
<div><br>
</div>
</div>
</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, 22:32):<br>
</div>
<blockquote class="gmail_quote" style="margin:0px 0px 0px
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div>
<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>Op 13-2-2022 om 20:46 schreef Imre Samu:<br>
</div>
<blockquote type="cite">
<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/"
target="_blank" moz-do-not-send="true"
class="moz-txt-link-freetext">https://www.postgresql.org/docs/release/14.2/</a><br>
</div>
<blockquote style="margin:0px 0px 0px
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"
target="_blank" 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"
target="_blank" 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"
target="_blank" 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></fieldset>
<pre>_______________________________________________
postgis-users mailing list
<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>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" target="_blank" moz-do-not-send="true" class="moz-txt-link-freetext">https://lists.osgeo.org/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
</div>
_______________________________________________<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>