Re: ERROR: catalog is missing 3 attribute(s) for relid 150243

Hi lists,

I have an index anomaly on a table; getting the error as subject.

dumping queries behind \dS+ of a table, got 4 queries (see detail below)

1st query --> OK

2nd query --> OK

3rd query ERROR same as subject (ERROR:  catalog is missing 3 attribute(s) for relid 150243)

comment on 3rd query line --pg_catalog.pg_get_indexdef looks good.

so my assumption we have an issue when call pg_catalog.pg_get_indexdef function.

select pg_catalog.pg_get_indexdef ( 150243, 0, TRUE );

return blank.

any ideas?

1st query:

========

SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, false AS relhasoids, c.relispartition, pg_catalog.array_to_string(c.reloptions || array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')
        , c.reltablespace, CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relpersistence, c.relreplident, am.amname
        FROM pg_catalog.pg_class c
         LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
        LEFT JOIN pg_catalog.pg_am am ON (c.relam = am.oid)
        WHERE c.oid = '150243';

2nd query:

========

SELECT a.attname,
          pg_catalog.format_type(a.atttypid, a.atttypmod),
          (SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid, true)
           FROM pg_catalog.pg_attrdef d
           WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
          a.attnotnull,
          (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
           WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation,
          a.attidentity,
          a.attgenerated,
          a.attstorage,
          CASE WHEN a.attstattarget=-1 THEN NULL ELSE a.attstattarget END AS attstattarget,
          pg_catalog.col_description(a.attrelid, a.attnum)
        FROM pg_catalog.pg_attribute a
        WHERE a.attrelid = '150243' AND a.attnum > 0 AND NOT a.attisdropped
        ORDER BY a.attnum;

3rd query:

========
SELECT
c2.relname,
i.indisprimary,
i.indisunique,
i.indisclustered,
i.indisvalid,
pg_catalog.pg_get_indexdef ( i.indexrelid, 0, TRUE ),
pg_catalog.pg_get_constraintdef ( con.oid, TRUE ),
contype,
condeferrable,
condeferred,
i.indisreplident,
c2.reltablespace
FROM
pg_catalog.pg_class C,
pg_catalog.pg_class c2,
pg_catalog.pg_index i
LEFT JOIN pg_catalog.pg_constraint con ON ( conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ( 'p', 'u', 'x' ) )
WHERE
C.oid = '150243'
AND C.oid = i.indrelid
AND i.indexrelid = c2.oid
ORDER BY
i.indisprimary DESC,
i.indisunique DESC,
c2.relname;

4th query:

========

SELECT
c2.relname,
i.indisprimary,
i.indisunique,
i.indisclustered,
i.indisvalid,
pg_catalog.pg_get_indexdef ( i.indexrelid, 0, TRUE ),
pg_catalog.pg_get_constraintdef ( con.oid, TRUE ),
contype,
condeferrable,
condeferred,
i.indisreplident,
c2.reltablespace
FROM
pg_catalog.pg_class C,
pg_catalog.pg_class c2,
pg_catalog.pg_index i
LEFT JOIN pg_catalog.pg_constraint con ON ( conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ( 'p', 'u', 'x' ) )
WHERE
C.oid = '150243'
AND C.oid = i.indrelid
AND i.indexrelid = c2.oid
ORDER BY
i.indisprimary DESC,
i.indisunique DESC,
c2.relname;

--

regards

ujang jaenudin | DBA Consultant (Freelancer)
http://ora62.wordpress.com
http://id.linkedin.com/pub/ujang-jaenudin/12/64/bab