bug with constraint dependencies? or bug with pg_dump/pg_restore?
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
On 28 Aug 2003, Gregory Stark wrote:
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.
Well, it shouldn't be limited to a primary key constraint (you must be
able to reference a non-primary key unique constraint). If we didn't
need to worry about backward compatiblity, we could make it dependant on
the unique/primary key constraint, not the underlying index (because
upgraded old systems might only generate a unique index). However, that
would only help if the unique constraint were created before the
references constraint.
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
... that would only help if the unique constraint were created before the
references constraint.
But in any case, pg_dump ought to be careful to dump index creation
commands before REFERENCES constraints, I should think. If it isn't
doing that, there's a performance hit even if the code manages to work.
regards, tom lane
On Thu, 28 Aug 2003, Tom Lane wrote:
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
... that would only help if the unique constraint were created before the
references constraint.But in any case, pg_dump ought to be careful to dump index creation
commands before REFERENCES constraints, I should think. If it isn't
doing that, there's a performance hit even if the code manages to work.
Good point, the checks would be even slower without the indexes.
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
On 28 Aug 2003, Gregory Stark wrote:
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.
Well, it shouldn't be limited to a primary key constraint (you must be
able to reference a non-primary key unique constraint).
AFAICT the difference is not whether you create the FK constraint during
table creation or add it later (in fact, the same code is executed
either way). The difference in Greg's example is that he said
"REFERENCES test" in one case and "REFERENCES test(a)" in the other.
The first syntax is specifically a dependency on the primary key, the
second is not.
I suppose we could tweak the code to prefer a primary key when there are
multiple matching indexes, but I don't see the point. Having multiple
identical indexes is silly anyway.
regards, tom lane
It looks like the problem has been introduced by recent changes to dump
more stuff as "constraints". pg_dump is careful to dump indexes before
constraints --- but the latter category now includes not only foreign
key constraints, but unique/primary constraints, and those will all end
up sorted by pg_constraint OID, it looks like. I think that should work
anyway, most of the time, but clearly after an OID wraparound it's
possible for the OID-order heuristic to fail. So we need to tweak
pg_dump to categorize the different kinds of constraints separately.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes:
AFAICT the difference is not whether you create the FK constraint during
table creation or add it later (in fact, the same code is executed
either way). The difference in Greg's example is that he said
"REFERENCES test" in one case and "REFERENCES test(a)" in the other.
The first syntax is specifically a dependency on the primary key, the
second is not.
Perhaps there should be some indication of what's going on when you \d the
table. As it is it appears as if the two syntaces are producing equivalent
constraints. I had started using the shorthand out of laziness thinking that.
[On that note, one of my pet peeves is that the default names for constraints
are of the form $1 which requires quoting, but that \d doesn't quote them so
that you can almost but not quite copy the \d output into an sql statement
recreating the constraint.]
I suppose we could tweak the code to prefer a primary key when there are
multiple matching indexes, but I don't see the point. Having multiple
identical indexes is silly anyway.
I didn't have multiple identical indexes when I created this situation. What
happened is that I forgot to create the primary key when I created the table.
Then I added a unique index thinking that was sufficient. -- In Oracle iirc a
unique index IS equivalent to a primary key; I don't think there is a syntax
to add a "primary key" after table creation beyond simply "create unique
index".
Hmmm I think there's still something missing here. I was using pg_restore -r
(actually I was using -L but the list was originally generated with -r) which
seemed like a good idea at the time. Rereading it now it seems like it
shouldn't change anything because it says "This option is the default":
-r
--rearrange
Restore items in modified OID order. By default pg_dump will dump items in an order convenient to pg_dump, then save the
archive in a modified OID order. Most objects will be restored in OID order, but some things (e.g., rules and indexes) will
be restored at the end of the process irrespective of their OIDs. This option is the default.
However the order with -r is clearly broken for this case:
bash-2.05b$ pg_restore -l /tmp/test | grep -v '^;'
2; 7218063 TABLE test postgres
3; 7218066 TABLE test2 postgres
5; 7218063 TABLE DATA test postgres
6; 7218066 TABLE DATA test2 postgres
4; 7218065 INDEX x postgres
7; 7218068 CONSTRAINT test2x postgres
bash-2.05b$ pg_restore -r -l /tmp/test | grep -v '^;'
2; 7218063 TABLE test postgres
3; 7218066 TABLE test2 postgres
7; 7218068 CONSTRAINT test2x postgres
5; 7218063 TABLE DATA test postgres
6; 7218066 TABLE DATA test2 postgres
4; 7218065 INDEX x postgres
(The index is a unique index on test.
Test has no explicit primary key.
Test2 has one foreign key constraint on test)
Attachments:
Greg Stark <gsstark@mit.edu> writes:
Hmmm I think there's still something missing here. I was using pg_restore -r
(actually I was using -L but the list was originally generated with -r) which
seemed like a good idea at the time. Rereading it now it seems like it
shouldn't change anything because it says "This option is the default":
Ah-hah. Someone has let pg_restore get out of sync with pg_dump. Will
fix.
regards, tom lane
Greg Stark <gsstark@mit.edu> writes:
Hmmm I think there's still something missing here. I was using pg_restore -r
(actually I was using -L but the list was originally generated with -r) which
seemed like a good idea at the time. Rereading it now it seems like it
shouldn't change anything because it says "This option is the default":
Actually, your real problem is that the documentation of -r bears no
relationship whatever to what the code really does :-(
I have attempted to improve it.
regards, tom lane
Greg Stark <gsstark@MIT.EDU> writes:
[On that note, one of my pet peeves is that the default names for constraints
are of the form $1 which requires quoting, but that \d doesn't quote them so
that you can almost but not quite copy the \d output into an sql statement
recreating the constraint.]
Oh look, that's already better in 7.4. It's hard to keep up with you guys.
--
greg