Обсуждение: SQL - display different data

Поиск
Список
Период
Сортировка

SQL - display different data

От
"Wang, Mary Y"
Дата:
Hi,

Here is the table for bug:

  Attribute   |  Type   |                   Modifier
---------------+---------+----------------------------------------------
 bug_id        | integer | not null default nextval('bug_pk_seq'::text)
 group_id      | integer | not null default '0'
 status_id     | integer | not null default '0'
 priority      | integer | not null default '0'
 category_id   | integer | not null default '0'
 submitted_by  | integer | not null default '0'
 assigned_to   | integer | not null default '0'
 date          | integer | not null default '0'
 summary       | text    |
 details       | text    |
 close_date    | integer |
 bug_group_id  | integer | not null default '0'
 resolution_id | integer | not null default '0'
Indices: bug_group_id,
         bug_groupid_assignedto_statusid,
         bug_groupid_statusid,
         bug_pkey

When I get to PostgreSQL , when I typed
"Select bug_id, group_id from bug order by id"
I would all id, and group_id from the bug table.

However, when I type
"Select * from bug where bug_id=166"
No rows are returned.

What puzzled me was able to see the bug_id (166) and group_id for bug_id (166) listed when I did
"Select id, group_id from bug order by id".

Any ideas of what's going on?

Thanks in advance.
Mary

Re: SQL - display different data

От
Tom Lane
Дата:
"Wang, Mary Y" <mary.y.wang@boeing.com> writes:
> However, when I type
> "Select * from bug where bug_id=166"
> No rows are returned.

This sounds to me like the bug_pkey index is corrupted.  You might try
REINDEXing it.  If that fixes it, better look for reasons why it got
corrupted (bad RAM or some such would be my first bet).

            regards, tom lane

Re: SQL - display different data

От
"Wang, Mary Y"
Дата:
Tom,
Thanks for the info.  After I did vacuum of that table, I was able to get the row return by this "Select * from bug
wherebug_id=166" statement.  Another problem I have is that a block of records (consecutive records) are disappeared
frommy database probably because of the index is corrupted.  Is there any other way that I get those records back
withoutdoing a restore? 

Thanks.
Mary




-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, July 21, 2004 5:09 PM
To: Wang, Mary Y
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] SQL - display different data


"Wang, Mary Y" <mary.y.wang@boeing.com> writes:
> However, when I type
> "Select * from bug where bug_id=166"
> No rows are returned.

This sounds to me like the bug_pkey index is corrupted.  You might try
REINDEXing it.  If that fixes it, better look for reasons why it got
corrupted (bad RAM or some such would be my first bet).

            regards, tom lane