<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">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">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">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">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">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">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">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">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">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">postgis-users@lists.osgeo.org</a>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" target="_blank">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">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
</blockquote></div>