Skip site navigation (1) Skip section navigation (2)

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

From: Damon Hart <dhcom(at)sundial(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: intermittent error: 'variable not found in subplan target list' - INCOMPLETE SUBMISSION
Date: 2004-02-22 03:43:36
Message-ID: 40382568.5080309@sundial.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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




Attachment: script.sql
Description: text/plain (2.0 KB)

Responses

pgsql-bugs by date

Next:From: Scott GoodwinDate: 2004-02-22 06:07:35
Subject: Re: [HACKERS] Mac OS X, PostgreSQL, PL/Tcl
Previous:From: Tom LaneDate: 2004-02-21 23:25:34
Subject: Re: BUG #1082: Order by doesn't sort correctly.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group