Re: zombie primary key lurches out of database to devour the brains of the unwary

On Aug 31, 2004, at 4:41 PM, Tom Lane wrote:
>> I have a primary key that I can't destroy and can't create. One weird
>> symptom is that when I use \d in psql to attempt to display the
>> constraint, there is no output at all!
>
> That's strange. Maybe some sort of catalog corruption? Try "psql -E"
> to see the queries issued by \d, and then execute them by hand to see
> what you get.

First off, I dumped and restored the database, and the problem seems to have
been resolved. (Before doing so, I restarted the database, which did _not_
help).

Tom, I'm not sure if my problem is still of interest to you, but I have a
backup of my data directory from the time of the problem (database was shut
down during copy), although it is pretty big.

Re: the catalog corruption, I used '\set ECHO_HIDDEN' and '\d
snp_main_chr22_pk', and then manually ran the commands:

egenome_test=# SELECT c.oid,
n.nspname,
c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE pg_catalog.pg_table_is_visible(c.oid)
AND c.relname ~ '^snp_main_chr22_pk$'
ORDER BY 2, 3;

egenome_test-# egenome_test-# egenome_test-# egenome_test-#
egenome_test-# egenome_test-# egenome_test-# oid | nspname |
relname
-----------+---------+-------------------
112337075 | build | snp_main_chr22_pk
(1 row)

egenome_test=# egenome_test=# SELECT relhasindex, relkind, relchecks,
reltriggers, relhasrules
FROM pg_catalog.pg_class WHERE oid = '112337075'

egenome_test-# egenome_test-# egenome_test-# ;
relhasindex | relkind | relchecks | reltriggers | relhasrules
-------------+---------+-----------+-------------+-------------
f | i | 0 | 0 | f
(1 row)

egenome_test=# SELECT a.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod),
(SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
a.attnotnull, a.attnum
FROM pg_catalog.pg_attribute a, pg_catalog.pg_index i
WHERE a.attrelid = '112337075' AND a.attnum > 0 AND NOT a.attisdropped
AND a.attrelid = i.indexrelid
ORDER BY a.attnum
;
egenome_test-# egenome_test-# egenome_test(# egenome_test-#
egenome_test-# egenome_test-# egenome_test-# egenome_test-# attname
| format_type | ?column? | attnotnull |\
attnum
-----------+-------------+----------+------------+--------
refsnp_id | integer | | f | 1
(1 row)

egenome_test=# SELECT i.indisunique, i.indisprimary, a.amname,
c2.relname,
pg_catalog.pg_get_expr(i.indpred, i.indrelid)
FROM pg_catalog.pg_index i, pg_catalog.pg_class c, pg_catalog.pg_class
c2, pg_catalog.pg_am a
WHERE i.indexrelid = c.oid AND c.oid = '112337075' AND c.relam = a.oid
AND i.indrelid = c2.oid
;
egenome_test-# egenome_test-# egenome_test-# egenome_test-#
egenome_test-# indisunique | indisprimary | amname | relname |
pg_get_expr
-------------+--------------+--------+---------+-------------
(0 rows)

egenome_test=#

(No \d results)

-Kevin Murphy