Re: bug with constraint dependencies? or bug with - Mailing list pgsql-hackers

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