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

[Kevin Murphy]

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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Kevin Murphy <mu****@genome. chop.edu> writes:

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.
egenome_test=# alter table snp_main_chr22 drop constraint
snp_main_chr22_ pk;
ERROR: constraint "snp_main_chr22 _pk" does not exist

Note that this only says the table doesn't have a constraint by that
name. There could for instance be a non-constraint-associated index
or table by that name. I'm not sure why you're not seeing it in \d,
though, if that were the case.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend