intermittent error: 'variable not found in subplan target list' - INCOMPLETE SUBMISSION

name: Damon Hart
email: dhcom(at)sundial(dot)com

System Configuration
---------------------
Architecture :

Intel PIII dual 800MHz, 1.5 GB RAM

Operating System :

Linux xs1.xd 2.4.22-1.2115.nptlsmp #1 SMP Wed Oct 29 15:30:09 EST 2003
i686 i686 i386 GNU/Linux
(Fedora Core 1)

PostgreSQL version : PostgreSQL-7.4.1

Compiler used : PDG RPMs

Description of problem:
-----------------------

My query (sample attached) fails intermittently with the backend
error 'variable not found in subplan target list.' The error is
reliably reproducible on specific datasets and intermittent on
others. An earlier post to the list, 3FCCE180(dot)2017E060(at)atichile(dot)com,
(2003-12-02) accessible at
http://www.spinics.net/lists/pgsql/msg03081.html suggests issues with
the planner. That list thread does not indicate a solution was found,
but that Tom Lane could not reproduce the error with an unpopulated
schema. His post suggests toggling enable_nestloop, enable_mergejoin,
and enable_hashjoin and in fact my error occurs only with
enable_nestloop ON.

I have isolated a reproducible example. As mentioned, it is data
dependent and the script queries do not result in the error if run
against empty tables. Sorry about the resulting length of this post.

Steps to reproduce:
-------------------

Attached are pg_dump output of a minimal database schema and data
excerpt as well as a short script which triggers the error. You may need
to run the excepts as 'postgres' or explicitly edit schema.sql or
otherwise give yourself required permissions to the resultant database.

$ createdb pgsql_bug
$ psql pgsql_bug -f schema.sql
$ psql pgsql_bug -f script.sql

The final statement in script.sql triggers the error in question:

psql:script.sql:63: ERROR: XX000: variable not found in subplan target list
LOCATION: replace_vars_with_subplan_refs_mutator, setrefs.c:685

ADDENDUM:
---------

I had to remove the attached schema.sql file as the list manager refused
the complete submission for overall message size (112KB). I tried to
reduce the size of the data, but ran into trouble reproducing the error.
With the original data or a smaller extract without running 'vacuum
analyze' (or a smaller extract and running 'vacuum analyze') the planner
takes a different path and the error is not triggered. I cannot divine
enough information to force the planner to take the error path, as I
can't use 'EXPLAIN' on this query (it fails in the same place.)

Please provide feedback with an off-list email address for the data
sample for reproduction.

thanks,

Damon Hart

Damon Hart <dhcom(at)sundial(dot)com> writes:
> I had to remove the attached schema.sql file as the list manager refused
> the complete submission for overall message size (112KB). I tried to
> reduce the size of the data, but ran into trouble reproducing the error.

You can send me the test case off-list if you like. First please
confirm that you are on 7.4.1 not 7.4? This looks a bit like a bug
that I thought was fixed in 7.4.1.

regards, tom lane

Tom Lane wrote:

>Damon Hart <dhcom(at)sundial(dot)com> writes:
>
>
>>I had to remove the attached schema.sql file as the list manager refused
>>the complete submission for overall message size (112KB). I tried to
>>reduce the size of the data, but ran into trouble reproducing the error.
>>
>>
>
>You can send me the test case off-list if you like. First please
>confirm that you are on 7.4.1 not 7.4? This looks a bit like a bug
>that I thought was fixed in 7.4.1.
>
> regards, tom lane
>
>
>
Sorry for the extra traffic to the list.

Tom -

Thanks for your offer to review this bug. I am, however, stymied by spam
filters which are rejecting all attempts to mail you directly at
tgl(at)sss(dot)pgh(dot)pa(dot)us(dot) Please let me know how to reach you with this email.

Damon Hart

P.S.: Definitely 7.4.1, as per "select version();"