
Can someone add something to the docs that gives an example of what should
be used from the command line to reindex a database's system tables?

All the man page says is use th e-O an d-P options :(

I'm getting:

psql -h pgsql horde
ERROR:  cannot read block 6 of pg_attribute_relid_attnam_index
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

horde=> \d
ERROR:  SearchSysCache: recursive use of cache 4
horde=> \q

I've tried:

bin/postgres -O -P -D `pwd`/data horde

POSTGRES backend interactive interface
$Revision: 1.155.2.1 $ $Date: 2000/08/30 21:19:32 $

backend> reindex database horde;
backend> 

still get it ...

I'm either doing something wrong with REINDEXng the system tables, or this
isn't what hte problem is :(

v7.0.2+ database being run ...

Help? :(

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 


Tue, 26 Sep 2000 15:06:50 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
Damn, pg_proc index corrupted, can't find anythign on REINDEX ...
> -----Original Message-----
> From: pgsql-hackers-owner@hub.org
> The Hermit Hacker
>
> Can someone add something to the docs that gives an example of what should
> be used from the command line to reindex a database's system tables?
>
> All the man page says is use th e-O an d-P options :(
>
> I'm getting:
>
> psql -h pgsql horde
> ERROR:  cannot read block 6 of pg_attribute_relid_attnam_index
> Welcome to psql, the PostgreSQL interactive terminal.
>
> Type:  \copyright for distribution terms
>        \h for help with SQL commands
>        \? for help on internal slash commands
>        \g or terminate with semicolon to execute query
>        \q to quit
>
> horde=> \d
> ERROR:  SearchSysCache: recursive use of cache 4
> horde=> \q
>
> I've tried:
>
> bin/postgres -O -P -D `pwd`/data horde
>
> POSTGRES backend interactive interface
> $Revision: 1.155.2.1 $ $Date: 2000/08/30 21:19:32 $
>
> backend> reindex database horde;

Maybe you have to add FORCE option i.e.
	reindex database horde force;

Regards.

Hiroshi Inoue


Wed, 27 Sep 2000 05:13:48 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: Damn, pg_proc index corrupted, can't find anythign on REINDEX ...

Tom is looking around the server right now, as he wants to try and see
what caused it before we go any further at trying to fix it, but I hadn't
thought to try FORCE ... thanks :)



On Wed, 27 Sep 2000, Hiroshi Inoue wrote:

> > -----Original Message-----
> > From: pgsql-hackers-owner@hub.org
> > The Hermit Hacker
> >
> > Can someone add something to the docs that gives an example of what should
> > be used from the command line to reindex a database's system tables?
> >
> > All the man page says is use th e-O an d-P options :(
> >
> > I'm getting:
> >
> > psql -h pgsql horde
> > ERROR:  cannot read block 6 of pg_attribute_relid_attnam_index
> > Welcome to psql, the PostgreSQL interactive terminal.
> >
> > Type:  \copyright for distribution terms
> >        \h for help with SQL commands
> >        \? for help on internal slash commands
> >        \g or terminate with semicolon to execute query
> >        \q to quit
> >
> > horde=> \d
> > ERROR:  SearchSysCache: recursive use of cache 4
> > horde=> \q
> >
> > I've tried:
> >
> > bin/postgres -O -P -D `pwd`/data horde
> >
> > POSTGRES backend interactive interface
> > $Revision: 1.155.2.1 $ $Date: 2000/08/30 21:19:32 $
> >
> > backend> reindex database horde;
> 
> Maybe you have to add FORCE option i.e.
> 	reindex database horde force;
> 
> Regards.
> 
> Hiroshi Inoue
> 

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 


Tue, 26 Sep 2000 17:38:17 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
RE: Damn, pg_proc index corrupted, can't find anythign on REINDEX ...
It looks like you are suffering from actual hardware failures:

%cd /pgsql/data/base/horde
%ls -l pg_attribute_relid_attnam_index
-rw-------  1 pgsql  pgsql  65536 Aug 21 12:27 pg_attribute_relid_attnam_index
%wc pg_attribute_relid_attnam_index
wc: pg_attribute_relid_attnam_index: read: Input/output error

%wc *
       1       1       4 PG_VERSION
wc: active_sessions: read: Input/output error
      13      50   16384 active_sessions_pkey
       0       0       0 auth_user
       0       0       0 auth_user_md5
       0       3   16384 auth_user_md5_pkey
       0       3   16384 auth_user_pkey
      51     806   32768 imp_addr
       0       5    8192 imp_logs
      97     468   16384 imp_pref
       0       3   16384 k_username
       0       3   16384 k_username_md5
       1     101    8192 pg_aggregate
       1      11   16384 pg_aggregate_name_type_index
       1      12    8192 pg_am
wc: pg_am_name_index: read: Input/output error
       3     220   16384 pg_amop
       3      31   16384 pg_amop_opid_index
       2      26   16384 pg_amop_strategy_index
       2      70    8192 pg_amproc
       0     136    8192 pg_attrdef
       0      10   16384 pg_attrdef_adrelid_index
      35     736   57344 pg_attribute
wc: pg_attribute_relid_attnam_index: read: Input/output error
      16     664   32768 pg_attribute_relid_attnum_index
      23     144   16384 pg_class
       2     112   16384 pg_class_oid_index
       2      17   16384 pg_class_relname_index
      58    3096   73728 pg_description
wc: pg_description_objoid_index: read: Input/output error
       2      84    8192 pg_index
       1      47   16384 pg_index_indexrelid_index
       0       0       0 pg_indexes
       0       0       0 pg_inheritproc
       0       0       0 pg_inherits
       0       3   16384 pg_inherits_relid_seqno_index
       0       4    1752 pg_internal.init
       0       0       0 pg_ipl
       0      10    8192 pg_language
       0       3   16384 pg_language_name_index
       0       8   16384 pg_language_oid_index
       0       0       0 pg_listener
wc: pg_listener_relname_pid_index: read: Input/output error
       1      39    8192 pg_opclass
       1      39   16384 pg_opclass_deftype_index
       1       9   16384 pg_opclass_name_index
wc: pg_operator: read: Input/output error
      11     652   32768 pg_operator_oid_index
      14      95   65536 pg_operator_oprname_l_r_k_index
     176    3305  212992 pg_proc
      71    1520   49152 pg_proc_oid_index
wc: pg_proc_proname_narg_type_index: read: Input/output error
       0       0       0 pg_relcheck
       0       3   16384 pg_relcheck_rcrelid_index
      28     351    8192 pg_rewrite
wc: pg_rewrite_oid_index: read: Input/output error
       0       3   16384 pg_rewrite_rulename_index
       0       0       0 pg_rules
      15     327   16384 pg_statistic
      10     232   16384 pg_statistic_relid_att_index
       0       0       0 pg_tables
       0       6    8192 pg_trigger
       0       4   16384 pg_trigger_tgconstrname_index
       0       5   16384 pg_trigger_tgconstrrelid_index
       0       5   16384 pg_trigger_tgrelid_index
       8     170   16384 pg_type
       3     150   16384 pg_type_oid_index
       2      20   16384 pg_type_typname_index
       0       0       0 pg_user
       0       0       0 pg_views
     655   13822 1132252 total


Do you know if there's a way to determine where these files are
physically stored?  I'm wondering if all the damaged indexes live
on the same disk track/cylinder/whatever ...

			regards, tom lane

Tue, 26 Sep 2000 16:53:17 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Damn, pg_proc index corrupted,
 can't find anythign on REINDEX ... 

@#%@#$@#$@!$@ and checking /var/log/messages confirms that :(

Sep 26 17:01:04 pgsql /kernel: (da1:ahc0:0:1:0): READ(10). CDB: 28 0 0 93 d6 9f 0 0 80 0 
Sep 26 17:01:04 pgsql /kernel: (da1:ahc0:0:1:0): HARDWARE FAILURE info:93d6dd asc:32,0
Sep 26 17:01:04 pgsql /kernel: (da1:ahc0:0:1:0): No defect spare location available field replaceable unit: 4
Sep 26 17:01:04 pgsql /kernel: (da1:ahc0:0:1:0): READ(10). CDB: 28 0 0 93 d6 af 0 0 70 0 
Sep 26 17:01:04 pgsql /kernel: (da1:ahc0:0:1:0): HARDWARE FAILURE info:93d6f1 asc:32,0
Sep 26 17:01:04 pgsql /kernel: (da1:ahc0:0:1:0): No defect spare location available field replaceable unit: 4
Sep 26 17:01:06 pgsql /kernel: (da1:ahc0:0:1:0): READ(10). CDB: 28 0 0 72 96 2f 0 0 10 0 
Sep 26 17:01:06 pgsql /kernel: (da1:ahc0:0:1:0): HARDWARE FAILURE info:729637 asc:32,0
Sep 26 17:01:06 pgsql /kernel: (da1:ahc0:0:1:0): No defect spare location available field replaceable unit: 4

shit shit shit :(

thanks tom ... never even thought to check that :(


On Tue, 26 Sep 2000, Tom Lane wrote:

> It looks like you are suffering from actual hardware failures:
> 
> %cd /pgsql/data/base/horde
> %ls -l pg_attribute_relid_attnam_index
> -rw-------  1 pgsql  pgsql  65536 Aug 21 12:27 pg_attribute_relid_attnam_index
> %wc pg_attribute_relid_attnam_index
> wc: pg_attribute_relid_attnam_index: read: Input/output error
> 
> %wc *
>        1       1       4 PG_VERSION
> wc: active_sessions: read: Input/output error
>       13      50   16384 active_sessions_pkey
>        0       0       0 auth_user
>        0       0       0 auth_user_md5
>        0       3   16384 auth_user_md5_pkey
>        0       3   16384 auth_user_pkey
>       51     806   32768 imp_addr
>        0       5    8192 imp_logs
>       97     468   16384 imp_pref
>        0       3   16384 k_username
>        0       3   16384 k_username_md5
>        1     101    8192 pg_aggregate
>        1      11   16384 pg_aggregate_name_type_index
>        1      12    8192 pg_am
> wc: pg_am_name_index: read: Input/output error
>        3     220   16384 pg_amop
>        3      31   16384 pg_amop_opid_index
>        2      26   16384 pg_amop_strategy_index
>        2      70    8192 pg_amproc
>        0     136    8192 pg_attrdef
>        0      10   16384 pg_attrdef_adrelid_index
>       35     736   57344 pg_attribute
> wc: pg_attribute_relid_attnam_index: read: Input/output error
>       16     664   32768 pg_attribute_relid_attnum_index
>       23     144   16384 pg_class
>        2     112   16384 pg_class_oid_index
>        2      17   16384 pg_class_relname_index
>       58    3096   73728 pg_description
> wc: pg_description_objoid_index: read: Input/output error
>        2      84    8192 pg_index
>        1      47   16384 pg_index_indexrelid_index
>        0       0       0 pg_indexes
>        0       0       0 pg_inheritproc
>        0       0       0 pg_inherits
>        0       3   16384 pg_inherits_relid_seqno_index
>        0       4    1752 pg_internal.init
>        0       0       0 pg_ipl
>        0      10    8192 pg_language
>        0       3   16384 pg_language_name_index
>        0       8   16384 pg_language_oid_index
>        0       0       0 pg_listener
> wc: pg_listener_relname_pid_index: read: Input/output error
>        1      39    8192 pg_opclass
>        1      39   16384 pg_opclass_deftype_index
>        1       9   16384 pg_opclass_name_index
> wc: pg_operator: read: Input/output error
>       11     652   32768 pg_operator_oid_index
>       14      95   65536 pg_operator_oprname_l_r_k_index
>      176    3305  212992 pg_proc
>       71    1520   49152 pg_proc_oid_index
> wc: pg_proc_proname_narg_type_index: read: Input/output error
>        0       0       0 pg_relcheck
>        0       3   16384 pg_relcheck_rcrelid_index
>       28     351    8192 pg_rewrite
> wc: pg_rewrite_oid_index: read: Input/output error
>        0       3   16384 pg_rewrite_rulename_index
>        0       0       0 pg_rules
>       15     327   16384 pg_statistic
>       10     232   16384 pg_statistic_relid_att_index
>        0       0       0 pg_tables
>        0       6    8192 pg_trigger
>        0       4   16384 pg_trigger_tgconstrname_index
>        0       5   16384 pg_trigger_tgconstrrelid_index
>        0       5   16384 pg_trigger_tgrelid_index
>        8     170   16384 pg_type
>        3     150   16384 pg_type_oid_index
>        2      20   16384 pg_type_typname_index
>        0       0       0 pg_user
>        0       0       0 pg_views
>      655   13822 1132252 total
> 
> 
> Do you know if there's a way to determine where these files are
> physically stored?  I'm wondering if all the damaged indexes live
> on the same disk track/cylinder/whatever ...
> 
> 			regards, tom lane
> 

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 


Tue, 26 Sep 2000 18:07:28 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
Re: Damn, pg_proc index corrupted,
 can't find anythign on REINDEX ... 