schema.rb duplicating foreign keys

[sobrinho]

Hello,

I have a postgresql database with 149 foreign keys (241 counting duplicates).

~/Code/... [master] [ruby-1.9.2-p180] 
17:14:51 $ cat db/schema.rb  | grep foreign_key | wc -l
     241

~/Code/... [master] [ruby-1.9.2-p180] 
17:14:58 $ cat db/schema.rb  | grep foreign_key | uniq | wc -l
     149

My schema.rb duplicates some foreign keys without reason and databases can't be created from schema.

This happens on rails 3 (3.0.0 and 3.0.5) and foreigner 0.9.1 (master have same issue).

A small piece of duplication:

add_foreign_key "inventory_request_reservation_items", "inventory_request_items", :name => "request_item_reservation_id_fk", :column => "request_item_id"
add_foreign_key "inventory_request_reservation_items", "inventory_request_reservations", :name => "reservation_id_fk", :column => "reservation_id"
add_foreign_key "inventory_request_reservation_items", "inventory_request_reservations", :name => "reservation_id_fk", :column => "reservation_id"
add_foreign_key "inventory_request_reservation_items", "inventory_request_reservations", :name => "reservation_id_fk", :column => "reservation_id"
add_foreign_key "inventory_request_reservation_items", "inventory_request_reservations", :name => "reservation_id_fk", :column => "reservation_id"
add_foreign_key "inventory_request_reservation_items", "inventory_request_reservations", :name => "reservation_id_fk", :column => "reservation_id"
add_foreign_key "inventory_request_reservation_items", "inventory_request_reservations", :name => "reservation_id_fk", :column => "reservation_id"
add_foreign_key "inventory_request_reservation_items", "inventory_request_reservations", :name => "reservation_id_fk", :column => "reservation_id"
add_foreign_key "inventory_request_reservation_items", "inventory_request_reservations", :name => "reservation_id_fk", :column => "reservation_id"

add_foreign_key "inventory_request_reservation_list_item_inventories", "inventories", :name => "inventory_id_fk", :column => "inventory_id"

add_foreign_key "inventory_request_reservation_list_items", "inventory_request_reservation_items", :name => "reservation_item_id_fk", :column => "reservation_item_id"
add_foreign_key "inventory_request_reservation_list_items", "inventory_request_reservation_lists", :name => "list_id_fk", :column => "list_id"
add_foreign_key "inventory_request_reservation_list_items", "inventory_request_reservations", :name => "reservation_id_fk", :column => "reservation_id"
add_foreign_key "inventory_request_reservation_list_items", "inventory_request_reservations", :name => "reservation_id_fk", :column => "reservation_id"
add_foreign_key "inventory_request_reservation_list_items", "inventory_request_reservations", :name => "reservation_id_fk", :column => "reservation_id"
add_foreign_key "inventory_request_reservation_list_items", "inventory_request_reservations", :name => "reservation_id_fk", :column => "reservation_id"
add_foreign_key "inventory_request_reservation_list_items", "inventory_request_reservations", :name => "reservation_id_fk", :column => "reservation_id"
add_foreign_key "inventory_request_reservation_list_items", "inventory_request_reservations", :name => "reservation_id_fk", :column => "reservation_id"
add_foreign_key "inventory_request_reservation_list_items", "inventory_request_reservations", :name => "reservation_id_fk", :column => "reservation_id"
add_foreign_key "inventory_request_reservation_list_items", "inventory_request_reservations", :name => "reservation_id_fk", :column => "reservation_id"

Maybe the reason is the same name on different tables?

[matthuhiggins]

Hmm.. it's difficult for me to debug this so here are some questions:

Do these same foreign keys show up when doing a "rake db:structure:dump"?
Pick one of these problematic tables and run the big query inside https://github.com/matthuhiggins/foreigner/blob/master/lib/foreigner/connection_adapters/postgresql_adapter.rb

It's going to be one of two problems: Your database actually has duplicates, or the sql query to read the foreign keys is wrong.

[sobrinho]

@matthuhiggins, the dump is fine:

---
-- Name: reservation_id_fk; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY inventory_request_reservation_items
    ADD CONSTRAINT reservation_id_fk FOREIGN KEY (reservation_id) REFERENCES inventory_request_reservations(id);

But query seems wrong:

SELECT tc.constraint_name as name
,ccu.table_name as to_table
,ccu.column_name as primary_key
,kcu.column_name as column
,rc.delete_rule as dependency
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
USING (constraint_catalog, constraint_schema, constraint_name)
JOIN information_schema.referential_constraints rc
USING (constraint_catalog, constraint_schema, constraint_name)
JOIN information_schema.constraint_column_usage ccu
USING (constraint_catalog, constraint_schema, constraint_name)
WHERE tc.constraint_type = 'FOREIGN KEY'
AND tc.constraint_catalog = '...'
AND tc.table_name = 'inventory_request_reservation_list_items'
AND tc.table_schema = ANY (current_schemas(false));
          name          |             to_table                | primary_key |       column        | dependency 
------------------------+-------------------------------------+-------------+---------------------+------------
 reservation_id_fk      | inventory_request_reservations      | id          | reservation_id      | NO ACTION
 reservation_id_fk      | inventory_request_reservations      | id          | reservation_id      | NO ACTION
 reservation_id_fk      | inventory_request_reservations      | id          | reservation_id      | NO ACTION
 reservation_id_fk      | inventory_request_reservations      | id          | reservation_id      | NO ACTION
 reservation_id_fk      | inventory_request_reservations      | id          | reservation_id      | NO ACTION
 reservation_id_fk      | inventory_request_reservations      | id          | reservation_id      | NO ACTION
 reservation_id_fk      | inventory_request_reservations      | id          | reservation_id      | NO ACTION
 reservation_id_fk      | inventory_request_reservations      | id          | reservation_id      | NO ACTION
 reservation_item_id_fk | inventory_request_reservation_items | id          | reservation_item_id | NO ACTION
 list_id_fk             | inventory_request_reservation_lists | id          | list_id             | NO ACTION
(10 rows)

I don't know how we can fix that. I'm looking the database but dump is generated correctly.

Looks like a issue with query, not my database.

[sobrinho]

The query is wrong. I'm working in a fix :)

[matthuhiggins]

That would be sweet. I am using postgres very lightly and have not seen this problem.

[sobrinho]

#47

The pull request fix an issue with repeated names for different constraints :)

[matthuhiggins]

I like this query a lot more than the existing one!

Is pg_constraint defined per database? For example, if there are two projects running on the same postgres server, will this query retrieve the foreign keys from both databases, or just the one for the connection.

Also, do they need to be left joins?

[sobrinho]

Yes, each database has your pg_catalog.pg_constraints table.

And no, the left join could be changed to join, makes more sense.

[sobrinho]

Closing. Pull request was accepted :)