32 Character Constraint Name Bug?

From: "the kay (efesar)" <efesar(at)nmia(dot)com>
To: pgadmin-support(at)postgresql(dot)org
Subject: 32 Character Constraint Name Bug?
Date: 2002-12-01 05:53:07
Message-ID: NGBBKFMOILMAGDABPFEGOEMKDCAA.efesar@nmia.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgadmin-support


First of all, sorry for the long post. I think I found a bug.

I get this error when I click on my table "fearuser" in the tree
schemas->public->tables (table definition to follow).

==========

pgAdmin II Error

An error has occurred in pgAdmin II:frmMain.tvTable:

Number: 457
Description: This key is already associated with an element of this
collection

OK

==========

After clicking OK, the fearuser table is displayed, but only Checks (0) and
Columns (30) are displayed. I think there is an error displaying the foreign
keys. I have two foreign keys, one with the name
"fearusersecuritylevel_fearuser" (which is 32 characters long) and
"fearusersecuritylevel_fearusers" (which is 33 characters long). I think
there is a bug in either PG or PGAdmin. I understand the name limit in PG is
32 characters, but it seems to have created a 33 character long object name.
I think PGAdmin is truncating it to 32 characters, and it can't add two of
the same names to the foreign key list.

This was a mistake when I created the foreign keys. I accidentally misnamed
one of the foreign keys. I would like to rename it, but as far as I know I
can't rename constraints. Also, I can't drop the constraint because
(according to the documentation) "To remove FOREIGN KEY constraints you need
to recreate and reload the table". Reloading the table is not possible (or
would be extremely hard) because at least 50 views and 20 tables depend on
this table.

Speaking of which ... does PGAdmin do dependency checking? If not, I might
be able to lend a hand in writing some code to check dependencies (like the
MSSQL Enterprise Manager) ... that might be a useful feature I've always
imagined ... I was also looking at the part about adding foreign keys to
tables ... is that hard to code or has it just been on nobody's priority
list? I might be able to help there too ... (this is off topic -- i should
subscribe to the hacker list) ...

Anyway here is the table definition so you can see what I'm talking about
with the 32/33 chars:

================

-- Table: fearuser
CREATE TABLE fearuser (
status int4 DEFAULT 1,
startdate timestamptz DEFAULT now(),
modifydate timestamptz DEFAULT now(),
securitylevel int4 DEFAULT 0,
realname text,
username text,
password text,
location text,
email text,
url text,
affliliation text,
contactinfo text,
profile text,
newsletter bool DEFAULT 't',
forumupdates bool DEFAULT 't',
showpublic bool DEFAULT 't',
userid int4 DEFAULT nextval('fearuser_userid_seq'::text) NOT NULL,
occupation text,
aim text,
yim text,
msnsm text,
icq text,
interest text,
additional text,
history text,
skill text,
desire text,
philosophy text,
logintime timestamptz DEFAULT now(),
lastlogintime timestamptz DEFAULT now(),
CONSTRAINT fearuser_pkey PRIMARY KEY (userid),
CONSTRAINT fearusersecuritylevel_fearuser FOREIGN KEY (securitylevel)
REFERENCES fearusersecuritylevel (securityid) ON DELETE NO ACTION ON UPDATE
NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT fearusersecuritylevel_fearusers FOREIGN KEY (status) REFERENCES
fearuserstatus (userstatusid) ON DELETE NO ACTION ON UPDATE NO ACTION NOT
DEFERRABLE INITIALLY IMMEDIATE
) WITH OIDS;
REVOKE ALL ON TABLE fearuser FROM PUBLIC;
GRANT ALL ON TABLE fearuser TO webmstrz;

=====

other info:

postgres 7.2
pgadmin II 1.4.2

=====

-Keith Woodell

In response to

Responses

Browse pgadmin-support by date

  From Date Subject
Next Message the kay (efesar) 2002-12-01 06:06:58 30/31 Character Constraint Name Bug? (correction)
Previous Message K Anderson 2002-11-30 20:37:05