Upon importing a file derived from rails establishment, psql is hitting the error of foreign key constraints:
ERROR: insert or update on table "documents" violates foreign key constraint "fk_rails_d4abdc7f58"
Key (typ_document_id)=(7) is not present in table "typ_documents".
Yet, querying afterwards
SELECT * from typ_documents;
getting a result
7 | Test Internal | 2013-07-04 08:36:16.026295 | 2013-07-04 08:36:16.026295
The only assumption that I can follow is that, when the table documents is being loaded, the order of loading is such that typ_documents is happening after and the error is arising. Yet, this error is not occurring consistently and the data dump is in alphabetical order of table names. Thus, this is a weak assumption.
Removing the foreign key constraint from rails would overcome the problem, but that is a weak reaction.
Update
Data from the pg_dump file (redacted to omit the long full text)
COPY documents (id, titolo, abstract, full_text, typ_document_id, idioma_id, competitor_id, created_at, updated_at) FROM stdin;
[...]
5 Conservazione Finocchio con lavaggio La prova รจ stata [...redacted...] 3 ppm\r\n15\t15\r\n0,75\r\n30 sec\t60 sec\t90 sec 7 1 \N 2013-07-08 10:49:53.393598 2013-07-11 16:07:31.540986
COPY typ_documents (id, nome, created_at, updated_at) FROM stdin;
[...]
7 Test Internal 2013-07-04 08:36:16.026295 2013-07-04 08:36:16.026295
How can this issue be debugged/overcome?
Best Answer
I think your problem is (as a_horse.. says) that your foreign key is not deferrable. Maybe creating your foreign key according the to code below solves your issue.