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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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 | Content-Type | Size |
---|---|---|
script.sql | text/plain | 2.0 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Goodwin | 2004-02-22 06:07:35 | Re: [HACKERS] Mac OS X, PostgreSQL, PL/Tcl |
Previous Message | Tom Lane | 2004-02-21 23:25:34 | Re: BUG #1082: Order by doesn't sort correctly. |