Thread: Another nasty pg_dump bug in 7.3 (and 7.4?)

This table:

australia=# \d users_users                                        Table "public.users_users"    Column     |
Type          |
 
Modifiers
----------------+--------------------------+--------------------------------
---------------------------------userid         | integer                  | not null default
nextval('public.users_users_userid_seq'::text)sex            | character(1)             | not nullsuspended      |
boolean                 | not null default 'f'recurring      | boolean                  | not null default 'f'referrer
    | integer                  |
 
<snip fields>
Check constraints: "$2" (NOT (recurring AND suspended))                  "users_users_sex" ((sex = 'M'::bpchar) OR (sex
=
'F'::bpchar))
Foreign Key constraints: $1 FOREIGN KEY (referrer) REFERENCES
users_users(userid) ON UPDATE NO ACTION ON DELETE SET NULL

Is dumped like this:

CREATE TABLE users_users (   userid SERIAL,   sex character(1) NOT NULL,   suspended boolean DEFAULT 'f' NOT NULL,
recurringboolean DEFAULT 'f' NOT NULL,   referrer integer,   CHECK ((NOT (recurring AND suspended))),   CONSTRAINT
users_users_sexCHECK (((sex = 'M'::bpchar) OR (sex =
 
'F'::bpchar)))
);
ALTER TABLE ONLY users_users   ADD CONSTRAINT "$1" FOREIGN KEY (referrer) REFERENCES
users_users(userid) ON UPDATE NO ACTION ON DELETE SET NULL;

Restoring this fails with:

ERROR:  constraint "$1" already exists for relation "users_users"

Solution:

We need to dump constraint name always for CHECK, or suppress default names
in ADD FOREIGN KEY.

Chris

"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> We need to dump constraint name always for CHECK, or suppress default names
> in ADD FOREIGN KEY.

Didn't Rod Taylor fix that recently?
        regards, tom lane

> "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> > We need to dump constraint name always for CHECK, or suppress default
names
> > in ADD FOREIGN KEY.
>
> Didn't Rod Taylor fix that recently?

Maybe - I haven't checked 7.4's dump of that yet...I'll give it a go...

Chris

> "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> > We need to dump constraint name always for CHECK, or suppress default
names
> > in ADD FOREIGN KEY.
>
> Didn't Rod Taylor fix that recently?

You're quite correct - it has been fixed.

Chris