Source of error on psql data load

Question

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?

Answer 1

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.

    ALTER TABLE documents
      ADD FOREIGN KEY (typ_document_id)
      REFERENCES typ_documents(id)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
      DEFERRABLE
      INITIALLY DEFERRED;