schema.rb duplicating foreign keys about foreigner HOT 8 CLOSED

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.

from foreigner.

@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.

from foreigner.

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

from foreigner.

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

from foreigner.

#47

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

from foreigner.

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?

from foreigner.

Yes, each database has your pg_catalog.pg_constraints table.

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

from foreigner.

Closing. Pull request was accepted :)

from foreigner.