Re: [HACKERS] index fix report - Mailing list pgsql-hackers

Bruce Momjian wrote:

> > > Forgot to mention.  I still have the problem also.    Tom, what are
> > > you running on?   Is platform still a factor in this problem?
> >
> > Platform is a problem in that Bruce's FreeBSD/i686 machine does not show
> > the symptoms, but it's a pretty widespread problem beyond that afaik.
> > I'm running on Linux/i686. Tatsuo sees problems on Linux/PPC. Some Sparc
> > machines running Solaris and Linux apparently still show problems.
> >
> > I'm guessing that it is a byte alignment difference in malloc behavior
> > between the systems which exposes misaligned structures on some
> > platforms. How's that for pure speculation, eh?
>
> Let me tell you what I need to help debug this.
>
> It would help to know if it is the cache, or an index problem.  It is
> sometimes hard to determine because the cache often uses the indexes to
> load the cache.
>
> Can someone step through a bad entry, and tell me where it is failing?
> If it is in the executor, it probably is an index.  EXPLAIN does show
> what indexes are involved.  Are several indexes failing, or just one?
>
> I like the malloc idea, but am unsure how the problem just started
> happening with the multi-key system indexes.

I will try to frame this as best I can.   I would be good it other could
verify my statements or add to them.

When I run this simple scenario:

    create table foo (i int);
    -- everything is fine
    select * from pg_class where relname = 'foo'
    -- no problem
    select * from pg_class where oid = {oid_num}
    -- no problem
    create index foo_x on foo using btree(i);
    --  Looks ok but it is not
     select * from pg_class where relname = 'foo'
    -- no rows found
     select * from pg_class where oid = {oid_num}
    -- no rows found
    -- The table and the index in pg_class cannot be found via ether index.
    --  They look like single part indexes too.
    select * from pg_class
    --  shows foo and foo_x along w/ everything else.
    --  I can use the UPDATE statement to rewrite the foo and foo_x rows into
pg_class
    --  and all is well again.
    -- INSERTing into foo does not seem to be a problem.
    --  ALTER table has similar negative effects on the system tables, but I
have yet to sort them all out.

I have verified all this using the latest snapshot on an AIX 4.1.4 system.
Non-gcc compiler.   I will let you know if the problem is on my Linux box.   I
need to reboot and test.   But to the best of my knowledge the problem in not
showing itself there.