[postgis-users] Postgresql 14 bug?: "variable not found in subplan target list"
Marco Boeringa
marco at boeringa.demon.nl
Sun Feb 13 14:31:11 PST 2022
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 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 list
>> postgis-users at lists.osgeo.org
>> https://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
>
>
> _______________________________________________
> 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/e96d73c6/attachment.html>
More information about the postgis-users
mailing list