Thread: Error: "catalog is missing 8 attribute(s) for relid 16683"
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.
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
\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)
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)
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'
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
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
ERROR: catalog is missing 8 attribute(s) for relid 16683
Even after REINDEX
template1=# reindex table pg_attribute;
REINDEX
REINDEX
template1=# REINDEX INDEX pg_attribute_relid_attnum_index;
REINDEX
REINDEX
template1=# select * from pg_user;
ERROR: catalog is missing 8 attribute(s) for relid 16683
Any ideeas ?
Thanks
Alex
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 > > > Thanks > Alex
"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
-----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-----