[postgis-users] Postgresql 14 bug?: "variable not found in subplan target list"

[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

bad news:  need more than 3 months to the next minor release.  ( May 12th,
2022. )

kind regards,
 Imre



Marco Boeringa <marco at 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 at 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 at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>>
>
> _______________________________________________
> postgis-users mailing listpostgis-users at lists.osgeo.orghttps://lists.osgeo.org/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20220213/fe9fefc5/attachment.html>