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

name: Damon Hart
email: dhcom@sundial.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@atichile.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



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

Damon Hart <dhcom@sundial.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@sundial.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@sss.pgh.pa.us. Please let me know how to reach you with this email.

Damon Hart

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