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

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.