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.