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

New Instance of Variable Not Found in Subplan Bug

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: New Instance of Variable Not Found in Subplan Bug
Date: 2004-03-05 19:47:33
Message-ID: 200403051147.33961.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-bugs
Tom,

I think I have a new instance of the "Variable not Found in Subplan Target 
List" bug, or at least one that was not patched in 7.4.1.

Version: 7.4.1 from source
Platform:  RH Linux 7.3 running on Dual Athalon
Severity:  Showstopper
Symptoms:

Converted 7.2 databse to 7.4.1 three weeks ago.   This view worked normally 
for those 3 weeks; in fact, it worked normally until a couple of hours ago 
(and was in heavy use all that time, being queried about 1000 times per day)  
It is still in use on a mirror server, with identical schema but slightly 
different data, where the error does NOT occur.

Starting about 2 hours ago, we began to get this:
net_test=# select * from sv_cases;
ERROR:  variable not found in subplan target lists

The database is huge, proprietary, and very complex.   I can't provide you 
with full schema on this list, but could provide more information privately.

Here is the view:
CREATE VIEW "sv_cases" as
SELECT cases.case_id, cases.case_name, cases.docket, status.status_label,
 cases.opp_counsel_name, trial_groups.tgroup_name, cases.tgroup_id,
  cases.status, cases.lead_case_docket, cases.lead_case_id,
  cases.priority, tpr.rollup1 as pr_element, tpr.rollup2 as pr_label
FROM status,
    ( SELECT vchar_to_int2(list_value) as priority, rollup1, rollup2
        from text_list_values WHERE list_name = 'Case Priority' ) tpr,
  cases LEFT JOIN trial_groups on cases.tgroup_id = trial_groups.tgroup_id
WHERE (cases.status = status.status AND status.relation = 'cases'::"varchar")
         AND cases.priority = tpr.priority;


I cannot run an EXPLAIN, it errors out as well.
And, per one of your previous e-mails, I tried forcing a change in the plan, 
but to no benefit:

jwnet_test=# set enable_hashjoin=false;
SET
jwnet_test=# select * from sv_cases;
ERROR:  variable not found in subplan target lists
jwnet_test=# set enable_hashjoin=true;
SET
jwnet_test=# set enable_mergejoin=false;
SET
jwnet_test=# select * from sv_cases;
ERROR:  variable not found in subplan target lists
jwnet_test=# set enable_mergejoin=true;
SET
jwnet_test=# set enable_nestloop=false;
SET
jwnet_test=# select * from sv_cases;
ERROR:  variable not found in subplan target lists

If there is a patch for this that isn't in 7.4.1, please let me know where I 
can grab it other than the archives, as the HTML formatting is messing me up 
with the 11/2003 patch.   Thanks!

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2004-03-05 20:12:55
Subject: Re: New Instance of Variable Not Found in Subplan Bug
Previous:From: Robert CreagerDate: 2004-03-05 03:46:19
Subject: Re: BUG #1094: date_part('week') bug

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