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

[Kevin Murphy <murphy(at)genome(dot)chop(dot)edu>]

Using PG 7.4.5 on Mac OS X 10.3 ...

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!  Normally, psql either shows the 
constraint or reports that the constraint doesn't exist.

(BTW, I know that the default clauses below are kind of weird, but I am 
using PG to process data for a database that doesn't use NULL and uses 
0 and empty strings as default values.  The DDL is generated 
automatically, so the defaults don't all make sense, but they should be 
harmless.  Don't worry; the app is almost ported to PG ;-)

In the transcript below, snp_main_chr22 is the table, and there is 
supposed to be a primary key snp_main_chr22_pk on the refsnp_id column:

egenome_test=# egenome_test=# \d snp_main_chr22
                    Table "build.snp_main_chr22"
   Column    |         Type          |           Modifiers
-------------+-----------------------+-------------------------------
 refsnp_id   | integer               | default 0
 variation   | character varying(10) | default ''::character varying
 het         | character varying(20) | default ''::character varying
 validated   | character varying(5)  | default ''::character varying
 chr         | character varying(2)  | default ''::character varying
 assay_size  | integer               | default 0
 pop_size    | integer               | default 0
 seq_pos     | integer               | default 0
 transcribed | character varying(1)  | default ''::character varying
egenome_test=#

egenome_test=# alter table snp_main_chr22 drop constraint 
snp_main_chr22_pk;
ERROR:  constraint "snp_main_chr22_pk" does not exist

egenome_test=# drop table snp_main_chr22 cascade;
DROP TABLE

egenome_test=# \d snp_main_chr22_pk

egenome_test=# \d snp_main_chr22_pk_gibberish
Did not find any relation named "snp_main_chr22_pk_gibberish".

egenome_test=# CREATE TABLE snp_main_chr22 (
refsnp_id                      integer DEFAULT 0,
variation                      varchar(10) DEFAULT '',
het                            varchar(20) DEFAULT '',
validated                      varchar(5) DEFAULT '',
chr                            varchar(2) DEFAULT '',
assay_size                     integer DEFAULT 0,
pop_size                       integer DEFAULT 0,
seq_pos                        integer DEFAULT 0,
transcribed                    varchar(1) DEFAULT ''
);
egenome_test(# egenome_test(# egenome_test(# egenome_test(# 
egenome_test(# egenome_test(# egenome_test(# egenome_test(# 
egenome_test(# egenome_test(# CREATE TABLE

egenome_test=# ALTER TABLE snp_main_chr22
  ADD CONSTRAINT snp_main_chr22_pk
  PRIMARY KEY (refsnp_id)
  ;
egenome_test-# egenome_test-# egenome_test-# NOTICE:  ALTER TABLE / ADD 
PRIMARY KEY will create implicit index "snp_main_chr22_pk" for table 
"snp_main_chr22"
ERROR:  relation "snp_main_chr22_pk" already exists

egenome_test=# egenome_test=# \d snp_main_chr22
                    Table "build.snp_main_chr22"
   Column    |         Type          |           Modifiers
-------------+-----------------------+-------------------------------
 refsnp_id   | integer               | default 0
 variation   | character varying(10) | default ''::character varying
 het         | character varying(20) | default ''::character varying
 validated   | character varying(5)  | default ''::character varying
 chr         | character varying(2)  | default ''::character varying
 assay_size  | integer               | default 0
 pop_size    | integer               | default 0
 seq_pos     | integer               | default 0
 transcribed | character varying(1)  | default ''::character varying
egenome_test=#


Thanks for any advice.

Kevin Murphy