Re: New Instance of Variable Not Found in Subplan Bug - Mailing list pgsql-bugs

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