[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] Postgresql 14 bug?: "variable not found in subplan target list"
From: Marco Boeringa <marco () boeringa ! demon ! nl>
Date: 2022-02-13 22:31:11
Message-ID: a7563b1a-a1c5-9a34-8763-126d6d92b75a () boeringa ! demon ! nl
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
Hi Imre,
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.
Marco
Op 13-2-2022 om 23:08 schreef Imre Samu:
> Hi Marco,
>
> > Postgresql 14 bug?: "variable not found in subplan target list"
>
> This is similar to your issue:
> https://www.postgresql.org/message-id/flat/4c347490-d734-5fdd-d613-1327601b4e7e%40mit.edu
>
> /"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/
> /with *“variable not found in subplan target list” errors* from
> PostgreSQL on a table that has a PGroonga index. I found the
> following reproduction recipe from a fresh database:"/
>
>
> good news: probably it has been fixed; commit: 2022Feb11:
> https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=e5691cc9170bcd6c684715c2755d919c5a16fea2 \
> <https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=e5691cc9170bcd6c684715c2755d919c5a16fea2> \
>
> bad news: need more than 3 months to the next minor release. ( May
> 12th, 2022. )
>
> kind regards,
> Imre
>
>
>
> Marco Boeringa <marco@boeringa.demon.nl> ezt írta (időpont: 2022.
> febr. 13., V, 22:32):
>
> Hi Imre,
>
> Thanks.
>
> 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.
>
> 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.
>
> That said, your reference to a possible issue with the indexes, is
> a possible lead to explore more.
>
> I run PostgreSQL 14.2 by the way.
>
> Marco
>
> Op 13-2-2022 om 20:46 schreef Imre Samu:
> > > after upgrading to PostgreSQL 14?
> > > ... If I 'VACUUM (FULL)' the dataset, I get back the record
> > count without issues.
> > > ... Thoughts?
> >
> > imho:
> > - Maybe the 'VACUUM (FULL)' is rebuilding your indexes - and
> > solving some corrupted index problem?
> > - As I see in 14.2 release notes: "reindexing is recommended"
> > https://www.postgresql.org/docs/release/14.2/
> >
> > /"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, *it's
> > recommended to reindex possibly-affected indexes after
> > updating.*"/
> >
> > in 14.1 - this is also recommended:
> > https://www.postgresql.org/docs/14/release-14-1.html
> > / " it's recommended to reindex possibly-affected
> > indexes after updating." /
> >
> > I don't know your upgrade process, but in this case, I will
> > re-run the "reindexdb" utility
> > ( https://www.postgresql.org/docs/14/app-reindexdb.html )
> >
> > Regards,
> > Imre
> >
> >
> >
> > Marco Boeringa <marco@boeringa.demon.nl> ezt írta (időpont: 2022.
> > febr. 13., V, 18:35):
> >
> > Hi,
> >
> > Anyone else running into issues with their process flow after
> > upgrading
> > to PostgreSQL 14?
> >
> > After my failed attempt to update GEOS and PROJ, as I wrote
> > about in
> > recent posts, I restored my backup VM with plain vanilla
> > PostgreSQL 13
> > and PostGIS 3.2.0 from the official apt repository of
> > PostgreSQL, which
> > also includes GEOS 3.8.0 and PROJ 6.3.1.
> >
> > I then upgraded my cluster to PostgreSQL 14, without any
> > other changes
> > or upgrades.
> >
> > Now I am running into a weird issue and error I have never
> > seen before
> > in the past four years of working with PostgreSQL:
> >
> > - On a dataset that has undergone some basic processing in
> > PostgreSQL /
> > PostGIS, including things like 'ST_SimplifyVW', 'ST_Transform',
> > somewhere along the processing chain I get a "variable not
> > found in
> > subplan target list" type error. This happens on a very basic
> > SQL
> > statement, just:
> >
> > "SELECT COUNT(*) FROM <TABLE>"
> >
> > - If I view the data in pgAdmin or DBeaver, I can open the
> > table and
> > view it spatially without issues. Running the same SQL COUNT
> > statement
> > against the dataset from these tools query interfaces, does
> > give me the
> > same error though as in my code.
> >
> > - If I 'VACUUM' the dataset, the error persists.
> >
> > - If I 'VACUUM (FULL)' the dataset, I get back the record
> > count without
> > issues.
> >
> > Thoughts?
> >
> > Marco
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users@lists.osgeo.org
> > https://lists.osgeo.org/mailman/listinfo/postgis-users
> >
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users@lists.osgeo.org
> > https://lists.osgeo.org/mailman/listinfo/postgis-users
> _______________________________________________
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
[Attachment #5 (text/html)]
<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>
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic