Damn, pg_proc index corrupted, can't find anythign on REINDEX ...

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 :(

I'm not sure how REINDEX works... to restore after some crashes
REINDEX should 1. drop indices; 2. vacuum table(s); 3. create indices
(note - create index *after* table itself is vacuumed).

Vadim

-----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 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

@#%@#$@#$@!$@ 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