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

[Marco Boeringa]

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