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 <ma...@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 <ma...@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 > listpostgis-users@lists.osgeo.orghttps://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