Thread: Damn, pg_proc index corrupted, can't find anythign on REINDEX ...
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
> -----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
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
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
0pg_tables 0 6 8192 pg_trigger 0 4 16384 pg_trigger_tgconstrname_index 0 5
16384pg_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
@#%@#$@#$@!$@ 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