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

Ryan Kelly <rpkelly22(at)gmail(dot)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