Re: [HACKERS] index fix report

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(at)sra(dot)co(dot)jp

> 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(at)candle(dot)pha(dot)pa(dot)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)

> 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

> > 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(at)candle(dot)pha(dot)pa(dot)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)

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.

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.