Thread: 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 referencedtable "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 itUse DROP ... CASCADE to drop thedependent 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)
--
greg
Attachment
> 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:
> 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: > [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