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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2004-03-05 20:12:55 | Re: New Instance of Variable Not Found in Subplan Bug |
Previous Message | Robert Creager | 2004-03-05 03:46:19 | Re: BUG #1094: date_part('week') bug |