Horton
Menu

Thread

  1. Re: [HACKERS] index fix report

    Tatsuo Ishii <t-ishii@sra.co.jp> — 1998-09-06T03:52:31Z

    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

  2. Re: [HACKERS] index fix report

    Bruce Momjian <maillist@candle.pha.pa.us> — 1998-09-06T04:50:48Z

    > 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)

  3. Re: [HACKERS] index fix report

    Thomas Lockhart <lockhart@alumni.caltech.edu> — 1998-09-06T05:37:59Z

    > 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

  4. Re: [HACKERS] index fix report

    Bruce Momjian <maillist@candle.pha.pa.us> — 1998-09-06T05:43:42Z

    > > 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)

  5. Re: [HACKERS] index fix report

    David Hartwig <daveh@insightdist.com> — 1998-09-08T19:04:01Z

    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.

  6. Re: [HACKERS] index fix report

    David Hartwig <daybee@bellatlantic.net> — 1998-09-09T02:45:27Z

    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.