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
