bug with constraint dependencies? or bug with pg_dump/pg_restore?

From: Gregory Stark <gsstark(at)mit(dot)edu>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: bug with constraint dependencies? or bug with pg_dump/pg_restore?
Date: 2003-08-28 15:37:18
Message-ID: 87r835lro1.fsf@stark.dyndns.tv
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


It seems when you create a new table with the "references" syntax the
constraint is created with a dependency specifically on a "primary key"
constraint on the target table.

However when you alter a table to add a foreign key constraint the constraint
is added with a dependency on any unique index on the column -- not
necessarily a primary key constraint.

This causes problems with pg_dump/pg_restore. I'm having trouble restoring my
database now for a 7.4 beta test because I get errors like:

pg_restore: [archiver (db)] could not execute query: ERROR: there is no UNIQUE constraint matching given keys for referenced table "region"
pg_restore: *** aborted because of error

In fact there is a unique index, but the indexes aren't created by pg_restore
until later in the process.

test=> create table test (a integer);
CREATE TABLE

test=> create unique index test_idx on test (a);
CREATE INDEX
test=> alter table test add constraint test_pkey primary key (a);
ERROR: Existing attribute "a" cannot be a PRIMARY KEY because it is not marked NOT NULL

test=> alter table test alter a set not null;
ALTER TABLE

test=> alter table test add constraint test_pkey primary key (a);
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index 'test_pkey' for table 'test'
ALTER TABLE

test=> create table test2 (a integer references test);
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE TABLE

test=> alter table test drop constraint test_pkey;
NOTICE: constraint $1 on table test2 depends on index test_pkey
ERROR: Cannot drop constraint test_pkey on table test because other objects depend on it
Use DROP ... CASCADE to drop the dependent objects too

test=> drop table test2;
DROP TABLE

test=> create table test2 (a integer);
CREATE TABLE

test=> alter table test2 add constraint test2fk foreign key (a) references foo(a);
NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
ALTER TABLE

test=> alter table test drop constraint test_pkey;
ALTER TABLE

--
greg

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Stephan Szabo 2003-08-28 16:02:02 Re: bug with constraint dependencies? or bug with
Previous Message scott.marlowe 2003-08-28 15:33:15 Re: Hardware recommendations to scale to silly load