Error: "catalog is missing 8 attribute(s) for relid 16683"

Started by Alexandru Coseruabout 21 years ago4 messagesgeneral
Jump to latest
#1Alexandru Coseru
alex_spam@distinctgroup.net

Hello..
I've got this error and I don't know how to fix it.
Since it's an production database , I can't drop & recreate it..

Here are some infos below..

[root@gw gateway]# psql -U postgres -h 127.0.0.1 template1
Welcome to psql 7.4.5, 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
template1=# \dt
ERROR: catalog is missing 8 attribute(s) for relid 16683
template1=# VACUUM FULL;
ERROR: catalog is missing 3 attribute(s) for relid 16656

There is some data in pg_attribute :
template1=# select * from pg_attribute LIMIT 3;
attrelid | attname | atttypid | attstattarget | attlen | attnum | attndims | attcacheoff | atttypmod | attbyval | attstorage | attisset | attalign | attnotnull | atthasdef | attisdropped | attislocal | attinhcount
----------+--------------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+----------+----------+------------+-----------+--------------+------------+-------------
1247 | typname | 19 | -1 | 64 | 1 | 0 | -1 | -1 | f | p | f | i | t | f | f | t | 0
1247 | typnamespace | 26 | -1 | 4 | 2 | 0 | -1 | -1 | t | p | f | i | t | f | f | t | 0
1247 | typowner | 23 | -1 | 4 | 3 | 0 | -1 | -1 | t | p | f | i | t | f | f | t | 0
(3 rows)

but non for relid 16683
template1=# select * from pg_attribute where attrelid=16683;
attrelid | attname | atttypid | attstattarget | attlen | attnum | attndims | attcacheoff | atttypmod | attbyval | attstorage | attisset | attalign | attnotnull | atthasdef | attisdropped | attislocal | attinhcount
----------+---------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+----------+----------+------------+-----------+--------------+------------+-------------
(0 rows)

i've tried an pg_dump:

[root@gw gateway]# pg_dump -U mydata -h 127.0.0.1 mydata > data.sql
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: catalog is missing 8 attribute(s) for relid 16683
pg_dump: The command was: SELECT (SELECT usename FROM pg_user WHERE usesysid = datdba) as dba, pg_encoding_to_char(encoding) as encoding, datpath FROM pg_database WHERE datname = 'mydata'

The table affected is pg_user:

[root@gw root]# oid2name -H 127.0.0.1 -U mydata -d mydata -o 16683
Tablename of oid 16683 from database "mydata":
---------------------------------
16683 = pg_user
[root@gw root]# oid2name -H 127.0.0.1 -U mydata -d mydata -o 16656
Tablename of oid 16656 from database "mydata":
---------------------------------
16656 = pg_toast_16384
[root@gw root]# oid2name -H 127.0.0.1 -U mydata -d mydata -o 16384
Tablename of oid 16384 from database "mydata":
---------------------------------
16384 = pg_attrdef

Any select from pg_user fails..

template1=# select * from pg_user;
ERROR: catalog is missing 8 attribute(s) for relid 16683

Even after REINDEX

template1=# reindex table pg_attribute;
REINDEX
template1=# REINDEX INDEX pg_attribute_relid_attnum_index;
REINDEX

template1=# select * from pg_user;
ERROR: catalog is missing 8 attribute(s) for relid 16683

Any ideeas ?

Thanks
Alex

#2Edward Macnaghten
eddy@edlsystems.com
In reply to: Alexandru Coseru (#1)
Re: Error: "catalog is missing 8 attribute(s) for relid

Alexandru Coseru wrote:

Hello..

<snip content="Error Details"/>

Any ideeas ?

Fraid so and it is not good.

I am no expert in the inner-inner workings of Postgres, but my guess is
that your catalogue is, or has been, corrupt.

If this is the case it is unrecoverable. It is a matter of rescuing
what data you can (using pg_dump), and re-creating the database (or even
the entire cluster) from scratch.

Eddy

Show quoted text

Thanks
Alex

#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: Alexandru Coseru (#1)
Re: Error: "catalog is missing 8 attribute(s) for relid 16683"

"Alexandru Coseru" <alex_spam@distinctgroup.net> writes:

[root@gw gateway]# psql -U postgres -h 127.0.0.1 template1
Welcome to psql 7.4.5, 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
template1=3D# \dt
ERROR: catalog is missing 8 attribute(s) for relid 16683
template1=3D# VACUUM FULL;
ERROR: catalog is missing 3 attribute(s) for relid 16656

Something very bad has happened to pg_attribute. I don't think there's
any useful way to recover that database; however, if it's only template1
that is corrupted, you could drop template1 and recreate it from
template0 (see techdocs.postgresql.org for detailed instructions).

The rest of your message suggests that the same corruption has occurred
in both template1 and your "mydata" database. That's really odd. Maybe
template1 was already broken when you cloned it to make mydata? But I
think you'd have noticed before getting very far.

It's barely possible that you could get to a state where pg_dump would
succeed by dropping and recreating the pg_user view --- since it's only
a view, there's no data to lose. I expect that DROP VIEW would not work
but you could simply delete the pg_class row (DELETE FROM pg_class WHERE
oid = 16683) and then make a new view using the same definition you see
in the initdb script:

CREATE VIEW pg_catalog.pg_user AS
SELECT
usename,
usesysid,
usecreatedb,
usesuper,
usecatupd,
'********'::text as passwd,
valuntil,
useconfig
FROM pg_shadow;

regards, tom lane

#4Greg Sabino Mullane
greg@turnstep.com
In reply to: Tom Lane (#3)
Re: Error: "catalog is missing 8 attribute(s) for relid 16683"

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Something very bad has happened to pg_attribute.

Just for a data point, this same problem happened to me on a 8.0
beta database. Actually, only one of 8 databases was affected for that
particular backend, but that one was toast and I had to rebuild it from
a backup. This was a pretty lightly used database, and certainly all
the SQL used was pretty standard (no mucking with pg_attribute directly,
as a google-searched thread of this problem insinuated).

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200503060137
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFCKqVTvJuQZxSWSsgRArrEAJ4jZDILFDgtBF+8GAvMzeGvXsgcPACfbBZi
0zE2+vJbGxB5SePepObp6PY=
=/XRk
-----END PGP SIGNATURE-----