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

[Marco Boeringa]

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