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

[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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20220213/10ef9374/attachment.html>