I am still waiting for the first report from my index fix.

Anyone?

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Thu, 3 Sep 1998 00:21:50 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
index fix report
> I am still waiting for the first report from my index fix.
> Anyone?

OK, here is my interim report: everything is broken :)

Looks like David's patches busted several Makefiles. Will poke at it and
let you know.

In the meantime, I can't test your fixes; maybe next time wait a few
hours to let things settle down before adding new breakage? *wink*

                    - Tom

gcc -I../../../include -I../../../backend   -O2  -m486 -Wall
-Wmissing-prototypes -I../.. -Wno-error   -c geqo_ox2.c -o geqo_ox2.o
ld -r -o SUBSYS.o geqo_copy.o geqo_eval.o geqo_main.o geqo_misc.o
geqo_params.o geqo_paths.o geqo_pool.o geqo_recombination.o
geqo_selection.o geqo_erx.o geqo_pmx.o geqo_cx.o geqo_px.o geqo_ox1.o
geqo_ox2.o
make[3]: Leaving directory
`/opt/postgres/pgsql/src/backend/optimizer/geqo'
for i in path plan prep util geqo; do make -C $i prep/SUBSYS.o; done
make[3]: Entering directory
`/opt/postgres/pgsql/src/backend/optimizer/path'
make[3]: *** No rule to make target `prep/SUBSYS.o'.  Stop.
make[3]: Leaving directory
`/opt/postgres/pgsql/src/backend/optimizer/path'
make[3]: Entering directory
`/opt/postgres/pgsql/src/backend/optimizer/plan'
make[3]: *** No rule to make target `prep/SUBSYS.o'.  Stop.
make[3]: Leaving directory
`/opt/postgres/pgsql/src/backend/optimizer/plan'
make[3]: Entering directory
`/opt/postgres/pgsql/src/backend/optimizer/prep'
make[3]: *** No rule to make target `prep/SUBSYS.o'.  Stop.
make[3]: Leaving directory
`/opt/postgres/pgsql/src/backend/optimizer/prep'
make[3]: Entering directory
`/opt/postgres/pgsql/src/backend/optimizer/util'
make[3]: *** No rule to make target `prep/SUBSYS.o'.  Stop.
make[3]: Leaving directory
`/opt/postgres/pgsql/src/backend/optimizer/util'
make[3]: Entering directory
`/opt/postgres/pgsql/src/backend/optimizer/geqo'
make[3]: *** No rule to make target `prep/SUBSYS.o'.  Stop.
make[3]: Leaving directory
`/opt/postgres/pgsql/src/backend/optimizer/geqo'
make[2]: *** [prep/SUBSYS.o] Error 2
make[2]: Leaving directory `/opt/postgres/pgsql/src/backend/optimizer'
make[1]: *** [optimizer.dir] Error 2
make[1]: Leaving directory `/opt/postgres/pgsql/src/backend'
make: *** [install] Error 2

Thu, 03 Sep 1998 04:59:33 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] index fix report
> > I am still waiting for the first report from my index fix.
> > Anyone?
> 
> OK, here is my interim report: everything is broken :)
> 
> Looks like David's patches busted several Makefiles. Will poke at it and
> let you know.
> 
> In the meantime, I can't test your fixes; maybe next time wait a few
> hours to let things settle down before adding new breakage? *wink*

It is compiling here now.  Strange.  There are few problems with
libpgtcl and psql.c that I am fixing now.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Thu, 3 Sep 1998 01:08:12 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] index fix report
> OK, it looks like there is a missing file in the tree. I had to modify
> 
>   ./backend/optimizer/prep/Makefile
> 
> to remove a reference to a nonexistant object file prepkeyset.o
> presumably needing to be built from prepkeyset.c.
> 
> That got things building until the next link step, when some routines
> were a missin'. Got that file anywhere? Seems useful...

No wonder it compiles here so nicely.  Adding it now.  I will also do a
make distclean and cvs to see if any other files are missing.
> 
>                      - Tom
> 
> commands/SUBSYS.o: In function `parse_ksqo':
> commands/SUBSYS.o(.text+0xdff4): undefined reference to
> `_use_keyset_query_optimizer'
> commands/SUBSYS.o(.text+0xe014): undefined reference to
> `_use_keyset_query_optimizer'
> commands/SUBSYS.o: In function `show_ksqo':
> commands/SUBSYS.o(.text+0xe045): undefined reference to
> `_use_keyset_query_optimizer'
> commands/SUBSYS.o: In function `reset_ksqo':
> commands/SUBSYS.o(.text+0xe085): undefined reference to
> `_use_keyset_query_optimizer'
> optimizer/SUBSYS.o: In function `planner':
> optimizer/SUBSYS.o(.text+0x564b): undefined reference to
> `transformKeySetQuery'
> make[1]: *** [postgres] Error 1
> make[1]: Leaving directory `/opt/postgres/pgsql/src/backend'
> make: *** [install] Error 2
> 


-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Thu, 3 Sep 1998 01:09:50 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] index fix report
OK, it looks like there is a missing file in the tree. I had to modify

  ./backend/optimizer/prep/Makefile

to remove a reference to a nonexistant object file prepkeyset.o
presumably needing to be built from prepkeyset.c.

That got things building until the next link step, when some routines
were a missin'. Got that file anywhere? Seems useful...

                     - Tom

commands/SUBSYS.o: In function `parse_ksqo':
commands/SUBSYS.o(.text+0xdff4): undefined reference to
`_use_keyset_query_optimizer'
commands/SUBSYS.o(.text+0xe014): undefined reference to
`_use_keyset_query_optimizer'
commands/SUBSYS.o: In function `show_ksqo':
commands/SUBSYS.o(.text+0xe045): undefined reference to
`_use_keyset_query_optimizer'
commands/SUBSYS.o: In function `reset_ksqo':
commands/SUBSYS.o(.text+0xe085): undefined reference to
`_use_keyset_query_optimizer'
optimizer/SUBSYS.o: In function `planner':
optimizer/SUBSYS.o(.text+0x564b): undefined reference to
`transformKeySetQuery'
make[1]: *** [postgres] Error 1
make[1]: Leaving directory `/opt/postgres/pgsql/src/backend'
make: *** [install] Error 2

Thu, 03 Sep 1998 05:11:32 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] index fix report
> > I am still waiting for the first report from my index fix.
> > Anyone?
> 
> OK, here is my interim report: everything is broken :)
> 
> Looks like David's patches busted several Makefiles. Will poke at it and
> let you know.
> 
> In the meantime, I can't test your fixes; maybe next time wait a few
> hours to let things settle down before adding new breakage? *wink*

OK, everything is there now.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Thu, 3 Sep 1998 01:18:05 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] index fix report
> make distclean and cvs to see if any other files are missing.

More breakage. Will disable the tcl stuff and see if I can proceed...

                     - Tom

gcc -I../../include -I../../backend   -O2  -m486 -Wall
-Wmissing-prototypes -I../../backend -I../../include
-I../../interfaces/libpq -fpic   -c pgtclCmds.c -o pgtclCmds.o
pgtclCmds.c: In function `Pg_connect':
pgtclCmds.c:366: parse error before `else'
pgtclCmds.c:373: warning: control reaches end of non-void function
pgtclCmds.c: In function `Pg_listen':
pgtclCmds.c:1460: parse error at end of input
make[2]: *** [pgtclCmds.o] Error 1
make[2]: Leaving directory `/opt/postgres/pgsql/src/interfaces/libpgtcl'
make[1]: *** [install] Error 2
make[1]: Leaving directory `/opt/postgres/pgsql/src/interfaces'
make: *** [install] Error 2

Thu, 03 Sep 1998 05:30:25 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] index fix report
> make distclean and cvs to see if any other files are missing.

... And more breakage ... Fortunately I had a psql from yesterday lying
around, so will continue testing ...

                     - Tom

psql.c: In function `SendQuery':
psql.c:1139: warning: passing arg 1 of `handleCopyIn' from incompatible
pointer type
psql.c:1143: warning: passing arg 1 of `handleCopyIn' from incompatible
pointer type
psql.c: At top level:
psql.c:2957: conflicting types for `handleCopyIn'
psql.c:153: previous declaration of `handleCopyIn'
make[2]: *** [psql.o] Error 1
make[2]: Leaving directory `/opt/postgres/pgsql/src/bin/psql'
make[2]: Entering directory `/opt/postgres/pgsql/src/bin/pg_dump'

Thu, 03 Sep 1998 05:36:31 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] index fix report
> OK, everything is there now.

The select_having test now passes. The other tests which were broken
yesterday are still broken:

constraints .. failed -- trouble finding a new table after dropping old
...
create_index .. failed -- trouble creating an index after creating first
sanity_check .. failed -- missing tables from previous failures, n/a
...
select_views .. failed -- old (one month?) problem with core dump
...
run_ruletest .. failed -- dba account name differs, n/a

I did a "make distclean" earlier this evening, and a "make clean" and
"initdb" during this recent build session, so things should be up to
date. Let's not worry about select_views until we've fixed constraints
and create_index, since that is an older problem.

I'm sure we are getting closer though...

                  - Tom

Thu, 03 Sep 1998 05:52:52 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] index fix report
around line 812 in access/common/heaptuple.c:

	len = sizeof *tuple - sizeof tuple->t_bits;

This seems questionable for me. 

tuple is a pointer to struct HeaptupleData.

typedef struct HeapTupleData
{
	unsigned int t_len;			/* length of entire tuple */

	[snip]

	uint8		t_hoff;			/* sizeof tuple header */

	bits8		t_bits[MinHeapTupleBitmapSize / 8];
	/* bit map of domains */

	/* MORE DATA FOLLOWS AT END OF STRUCT */
} HeapTupleData;

I think the code tries to calculate the offset from top of the
structure to t_bits. t_bits is the last structure member of
HeapTupleData, and that would give the offset...

No. since the size of the whole structure is aligned to 2-byte, there
is a "padding" byte after t_bits.

I think more acculate way to calculate the offset is:

	len = (char *)&tuple->t_bits[0] - (char *)tuple;

I ran a test and found the first one gives len = 36, while second one
gives 35.

I'm not sure how this affects. maybe nothing (len is aligned to 8-byte
boundary later).
--
Tatsuo Ishii
t-ishii@sra.co.jp

Thu, 03 Sep 1998 15:52:19 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
questionable code in heap_formtuple()


Thomas G. Lockhart wrote:

> > I am still waiting for the first report from my index fix.
> > Anyone?
>
> OK, here is my interim report: everything is broken :)
>
> Looks like David's patches busted several Makefiles. Will poke at it and
> let you know.
>

Did this get resolved?   I reviewed the original patch, and the missing
items that are mentioned in this thread are in the patch.


Thu, 03 Sep 1998 09:33:17 -0400
David Hartwig <daveh@insightdist.com>
Re: [HACKERS] index fix report
> 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.

This may only happen with tables which have been destroyed and then
redefined??

I've done a full "cvs update -Pd", a "make distclean", and an initdb.
I'll try a clean checkout next. If that doesn't help, then will start
poking at it...

                     - Tom

Thu, 03 Sep 1998 14:11:50 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
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.





Thu, 03 Sep 1998 10:41:29 -0400
David Hartwig <daveh@insightdist.com>
Re: [HACKERS] index fix report
> OK, everything is there now.

I did a CVSup, a cvs checkout into a new tree, and still see the same
problems. I'll bet that most of the symptoms in the regression tests are
due to the same thing, which seems to involve table munging of pg_class.
It looks like some tuples end up with leading garbage of some sort in
the first field: see the example below.

I'll try to distill this down to a simpler test case. If anyone has some
hints on where to look...

                       - Tom

regression=> select * from primary_tbl;
ERROR:  primary_tbl: Table does not exist.

regression=> select * from pg_class where relname = 'primary_tbl';
relname|reltype|relowner|relam|relpages|reltuples|relhasindex|relisshared|relkind|relnatts|relchecks|reltriggers|relhasrules|relacl
-------+-------+--------+-----+--------+---------+-----------+-----------+-------+--------+---------+-----------+-----------+------
(0 rows)

regression=> select * from pg_class where relname like '%primary_tbl%';
relname        
|reltype|relowner|relam|relpages|reltuples|relhasindex|relisshared|relkind|relnatts|relchecks|reltriggers|relhasrules|relacl
----------------+-------+--------+-----+--------+---------+-----------+-----------+-------+--------+---------+-----------+-----------+------
primary_tbl     |      0|     500|    0|       0|        0|t         
|f          |r      |       2|        0|          0|f          |
primary_tbl_pkey|      0|     500|  403|       2|     1024|f         
|f          |i      |       2|        0|          0|f          |
(2 rows)

regression=> select * from pg_class where relname like 'primary_tbl%';
relname|reltype|relowner|relam|relpages|reltuples|relhasindex|relisshared|relkind|relnatts|relchecks|reltriggers|relhasrules|relacl
-------+-------+--------+-----+--------+---------+-----------+-----------+-------+--------+---------+-----------+-----------+------
(0 rows)

regression=> select * from pg_class where relname like '%primary_tbl';
relname   
|reltype|relowner|relam|relpages|reltuples|relhasindex|relisshared|relkind|relnatts|relchecks|reltriggers|relhasrules|relacl
-----------+-------+--------+-----+--------+---------+-----------+-----------+-------+--------+---------+-----------+-----------+------
primary_tbl|      0|     500|    0|       0|        0|t         
|f          |r      |       2|        0|          0|f          |
(1 row)

Thu, 03 Sep 1998 15:06:43 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] index fix report
> 
> 
> Thomas G. Lockhart wrote:
> 
> > > I am still waiting for the first report from my index fix.
> > > Anyone?
> >
> > OK, here is my interim report: everything is broken :)
> >
> > Looks like David's patches busted several Makefiles. Will poke at it and
> > let you know.
> >
> 
> Did this get resolved?   I reviewed the original patch, and the missing
> items that are mentioned in this thread are in the patch.

Resolved.  I forgot to add the new file.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Thu, 3 Sep 1998 11:09:54 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] index fix report
Tom, its in the index.   Do an explain before each of your queries.

Thomas G. Lockhart wrote:

> > OK, everything is there now.
>
> I did a CVSup, a cvs checkout into a new tree, and still see the same
> problems. I'll bet that most of the symptoms in the regression tests are
> due to the same thing, which seems to involve table munging of pg_class.
> It looks like some tuples end up with leading garbage of some sort in
> the first field: see the example below.
>
> I'll try to distill this down to a simpler test case. If anyone has some
> hints on where to look...
>
>                        - Tom
>
> regression=> select * from primary_tbl;
> ERROR:  primary_tbl: Table does not exist.
>
> regression=> select * from pg_class where relname = 'primary_tbl';
> relname|reltype|relowner|relam|relpages|reltuples|relhasindex|relisshared|relkind|relnatts|relchecks|reltriggers|relhasrules|relacl
> -------+-------+--------+-----+--------+---------+-----------+-----------+-------+--------+---------+-----------+-----------+------
> (0 rows)
>
> regression=> select * from pg_class where relname like '%primary_tbl%';
> relname
> |reltype|relowner|relam|relpages|reltuples|relhasindex|relisshared|relkind|relnatts|relchecks|reltriggers|relhasrules|relacl
> ----------------+-------+--------+-----+--------+---------+-----------+-----------+-------+--------+---------+-----------+-----------+------
> primary_tbl     |      0|     500|    0|       0|        0|t
> |f          |r      |       2|        0|          0|f          |
> primary_tbl_pkey|      0|     500|  403|       2|     1024|f
> |f          |i      |       2|        0|          0|f          |
> (2 rows)
>
> regression=> select * from pg_class where relname like 'primary_tbl%';
> relname|reltype|relowner|relam|relpages|reltuples|relhasindex|relisshared|relkind|relnatts|relchecks|reltriggers|relhasrules|relacl
> -------+-------+--------+-----+--------+---------+-----------+-----------+-------+--------+---------+-----------+-----------+------
> (0 rows)
>
> regression=> select * from pg_class where relname like '%primary_tbl';
> relname
> |reltype|relowner|relam|relpages|reltuples|relhasindex|relisshared|relkind|relnatts|relchecks|reltriggers|relhasrules|relacl
> -----------+-------+--------+-----+--------+---------+-----------+-----------+-------+--------+---------+-----------+-----------+------
> primary_tbl|      0|     500|    0|       0|        0|t
> |f          |r      |       2|        0|          0|f          |
> (1 row)




Thu, 03 Sep 1998 11:13:43 -0400
David Hartwig <daveh@insightdist.com>
Re: [HACKERS] index fix report


Thomas G. Lockhart wrote:

> > OK, everything is there now.
>
> I did a CVSup, a cvs checkout into a new tree, and still see the same
> problems.

Forgot to mention.  I still have the problem also.    Tom, what are you running on?   Is platform still a factor in this problem?


Thu, 03 Sep 1998 11:19:00 -0400
David Hartwig <daveh@insightdist.com>
Re: [HACKERS] index fix report
In message <35EEA3A6.8E9270F7@alumni.caltech.edu>, "Thomas G. Lockhart" writes:
> > 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.
> 
> This may only happen with tables which have been destroyed and then
> redefined??
> 
> I've done a full "cvs update -Pd", a "make distclean", and an initdb.
> I'll try a clean checkout next. If that doesn't help, then will start
> poking at it...
> 
>                      - Tom
> 

I just did a cvs update, and ran the regression test on solaris.

I think what Tom is describes is my only remaining problem. 
Tables just appear to "vanish".  For instance this is from the
triggers test:

QUERY: create table pkeys (pkey1 int4 not null, pkey2 text not null);
QUERY: create table fkeys (fkey1 int4, fkey2 text, fkey3 int);
QUERY: create table fkeys2 (fkey21 int4, fkey22 text, pkey23 int not null);
QUERY: create index fkeys_i on fkeys (fkey1, fkey2);
QUERY: create index fkeys2_i on fkeys2 (fkey21, fkey22);
QUERY: create index fkeys2p_i on fkeys2 (pkey23);
ERROR:  DefineIndex: fkeys2 relation not found                  

Odd that it works the first time.  The triggers test later crashes.

I also had this at to top of sanity_check:

QUERY: VACUUM;
NOTICE:  Index pg_class_relname_index: NUMBER OF INDEX' TUPLES (169) IS NOT THE SAME AS HEAP' (139)
NOTICE:  Index pg_class_oid_index: NUMBER OF INDEX' TUPLES (169) IS NOT THE SAME AS HEAP' (139)
NOTICE:  Rel pg_trigger: Uninitialized page 0 - fixing                                                     


All in all *much* better!

Thanks,

Tom Szybist
szybist@boxhill.com

Thu, 03 Sep 1998 11:26:40 -0400
"Thomas A. Szybist" <szybist@boxhill.com>
Re: [HACKERS] index fix report 
> 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?

                        - Tom

Fri, 04 Sep 1998 01:22:30 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] index fix report
> > 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.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Thu, 3 Sep 1998 21:24:48 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] index fix report
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.
    ^^^^^^^^^^^^^^^^^^^^^^^^
This is what I have:

SunOS sunpine.krs.ru 5.5.1 Generic_103640-12 sun4u sparc SUNW,Ultra-4

I didn't install 6.4 on this machine but could to do this...
What are the problems ?

Vadim

Fri, 04 Sep 1998 11:07:41 +0800
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] index fix report


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.


Thu, 03 Sep 1998 23:22:43 -0400
David Hartwig <daybee@bellatlantic.net>
Re: [HACKERS] index fix report
> 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.

This does help.  Can you check UpdateRelationRelation(), which is called
from create_index, and which calls CatalogIndexInsert()?  Seems like the
problem must be in that area.

Looks like Tatsuo Ishii is on this already, as he has suggested some
good fixes to heap_addheader(), which is called from
UpdateRelationRelation().

Again, I am sorry to have broken this stuff so badly.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Fri, 4 Sep 1998 10:59:59 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] index fix report
> 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'm not sure how to "step through a bad entry" for this case. Just
haven't done it before, and have never used gdb on the backend. That may
explain why I've got so many debugging print statements :)

I believe that in at least some cases the index itself is damaged. If it
were just problems _updating_ the cache, then stopping and restarting
all frontends and backends might fix the problem, at least for the first
query. That doesn't eliminate the possibility that it is a problem with
the cache as it is first built though.

regression=> select oid, relname from pg_class where relname =
'primary_tbl';
oid|relname
---+-------
(0 rows)

regression=> explain select relname
regression->  from pg_class where relname = 'primary_key';
NOTICE:  QUERY PLAN:

Index Scan using pg_class_relname_index on pg_class
  (cost=2.03 size=2 width=32)

EXPLAIN

regression=> select oid, relname from pg_class
regression->  where relname like '%primary%';
  oid|relname
-----+----------------
19947|primary_tbl
19957|primary_tbl_pkey
(2 rows)

regression=> explain select oid, relname from pg_class
regression->  where oid = 19947;
NOTICE:  QUERY PLAN:

Index Scan using pg_class_oid_index on pg_class
  (cost=2.03 size=2 width=36)

EXPLAIN

So these indices appear damaged. Now here is another symptom from my
regression tests, which appears to illustrate cache damage, though since
it is after the fact perhaps a damaged index has just been changed
enough in the meantime to uncover the right nodes...

The regression result shows trouble finding a relation on which to
create the index, and once it has trouble it never finds the relation
_during the same session_:

QUERY: CREATE INDEX onek_unique1 ON onek USING btree(unique1 int4_ops);
QUERY: CREATE INDEX onek_unique2 ON onek USING btree(unique2 int4_ops);
ERROR:  DefineIndex: onek relation not found
QUERY: CREATE INDEX onek_hundred ON onek USING btree(hundred int4_ops);
ERROR:  DefineIndex: onek relation not found
QUERY: CREATE INDEX onek_stringu1 ON onek USING btree(stringu1
name_ops);
ERROR:  DefineIndex: onek relation not found
QUERY: CREATE INDEX tenk1_unique1 ON tenk1 USING btree(unique1
int4_ops);
QUERY: CREATE INDEX tenk1_unique2 ON tenk1 USING btree(unique2
int4_ops);
QUERY: CREATE INDEX tenk1_hundred ON tenk1 USING btree(hundred
int4_ops);
QUERY: CREATE INDEX tenk2_unique1 ON tenk2 USING btree(unique1
int4_ops);
QUERY: CREATE INDEX tenk2_unique2 ON tenk2 USING btree(unique2
int4_ops);
ERROR:  DefineIndex: tenk2 relation not found
QUERY: CREATE INDEX tenk2_hundred ON tenk2 USING btree(hundred
int4_ops);
ERROR:  DefineIndex: tenk2 relation not found
<snip other tables' indices successfully created>

However, if I go back in after the regression test is over, the table is
found and the index created:

regression=> CREATE INDEX onek_unique2 ON onek
regression->  USING btree(unique2 int4_ops);
CREATE

btw, my linux box is not quite as sensitive to the problem(s) as David's
AIX box; his simpler test case does not fail on my machine :(

                       - Tom

Fri, 04 Sep 1998 15:08:02 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] index fix report
> regression=> explain select oid, relname from pg_class
> regression->  where oid = 19947;
> NOTICE:  QUERY PLAN:
> 
> Index Scan using pg_class_oid_index on pg_class
>   (cost=2.03 size=2 width=36)
> 
> EXPLAIN
> 
> So these indices appear damaged.

Sorry, left out one of the test cases which lead to the conclusion that
multiple indices are damaged on my machine:

regression=> select oid, relname from pg_class
regression->  where oid = 19947;
oid|relname
---+-------
(0 rows)

                  - Tom

Fri, 04 Sep 1998 15:52:54 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] index fix report
> > 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'm not sure how to "step through a bad entry" for this case. Just
> haven't done it before, and have never used gdb on the backend. That may
> explain why I've got so many debugging print statements :)

Oh, you are missing so much fun.  Just compile with -g, and run gdb as
the postgres user, and do 'run -D /usr/local/pgsql/data test' and you
get a nice prompt.  You are not using the postmaster, and are not using
the shared buffer cache, but this is usually not a problem.  Give you
the ability to do all sorts of things.  's' steps into functions, 'n'
steps over functions, 'break' stops at certain functions or line
numbers.

> 
> I believe that in at least some cases the index itself is damaged. If it
> were just problems _updating_ the cache, then stopping and restarting
> all frontends and backends might fix the problem, at least for the first
> query. That doesn't eliminate the possibility that it is a problem with
> the cache as it is first built though.
> 

OK, let me suggest something.  Create a table, make it int4, stuff some
values in there, create an index, do a vacuum, and make sure the index
is being used using EXPLAIN.  Then, see if you can retrieve the values
using the index.

This should show if the problem exists external to pg_class.  I believe
you will find that it works fine, so it must be the system indexes that
are at fault.  Are other system indexes affected, or only pg_class
indexes?

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Fri, 4 Sep 1998 12:13:44 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] index fix report
> around line 812 in access/common/heaptuple.c:
> 
> 	len = sizeof *tuple - sizeof tuple->t_bits;
> 
> This seems questionable for me. 

This is interesting.  They are getting the sizeof tuple->t_bits, not the
offset, so aren't they getting this very wrong?

They are computing the size of the tuple, minus the t_bits field, which
means nothing, no?

> 
> tuple is a pointer to struct HeaptupleData.
> 
> typedef struct HeapTupleData
> {
> 	unsigned int t_len;			/* length of entire tuple */
> 
> 	[snip]
> 
> 	uint8		t_hoff;			/* sizeof tuple header */
> 
> 	bits8		t_bits[MinHeapTupleBitmapSize / 8];
> 	/* bit map of domains */
> 
> 	/* MORE DATA FOLLOWS AT END OF STRUCT */
> } HeapTupleData;
> 
> I think the code tries to calculate the offset from top of the
> structure to t_bits. t_bits is the last structure member of
> HeapTupleData, and that would give the offset...

Does it?

> 
> No. since the size of the whole structure is aligned to 2-byte, there
> is a "padding" byte after t_bits.
> 
> I think more acculate way to calculate the offset is:
> 
> 	len = (char *)&tuple->t_bits[0] - (char *)tuple;

Yours is much better.

> 
> I ran a test and found the first one gives len = 36, while second one
> gives 35.
> 
> I'm not sure how this affects. maybe nothing (len is aligned to 8-byte
> boundary later).

Should affect a lot, if I am understanding it properly.  This is also
done in heap_addheader() later in the file.

I just ran a little test:
	
	#include <stdio.h>
	
	struct test {
	        int x;
	        int y;
	} test;
	
	main()
	{
	    printf("%d\n",sizeof(test.y));
	    return 0;
	}

and with sizeof int == 4, the program returns 4, which is not the offset
of y, but the size of y.  6.3.2 has the same code.

I must be misunderstanding this.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Fri, 4 Sep 1998 13:02:53 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] questionable code in heap_formtuple()
> > around line 812 in access/common/heaptuple.c:
> > 
> > 	len = sizeof *tuple - sizeof tuple->t_bits;
> > 
> > This seems questionable for me. 


> > I think more acculate way to calculate the offset is:
> > 
> > 	len = (char *)&tuple->t_bits[0] - (char *)tuple;

OK, now I am more confused.  Doesn't this work:

    len = sizeof(HeapTupleData) - offsetof(HeapTupleData.t_bits);      

while your solution is finding the size of the area before t_bits?

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Fri, 4 Sep 1998 13:51:13 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] questionable code in heap_formtuple()]
> > > around line 812 in access/common/heaptuple.c:
> > > 
> > > 	len = sizeof *tuple - sizeof tuple->t_bits;
> > > 
> > > This seems questionable for me. 
> 
> 
> > > I think more acculate way to calculate the offset is:
> > > 
> > > 	len = (char *)&tuple->t_bits[0] - (char *)tuple;
> 
> OK, now I am more confused.  Doesn't this work:
> 
>     len = sizeof(HeapTupleData) - offsetof(HeapTupleData.t_bits);      
> 
> while your solution is finding the size of the area before t_bits?

OK, I finally get it.  I was thinking HeapTupleData had the tuple data
in the structure, while obviously it does not.

Sometimes there is no HeapTuple to get the size of at the point you need
it, so I have applied a patch to do

	len = offsetof(HeapTupleData.t_bits);

which should fix the obvious problem Tatsuo Ishii found.  Does that fix
anything, index people?

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Fri, 4 Sep 1998 14:07:18 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] questionable code in heap_formtuple()]
Oops, I meant:

	len = offsetof(HeapTupleData, t_bits);

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Fri, 4 Sep 1998 14:21:23 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] questionable code in heap_formtuple()


Bruce Momjian wrote:

> Oops, I meant:
>
>         len = offsetof(HeapTupleData, t_bits);
>

No luck so far.  I am digging around to see if anything has been effected at
all.

At line 812
      /* len = sizeof *tuple - sizeof tuple->t_bits;  */
       len = offsetof(HeapTupleData, t_bits);


Fri, 04 Sep 1998 15:11:57 -0400
David Hartwig <daveh@insightdist.com>
Re: [HACKERS] questionable code in heap_formtuple()
At 10:59 AM 98.9.4 -0400, Bruce Momjian wrote:

>This does help.  Can you check UpdateRelationRelation(), which is called
>from create_index, and which calls CatalogIndexInsert()?  Seems like the
>problem must be in that area.

The test case David showed no error on my LinuxPPC box.
maybe platform dependent.

>Looks like Tatsuo Ishii is on this already, as he has suggested some
>good fixes to heap_addheader(), which is called from
>UpdateRelationRelation().

No progress so far. I'm looking for the smallest test case which definitely
causes problems. But the behavior of the problems seem "random" on LinuxBox.
Really strange...

BTW, in catalog/pg_type.h:

#define Anum_pg_index_indisunique		8

I think this should be 9.

--
Tatsuo Ishii
t-ishii@sra.co.jp


Sun, 6 Sep 1998 12:52:31 +0900
t-ishii@sra.co.jp (Tatsuo Ishii)
Re: [HACKERS] index fix report
> BTW, in catalog/pg_type.h:
>
> #define Anum_pg_index_indisunique		8
>
> I think this should be 9.


Yep, patch applied.



-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Sun, 6 Sep 1998 00:50:48 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] index fix report
> The test case David showed no error on my LinuxPPC box.
> maybe platform dependent.

Same on my Linux/i686 box. No error with David's simple test.

> No progress so far. I'm looking for the smallest test case which 
> definitely causes problems. But the behavior of the problems seem 
> "random" on LinuxBox. Really strange...

I haven't yet tried stepping through code. But random as it seems the
behavior in the regression test is quite repeatable. I wonder how few
tests we could include and still see a problem there? Maybe I'll look at
that a bit to see if I can help with a repeatable case.

Good hunting...

                    - Tom

Sun, 06 Sep 1998 05:37:59 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] index fix report
> > The test case David showed no error on my LinuxPPC box.
> > maybe platform dependent.
> 
> Same on my Linux/i686 box. No error with David's simple test.
> 
> > No progress so far. I'm looking for the smallest test case which 
> > definitely causes problems. But the behavior of the problems seem 
> > "random" on LinuxBox. Really strange...
> 
> I haven't yet tried stepping through code. But random as it seems the
> behavior in the regression test is quite repeatable. I wonder how few
> tests we could include and still see a problem there? Maybe I'll look at
> that a bit to see if I can help with a repeatable case.
> 
> Good hunting...

I just e-mailed David Hartwig who is in Maryland.  I am going to dial
into his machine in the next day or two and see the problem for myself.
We will work together to see what information we can gather.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Sun, 6 Sep 1998 01:43:42 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] index fix report
Here are some recent observations.

 create table foo (bar int);

 select oid, relname from pg_class;
  oid|relname
-----+-------------------------------
 1247|pg_type
 1249|pg_attribute
 1255|pg_proc
 1259|pg_class
23296|foo
 1261|pg_group
 1262|pg_database
 1264|pg_variable
 1269|pg_log
 1215|pg_attrdef
 1216|pg_relcheck
 1219|pg_trigger
16537|pg_inherits
16548|pg_index
16566|pg_version
16577|pg_statistic
16590|pg_operator
16614|pg_opclass
16624|pg_am
16654|pg_amop
16805|pg_amproc
16869|pg_language
16882|pg_parg
16946|pg_aggregate
17002|pg_ipl
17013|pg_inheritproc
17025|pg_rewrite
17040|pg_listener
17051|pg_description
17061|pg_attribute_relid_attnam_index
17064|pg_attribute_relid_attnum_index
17067|pg_attribute_attrelid_index
17070|pg_proc_oid_index
17073|pg_proc_proname_narg_type_index
17076|pg_proc_prosrc_index
17079|pg_type_oid_index
17082|pg_type_typname_index
17085|pg_class_oid_index
17088|pg_class_relname_index
17091|pg_attrdef_adrelid_index
17094|pg_relcheck_rcrelid_index
17097|pg_trigger_tgrelid_index
17100|pg_description_objoid_index
17184|pg_user
 1260|pg_shadow
17248|pg_rule
17312|pg_view
(47 rows)

[  Notice where "foo" ends up in the list.   What has changed to make it not
be the last row??? ]

Furthermore...

create index foo_idx on foo using btree (bar);

select oid, relname from pg_class;
  oid|relname
-----+-------------------------------
 1247|pg_type
 1249|pg_attribute
 1255|pg_proc
 1259|pg_class
 1261|pg_group
 1262|pg_database
 1264|pg_variable
 1269|pg_log
 1215|pg_attrdef
 1216|pg_relcheck
 1219|pg_trigger
16537|pg_inherits
16548|pg_index
16566|pg_version
16577|pg_statistic
16590|pg_operator
16614|pg_opclass
16624|pg_am
16654|pg_amop
16805|pg_amproc
16869|pg_language
16882|pg_parg
16946|pg_aggregate
17002|pg_ipl
17013|pg_inheritproc
17025|pg_rewrite
17040|pg_listener
17051|pg_description
17061|pg_attribute_relid_attnam_index
17064|pg_attribute_relid_attnum_index
17067|pg_attribute_attrelid_index
17070|pg_proc_oid_index
17073|pg_proc_proname_narg_type_index
17076|pg_proc_prosrc_index
17079|pg_type_oid_index
17082|pg_type_typname_index
17085|pg_class_oid_index
17088|pg_class_relname_index
17091|pg_attrdef_adrelid_index
17094|pg_relcheck_rcrelid_index
17097|pg_trigger_tgrelid_index
17100|pg_description_objoid_index
23296|foo
17184|pg_user
 1260|pg_shadow
23305|foo_idx
17248|pg_rule
17312|pg_view
(48 rows)

[ Again neither "foo' nor 'foo_idx are last. ]

Perhaps this is normal, but I have never seen before; not in system tables or
user tables.


Also Bruce,
As you requested, I SELECT'ed pg_class into another table.  Then, added the
relname and oid indexes to the new table.   After making the corrections to
pg_class to make the new table usable, I was able to INSERT, UPDATE, and
SELECT using indexes, without any problems.









Tue, 08 Sep 1998 15:04:01 -0400
David Hartwig <daveh@insightdist.com>
Re: [HACKERS] index fix report
More observations.

 I can produce the exact scenario on my Linux box at home.  (i.e. create table,
create index, pg_class index damage)  I don't know why I had not come across this
sooner.   I had heard other Linux people could not produce the problem
reliably.

It doesn't  solves the problem;  I just don't feel alone any more.  :)

Theory: Could it be that the index is ok, but that pg_class is corrupted.   This
is based on the earlier observation that shows the most recent inserts and
updated no being appended to the end of the table.

David Hartwig wrote:

> Here are some recent observations.
>
>  create table foo (bar int);
>
>  select oid, relname from pg_class;
>   oid|relname
> -----+-------------------------------
>  1247|pg_type
>  1249|pg_attribute
>  1255|pg_proc
>  1259|pg_class
> 23296|foo
>  1261|pg_group
>  1262|pg_database
>  1264|pg_variable
>  1269|pg_log
>  1215|pg_attrdef
>  1216|pg_relcheck
>  1219|pg_trigger
> 16537|pg_inherits
> 16548|pg_index
> 16566|pg_version
> 16577|pg_statistic
> 16590|pg_operator
> 16614|pg_opclass
> 16624|pg_am
> 16654|pg_amop
> 16805|pg_amproc
> 16869|pg_language
> 16882|pg_parg
> 16946|pg_aggregate
> 17002|pg_ipl
> 17013|pg_inheritproc
> 17025|pg_rewrite
> 17040|pg_listener
> 17051|pg_description
> 17061|pg_attribute_relid_attnam_index
> 17064|pg_attribute_relid_attnum_index
> 17067|pg_attribute_attrelid_index
> 17070|pg_proc_oid_index
> 17073|pg_proc_proname_narg_type_index
> 17076|pg_proc_prosrc_index
> 17079|pg_type_oid_index
> 17082|pg_type_typname_index
> 17085|pg_class_oid_index
> 17088|pg_class_relname_index
> 17091|pg_attrdef_adrelid_index
> 17094|pg_relcheck_rcrelid_index
> 17097|pg_trigger_tgrelid_index
> 17100|pg_description_objoid_index
> 17184|pg_user
>  1260|pg_shadow
> 17248|pg_rule
> 17312|pg_view
> (47 rows)
>
> [  Notice where "foo" ends up in the list.   What has changed to make it not
> be the last row??? ]
>
> Furthermore...
>
> create index foo_idx on foo using btree (bar);
>
> select oid, relname from pg_class;
>   oid|relname
> -----+-------------------------------
>  1247|pg_type
>  1249|pg_attribute
>  1255|pg_proc
>  1259|pg_class
>  1261|pg_group
>  1262|pg_database
>  1264|pg_variable
>  1269|pg_log
>  1215|pg_attrdef
>  1216|pg_relcheck
>  1219|pg_trigger
> 16537|pg_inherits
> 16548|pg_index
> 16566|pg_version
> 16577|pg_statistic
> 16590|pg_operator
> 16614|pg_opclass
> 16624|pg_am
> 16654|pg_amop
> 16805|pg_amproc
> 16869|pg_language
> 16882|pg_parg
> 16946|pg_aggregate
> 17002|pg_ipl
> 17013|pg_inheritproc
> 17025|pg_rewrite
> 17040|pg_listener
> 17051|pg_description
> 17061|pg_attribute_relid_attnam_index
> 17064|pg_attribute_relid_attnum_index
> 17067|pg_attribute_attrelid_index
> 17070|pg_proc_oid_index
> 17073|pg_proc_proname_narg_type_index
> 17076|pg_proc_prosrc_index
> 17079|pg_type_oid_index
> 17082|pg_type_typname_index
> 17085|pg_class_oid_index
> 17088|pg_class_relname_index
> 17091|pg_attrdef_adrelid_index
> 17094|pg_relcheck_rcrelid_index
> 17097|pg_trigger_tgrelid_index
> 17100|pg_description_objoid_index
> 23296|foo
> 17184|pg_user
>  1260|pg_shadow
> 23305|foo_idx
> 17248|pg_rule
> 17312|pg_view
> (48 rows)
>
> [ Again neither "foo' nor 'foo_idx are last. ]
>
> Perhaps this is normal, but I have never seen before; not in system tables or
> user tables.
>
> Also Bruce,
> As you requested, I SELECT'ed pg_class into another table.  Then, added the
> relname and oid indexes to the new table.   After making the corrections to
> pg_class to make the new table usable, I was able to INSERT, UPDATE, and
> SELECT using indexes, without any problems.


Tue, 08 Sep 1998 22:45:27 -0400
David Hartwig <daybee@bellatlantic.net>
Re: [HACKERS] index fix report