Re: Wrong rows selected with view - Mailing list pgsql-general

On Wed, Nov 16, 2005 at 10:53:21AM -0500, Tom Lane wrote:
> Bill Moseley <moseley@hank.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@hank.org