Re: [HACKERS] index fix report

Thomas G. Lockhart wrote:

> > Did this get resolved?
>
> Yes, a file got left out of the cvs commit; Bruce fixed it right away.
>
> Update:
>
> I'm still seeing problems with the regression tests, and it appears to
> be the same symptom reported by someone else earlier: a couple of tables
> (or indices) exist but something is munged in pg_class so that I can
> only see the entry using a "like" query; an "=" equals query does not
> return the row.

I know why this was happening. (At least on the surface) In my case,
whenever a I added an index to a table pg_class_relname_index was getting
corrupted. The nature of the corruption was that any query that used the
pg_class_relname_index to find a table that was just indexed, could no
longer find it. The corruption must occur on the update of pg_class when
the index is added. This explains why:

CREATE TABLE foo (i int);
CREATE INDEX foo_idx ON foo USING btree(i);

SELECT * FROM pg_class;

Showed a complete correct list of tables.

SELECT * FROM pg_class WHERE = 'foo';
and
SELECT * FROM pg_class WHERE LIKE 'foo%';

Showed nothing. I did not know LIKE was using an index. It through
me. Nice job by sombody.

SELECT * FROM pg_class WHERE LIKE '%foo';

Showed my original table. But this query like the first does a full
scan.

In any case I will be doing some testing myself this afternoon.