Sudden database error with COUNT(*) making Query Planner crashes: variable not found in subplan target list - Mailing list pgsql-hackers

Dear PostgreSQL Hacker Community,

I am facing a tricky bug which makes the Query Planner crashes when using COUNT(*) function.

Without any upgrade suddenly a table of a database instance could not be queried this way:

SELECT COUNT(*) FROM items;

-- ERROR:  variable not found in subplan target list

-- SQL state: XX000

Message and behaviour seem related to the Query Planner:

EXPLAIN SELECT COUNT(*) FROM item;

-- ERROR:  variable not found in subplan target list

-- SQL state: XX000

Looks like a column name could not be found (see https://github.com/postgres/postgres/blob/ce4f46fdc814eb1b704d81640f6d8f03625d0f53/src/backend/optimizer/plan/setrefs.c#L2967-L2972) in some specific context that is somehow hard to reproduce.

Interesting facts:

SELECT COUNT(id) FROM items;  -- 213

SELECT COUNT(*) FROM items WHERE id > 0; -- 213

Work as expected.

I can see that other people are recently facing a similar problem (https://www.postgresql.org/message-id/flat/4c347490-d734-5fdd-d613-1327601b4e7e%40mit.edu).

If it is the same bug then it is not related to the PGroonga extension as I don’t use it all.

Anyway, the bug is difficult to reproduce on my application.

At the time of writing, I could just isolate it on a specific database but I could not draw a MCVE from it.

I am looking for help to make it reproducible and feed your knowledge database.

My first guess was to open a post of SO (see for details https://stackoverflow.com/questions/72498741/how-can-i-reproduce-a-database-context-to-debug-a-tricky-postgresql-error-vari), but digging deeper in the investigation it seems it will require people with strong insights on how PostgreSQL actually works under the hood.

Therefore, I chose this specific mailing list.

The bug is tricky to reproduce, I could not succeed to replicate elsewhere (dump/restore does not preserve it).

Anyway it makes my database unusable and looks like a potential bug for your product and applications relying on it.

Faulty setup is about:

SELECT version();

-- PostgreSQL 13.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit

SELECT extname, extversion FROM pg_extension;

-- "plpgsql"    "1.0"

-- "postgis"    "3.1.1"

By now, the only workarounds I have found are:

• Dump database and recreate a new instance (problem seems to vanish but there is no guarantee it is solved or it will not happened later on);
• Add dummy filter on all queries (more a trick than a solution).

I am writing to this mailing list to raise you attention on it.

I’ll be happy to help you investigate it deeper.

Best regards,

Landercy Jean