Re: Wrong rows selected with view

On Wed, Nov 16, 2005 at 10:53:21AM -0500, Tom Lane wrote:
> Bill Moseley <moseley(at)hank(dot)org> writes:
> > The first plan below returns the correct number of rows, the second plan does
> > not. These are after I did the reindex, btw.
>
> Bizarre. What are the datatypes of the columns being joined on? If
> they're string types, what's your database locale and encoding?

The primary keys are all SERIAL, and the FKs are integer. Nothing too
odd.

The odd thing is the row that is not returned is basically a clone of
another row -- which is why I diff'ed them in my first posting.

BTW, this might be obvious, but the reason I'm doing DISTINCT ON
class.id is that the instructors table is a link table and a class can
have more than one instructor. I only want a list of classes, not one
per instructor (which could duplicate them).

I'm still a novice with Pg, so I assume this is what you are asking
(although none of my joins are on text fields).

ws2=> SHOW LC_CTYPE;
-[ RECORD 1 ]---
lc_ctype | en_US

ws2=> SHOW SERVER_ENCODING;
-[ RECORD 1 ]---+-------
server_encoding | LATIN1

So my joins are:

WHERE class.location = location.id -- join with location
AND class.id = instructors.class -- join the instructors
AND instructors.person = person.id -- join the person(s)
AND location.region = region.id; -- join the location to a region

And the .id are all SERIAL integer and the FKs are all integer.

Trying to avoid sending too much unnecessary data to the list, but
here's a sample of the tables:

ws2=> \d region
Table "public.region"
Column | Type | Modifiers
------------+---------+--------------------------------------------------------
id | integer | not null default nextval('public.region_id_seq'::text)
active | boolean | not null default true
sort_order | integer | not null default 1
name | text | not null
Indexes:
"region_pkey" primary key, btree (id)
"region_name_key" unique, btree (nam

ws2=> \d instructors
Table "public.instructors"
Column | Type | Modifiers
--------+---------+-----------
person | integer | not null
class | integer | not null
Indexes:
"instructors_pkey" primary key, btree (person, "class")
"instructors_class_index" btree ("class")
"instructors_person_index" btree (person)
Foreign-key constraints:
"$1" FOREIGN KEY (person) REFERENCES person(id)
"$2" FOREIGN KEY ("class") REFERENCES "class"(id)

ws2=> \d class
Table "public.class"
Column | Type | Modifiers
-------------------------+-----------------------------+-------------------------------------------------------
id | integer | not null default nextval('public.class_id_seq'::text)
name | text | not null
old_id | integer |
location | integer | not null
workshop | integer | not null
class_time | timestamp(0) with time zone | not null
class_end_time | timestamp(0) with time zone | not null
class_size | integer | not null
begin_reg_time | timestamp(0) with time zone |
class_list_sent_time | timestamp(0) with time zone |
class_list_sent_email | text |
reminder_sent_time | timestamp(0) with time zone |
ride_list_sent_time | timestamp(0) with time zone |
html_description | text | not null
short_description | text |
special_instructions | text |
on_hold_message | text |
review_mode | boolean | not null default false
workshop_group | integer | not null
distance_ed | boolean | not null default false
contract_class | boolean | not null default false
online_evaluation | boolean | not null default true
price_scheme | integer | not null
duration | text |
register_cutoff_time | timestamp(0) with time zone | not null
cutoff_message | text |
full_message | text |
wait_list_size | integer |
wait_description | text |
wait_instructions | text |
wait_email_instructions | text |
cancel_late_hours | integer |
cancel_cutoff_hours | integer |
cancel_email | text |
send_confirmation | boolean | not null default true
confirmed_change_notify | text |
send_class_list_email | text |
send_class_hours | integer |
ride_list_hours | integer |
reminder_hours | integer |
notify_email | text |
Indexes:
"class_pkey" primary key, btree (id)
"class_old_id_key" unique, btree (old_id)
"class_class_time_index" btree (class_time)
"class_old_id_index" btree (old_id)
"class_workshop_index" btree (workshop)
Foreign-key constraints:
"$1" FOREIGN KEY ("location") REFERENCES "location"(id)
"$2" FOREIGN KEY (workshop) REFERENCES workshop(id)
"$3" FOREIGN KEY (workshop_group) REFERENCES workshop_group(id)
"$4" FOREIGN KEY (price_scheme) REFERENCES pricing(id)

ws2=> \d location
Table "public.location"
Column | Type | Modifiers
------------------+--------------+----------------------------------------------------------
id | integer | not null default nextval('public.location_id_seq'::text)
old_id | integer |
active | boolean | not null default true
name | text | not null
class_size | integer | not null
num_workstations | integer |
time_zone | integer | not null
directions | text | not null
region | integer | not null
phone | text |
address | text |
city | text |
zip | text |
state | character(2) |
map_link | text |
Indexes:
"location_pkey" primary key, btree (id)
"location_name_key" unique, btree (name)
"location_old_id_key" unique, btree (old_id)
"location_old_id" btree (old_id)
Foreign-key constraints:
"$1" FOREIGN KEY (time_zone) REFERENCES timezone(id)
"$2" FOREIGN KEY (region) REFERENCES region(id)
"$3" FOREIGN KEY (state) REFERENCES geo_state(state)

--
Bill Moseley
moseley(at)hank(dot)org