Horton

Thread

Commits

  1. Don't use_physical_tlist for an IOS with non-returnable columns.

  1. ERROR: XX000: variable not found in subplan target list

    Ryan Kelly <rpkelly22@gmail.com> — 2022-02-11T03:21:56Z

    Hello,

After upgrading to 11.15 we have queries failing as follows:
(postgres@[local]:5432 02:52:54) [capitalrx_test_adjudication_master]>
EXPLAIN SELECT count(*) FROM adjudication.claims;
ERROR:  XX000: variable not found in subplan target list
LOCATION:  fix_upper_expr_mutator, setrefs.c:2427
Time: 0.697 ms
(postgres@[local]:5432 02:53:00) [capitalrx_test_adjudication_master]>
EXPLAIN SELECT count(1) FROM adjudication.claims;
ERROR:  XX000: variable not found in subplan target list
LOCATION:  fix_upper_expr_mutator, setrefs.c:2427
Time: 0.541 ms
(postgres@[local]:5432 02:53:03) [capitalrx_test_adjudication_master]>
EXPLAIN SELECT count(null) FROM adjudication.claims;
ERROR:  XX000: variable not found in subplan target list
LOCATION:  fix_upper_expr_mutator, setrefs.c:2427
Time: 0.890 ms
(postgres@[local]:5432 02:53:06) [capitalrx_test_adjudication_master]>
EXPLAIN SELECT count(id) FROM adjudication.claims;
                                     QUERY PLAN

-------------------------------------------------------------------------------------
 Aggregate  (cost=8.14..8.15 rows=1 width=8)
   ->  Index Only Scan using claims_pkey on claims  (cost=0.12..8.14 rows=1
width=8)
(2 rows)

Time: 1.423 ms

Thanks,
-Ryan Kelly

  2. Re: ERROR: XX000: variable not found in subplan target list

    Tom Lane <tgl@sss.pgh.pa.us> — 2022-02-11T04:03:21Z

    Ryan Kelly <rpkelly22@gmail.com> writes:
> After upgrading to 11.15 we have queries failing as follows:
> (postgres@[local]:5432 02:52:54) [capitalrx_test_adjudication_master]>
> EXPLAIN SELECT count(*) FROM adjudication.claims;
> ERROR:  XX000: variable not found in subplan target list

It's impossible to comment on this with this amount of detail.
Please try to extract a self-contained test case.

https://wiki.postgresql.org/wiki/Guide_to_reporting_problems

			regards, tom lane

  3. Re: ERROR: XX000: variable not found in subplan target list

    Ryan Kelly <rpkelly22@gmail.com> — 2022-02-11T16:34:14Z

    Sorry about that. Couldn't seem to get a simple reproduction. I have now
attached a straightforward one. I am not sure why so many columns are
required to cause the error, so it'd be nice to understand that too.

-Ryan

On Thu, Feb 10, 2022 at 11:03 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Ryan Kelly <rpkelly22@gmail.com> writes:
> > After upgrading to 11.15 we have queries failing as follows:
> > (postgres@[local]:5432 02:52:54) [capitalrx_test_adjudication_master]>
> > EXPLAIN SELECT count(*) FROM adjudication.claims;
> > ERROR:  XX000: variable not found in subplan target list
>
> It's impossible to comment on this with this amount of detail.
> Please try to extract a self-contained test case.
>
> https://wiki.postgresql.org/wiki/Guide_to_reporting_problems
>
>                         regards, tom lane
>

  4. Re: ERROR: XX000: variable not found in subplan target list

    Tom Lane <tgl@sss.pgh.pa.us> — 2022-02-11T19:28:12Z

    Ryan Kelly <rpkelly22@gmail.com> writes:
> Sorry about that. Couldn't seem to get a simple reproduction. I have now
> attached a straightforward one. I am not sure why so many columns are
> required to cause the error, so it'd be nice to understand that too.

Got it, thanks for the test case.

(For the archives' sake: this example doesn't fail for me with default
planner settings.  I got it to fail after adding
	set enable_seqscan to 0;
	set enable_bitmapscan to 0;
Probably some less heavy-handed manipulation of the planner's cost
constants would do the trick too, but I didn't try.)

The problem occurs when the planner decides that an index-only scan
on the idx_claims_first_name_upper_trgm index is the thing to do,
because it generates an invalid plan in that case.  Said plan used to
accidentally work before we tightened up what setrefs.c would allow,
which is how come you didn't see the failure before.  The normal
case is probably to do an IOS on the claims_pkey index instead,
which'll work fine --- it's the fact that the gist_trgm_ops opclass
doesn't support returning column values that creates the issue.
But with an empty or near-empty table, the two index plans have
exactly the same estimated cost, so it's luck of the draw which
one you get.  (I think it'll be the index with highest OID that gets
chosen in such cases, but that's an implementation artifact not
something to rely on.)  I think the apparent dependency on number
of columns is a red herring.  Possibly, depending on what cost
settings you are using, that could affect the relative cost
estimates for seqscan vs. index scan.

Anyway, the attached seems to be enough to fix it in HEAD.
It should apply more or less easily to v11 as well.

			regards, tom lane

  5. Re: ERROR: XX000: variable not found in subplan target list

    x4mmm@yandex-team.ru — 2022-04-14T10:24:36Z

    
> 12 февр. 2022 г., в 00:28, Tom Lane <tgl@sss.pgh.pa.us> написал(а):
> 
> Anyway, the attached seems to be enough to fix it in HEAD.
> It should apply more or less easily to v11 as well.

FWIW I've just observed reproduction on 10.10. Applied the patch and tested that query works correctly afterwards.

Do we need to check something else for this fix to be in next minor release?

Thanks!

Best regards, Andrey Borodin.

  6. Re: ERROR: XX000: variable not found in subplan target list

    x4mmm@yandex-team.ru — 2022-04-14T10:31:00Z

    14 апр. 2022 г., в 15:24, Andrey Borodin <x4mmm@yandex-team.ru> написал(а):
> 
> FWIW I've just observed reproduction on 10.10.

Oh, sorry for the noise. Surely it was 10.20.

Best regards, Andrey Borodin.

  7. Re: ERROR: XX000: variable not found in subplan target list

    Tom Lane <tgl@sss.pgh.pa.us> — 2022-04-14T14:11:01Z

    Andrey Borodin <x4mmm@yandex-team.ru> writes:
> Do we need to check something else for this fix to be in next minor release?

Are you referring to commit e5691cc91?

			regards, tom lane

  8. Re: ERROR: XX000: variable not found in subplan target list

    x4mmm@yandex-team.ru — 2022-04-14T15:35:24Z

    > On 14 Apr 2022, at 19:11, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 
> Andrey Borodin <x4mmm@yandex-team.ru> writes:
>> Do we need to check something else for this fix to be in next minor release?
> 
> Are you referring to commit e5691cc91?

Uhm, yes, it's already there. I've skimmed log of REL_10_STABLE, but somehow haven't found it. And

> It should apply more or less easily to v11 as well.

ensured me to ask.
Checking again I see it's there.

Many thanks!

Best regards, Andrey Borodin.