Re: New Instance of Variable Not Found in Subplan Bug

Tom,

> [scratches head...] That doesn't make any sense to me at all ...
> there must be some difference between the two view definitions.
> The planner doesn't have any statistics associated with views,
> only with underlying tables (in fact it never even sees the views).

Unlikely, given that I created the second view by copying the \d output of the
first view.

However, here goes. First is \d for the bad view, and second is \d and 2nd
for the good view. I can't see any difference. Can you?

View "public.text_list_values"
Column | Type | Modifiers
--------------------+------------------------+-----------
list_id | integer |
list_name | character varying(30) |
list_group | character varying(30) |
app_id | integer |
list_status | integer |
list_status_label | character varying(30) |
list_format | character varying(30) |
item_length | smallint |
value_id | integer |
list_value | character varying(50) |
description | character varying(100) |
rollup1 | character varying(100) |
rollup2 | character varying(50) |
value_status | integer |
value_status_label | smallint |
View definition:
SELECT text_lists.list_id, text_lists.list_name, text_lists.list_group,
text_lists.app_id, text_lists.status AS list_status, s1.status_label AS
list_status_label, text_lists.list_format, text_lists.item_length,
list_values.value_id, list_values.list_value, list_values.description,
list_values.rollup1, list_values.rollup2, list_values.status AS value_status,
s2.status AS value_status_label
FROM text_lists
JOIN list_values USING (list_id)
JOIN status s1 ON text_lists.status = s1.status AND s1.relation::text =
'text_lists'::character varying::text
JOIN status s2 ON list_values.status = s2.status AND s2.relation::text =
'list_values'::character varying::text;

View "public.text_list_values_2"
Column | Type | Modifiers
--------------------+------------------------+-----------
list_id | integer |
list_name | character varying(30) |
list_group | character varying(30) |
app_id | integer |
list_status | integer |
list_status_label | character varying(30) |
list_format | character varying(30) |
item_length | smallint |
value_id | integer |
list_value | character varying(50) |
description | character varying(100) |
rollup1 | character varying(100) |
rollup2 | character varying(50) |
value_status | integer |
value_status_label | smallint |
View definition:
SELECT text_lists.list_id, text_lists.list_name, text_lists.list_group,
text_lists.app_id, text_lists.status AS list_status, s1.status_label AS
list_status_label, text_lists.list_format, text_lists.item_length,
list_values.value_id, list_values.list_value, list_values.description,
list_values.rollup1, list_values.rollup2, list_values.status AS value_status,
s2.status AS value_status_label
FROM text_lists
JOIN list_values USING (list_id)
JOIN status s1 ON text_lists.status = s1.status AND s1.relation::text =
'text_lists'::character varying::text
JOIN status s2 ON list_values.status = s2.status AND s2.relation::text =
'list_values'::character varying::text;

--
-Josh Berkus
Aglio Database Solutions
San Francisco