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