This is a multi-part message in MIME format.
--------------090806030907050904010708
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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.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




--------------090806030907050904010708
Content-Type: text/plain;
name="script.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="script.sql"

-- only needed if script rerun without psql restart
drop table fill_dates;
drop table tmp_contract;
set ENABLE_NESTLOOP to on;
set ENABLE_MERGEJOIN to on;
set ENABLE_HASHJOIN to on;

VACUUM ANALYZE;

SELECT DISTINCT d.date INTO TEMP fill_dates FROM daily d, futures f
WHERE d.inst_id = f.inst_id
AND f.fut_base_id = 3209
AND f.inst_id <= 3517
AND d.date >= '2002-01-08'
AND d.data_source_id = 36869
AND d.trade_venue_id = 3347
ORDER BY d.date DESC LIMIT 300;

SELECT date, open, high, low, close, volume,
open_interest, update_time, user_id
INTO TEMP tmp_contract
FROM daily
WHERE inst_id = 3517::bigint
AND data_source_id = 36869::bigint
AND trade_venue_id = 3347::bigint
AND date >= (SELECT MIN(date) FROM fill_dates);

create unique index tmp_contract_date on tmp_contract (date);
vacuum analyze tmp_contract;

DELETE FROM fill_dates
WHERE EXISTS (SELECT tc.date
FROM tmp_contract tc
WHERE tc.date = fill_dates.date);

set ENABLE_NESTLOOP to on;
set ENABLE_MERGEJOIN to off;
set ENABLE_HASHJOIN to off;

-- query triggers error

INSERT INTO tmp_contract
SELECT d.date, d.open + tc.close - d2.close AS open,
d.high + tc.close - d2.close AS high,
d.low + tc.close - d2.close AS low,
d.close + tc.close - d2.close AS close,
d.volume, d.open_interest, d.update_time, d.user_id
FROM daily d, daily d2, tmp_contract tc, fill_dates fd
WHERE d.date = fd.date
AND d.inst_id = 3516::bigint
AND d.data_source_id = 36869::bigint
AND d.trade_venue_id = 3347::bigint
AND d2.inst_id = 3516::bigint
AND d2.data_source_id = 36869::bigint
AND d2.trade_venue_id = 3347::bigint
AND d2.date = (SELECT MIN(d3.date)
FROM daily d3, tmp_contract tc2
WHERE d3.date = tc2.date
AND d3.data_source_id = 36869::bigint
AND d3.trade_venue_id = 3347::bigint
AND d3.inst_id = 3516::bigint
AND d3.date > d.date)
AND tc.date = d2.date;

--------------090806030907050904010708
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

--------------090806030907050904010708--