Thread: Record not returned until index is rebuilt

I have the following table definition in postgres 8.0:

CREATE TABLE "userInfo"
(
  "userID" character varying(32) NOT NULL,
  "password" character varying(32) NOT NULL,
  salt character varying(16) NOT NULL,
  CONSTRAINT "userInfo_PK" PRIMARY KEY ("userID")
) ;

Administrators are able to add new users to this table via PHP web
interface. It works fine most of the time. Every once in a while, we
get a problem where a newly added user does not show up in the list of
all users after being added.

For example, I will add a user named "test" to the database. When I do
SELECT * FROM "userInfo"
I will see that record in the database. However if I do
SELECT * FROM "userInfo" WHERE "userID"='test'
no records are returned.

This record will not show up in the query where it's specified as a
WHERE clause until I REINDEX and VACUUM ANALYZE the table. After that,
the record will show up in both queries. Newly added users will show up
for a while, until the same problem occurs.

Why is it that the record is visible when bulk selected, but not when
selected as a part of the WHERE clause, and why is it that the index
rebuild and vacuum fixes the problem? Is it possible that the primary
key index is not being updated properly?

Thanks.

"Nik" <XLPizza@gmail.com> writes:
> I have the following table definition in postgres 8.0:

PG 8.0.what, exactly?

> Why is it that the record is visible when bulk selected, but not when
> selected as a part of the WHERE clause, and why is it that the index
> rebuild and vacuum fixes the problem?

Sounds like a corrupted index.  It seems pretty odd that you would be
getting recurring corruptions like that --- we've recently fixed some
corner case bugs causing index corruption, but they're not all that easy
to trigger (and most of the ones I remember had other symptoms than just
search misses).  How much faith have you got in your hardware platform?

            regards, tom lane

Thanks for the quick response.

It is PostgreSQL 8.0.6 running on Windows 2003 Server.

I haven't had any other issues with this particular piece of hardware
and it is fairly new and well maintained (not that this matters much).
I am going to try to drop and recreate this table, index, and the
contents and see if I face any other problems.

Nik

On Jan 26, 4:26 pm, t...@sss.pgh.pa.us (Tom Lane) wrote:
> "Nik" <XLPi...@gmail.com> writes:
> > I have the following table definition in postgres 8.0:PG 8.0.what, exactly?
>
> > Why is it that therecordis visible when bulk selected, butnotwhen
> > selected as a part of the WHERE clause, and why is it that the index
> > rebuild and vacuum fixes the problem?Sounds like a corrupted index.  It seems pretty odd that you would be
> getting recurring corruptions like that --- we've recently fixed some
> corner case bugs causing index corruption, but they'renotall that easy
> to trigger (and most of the ones I remember had other symptoms than just
> search misses).  How much faith have you got in your hardware platform?
>
>                         regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster