Re: BUG #17476: ERROR: "variable not found in subplan target list" when running SELECT COUNT(*)

po 9. 5. 2022 v 12:02 odesílatel Miloš Urbánek <milos(dot)urbanek(at)email(dot)cz>
napsal:

> No. But it would probably work as workaround aswell.
>

Diagnostics of every issue on Postgres should be started by ANALYZE. And
after any migration, restore, ... running ANALYZE is really part of best
practicies

Regards

Pavel

>
> --
> Milosh
>
> ---------- Původní e-mail ----------
> Od: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> Komu: Miloš Urbánek <milos(dot)urbanek(at)email(dot)cz>
> Datum: 9. 5. 2022 11:51:05
> Předmět: Re: BUG #17476: ERROR: "variable not found in subplan target
> list" when running SELECT COUNT(*)
>
>
>
> po 9. 5. 2022 v 10:52 odesílatel Miloš Urbánek <milos(dot)urbanek(at)email(dot)cz>
> napsal:
>
> The problem occurs in test database restored from PG 14 backup. There are
> 2 tables (out of 900 other) where this problem occurs (when running SELECT
> COUNT(*) on the table).
>
> I can not look at the query plan because EXPLAIN fails aswell, but my
> guess is that planner is trying to use index on the table to count the rows.
>
> There are only 10 records in the table. When I run VACUUM FULL, the
> problem disappears (planner is going to use Seq Scan which works).
>
>
> Did you run ANALYZE?
>
> Regads
>
> Pavel
>
>
>
> The database schema for both tables is complex, there are 20+ columns, 3+
> spatial data columns, 5 foreign keys, not such an easy task to create some
> test case for you.
>
> But my guess is that the root cause is planner trying to use index scan to
> count the rows.
>
> Therefore I hope that this is just another occurence of
> https://www.mail-archive.com/postgis-users(at)lists(dot)osgeo(dot)org/msg09410.html.
>
> Regards
>
> --
> Milosh
>
> ---------- Původní e-mail ----------
> Od: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> Komu: Miloš Urbánek <milos(dot)urbanek(at)email(dot)cz>
> Datum: 9. 5. 2022 10:22:16
> Předmět: Re: BUG #17476: ERROR: "variable not found in subplan target
> list" when running SELECT COUNT(*)
>
> Hi
>
> po 9. 5. 2022 v 9:22 odesílatel Miloš Urbánek <milos(dot)urbanek(at)email(dot)cz>
> napsal:
>
> I have 2 (ordinary) tables in 900+ table database restored from PG 9.6
> (with PostGIS geometries BTW) where this problem occurs.
> The count() queries into the other tables are OK.
> Also the problem happens only at the server but not at my Windows 10
> workstation with the same PG 14.2.
>
>
> Is there the same configuration on the server like on your workstation?
> The different configuration can enforce a different execution plan.
>
> Regards
>
> Pavel
>
>
> No clue what could be wrong.
>
> PGAdmin4 fails even when trying to list SQL creation statement.
>
> --
> Milos Urbanek
>
> ---------- Původní e-mail ----------
> Od: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Komu: milos(dot)urbanek(at)email(dot)cz
> Datum: 9. 5. 2022 2:22:10
> Předmět: Re: BUG #17476: ERROR: "variable not found in subplan target
> list" when running SELECT COUNT(*)
>
> PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> > Trying to run simple SELECT COUNT(*) from some table gives me the
> following
> > error (either running by hand or trying to run EXPLAIN using pgadmin or
> by
> > hand):
>
> > 2022-05-09 00:06:30.428 CEST [1196] ERROR: variable not found in subplan
> > target list
> > 2022-05-09 00:06:30.428 CEST [1196] STATEMENT: SELECT COUNT(*) FROM
> > "domino_efekty_havarijniprojev"
>
> Is that really just a table? I could believe a bug like this for
> some types of views, but it's a bit hard to credit for a plain table.
>
> In any case, we can't do much with this report unless you can provide
> a reproducer case --- preferably a SQL script that creates an object
> that triggers the problem.
>
> regards, tom lane
>
>