0

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?

  • If the dump is ordered alphabetically, then the error is expected. By consistently, do you mean you always have the same data but the behaviour is different? – dezso Feb 12 '16 at 11:45
  • Other tables are imported while using data from subsequently listed tables. Thus the error appears inconsistent to me. – Jerome Feb 12 '16 at 12:01
  • Is there only data in the dump or the tables thenselves, too? Could you show the relevant pieces belonging to either of those two tables? – dezso Feb 12 '16 at 12:09
  • the tables are in there as well. update question. – Jerome Feb 12 '16 at 12:25
  • 1
    @Jerome: you can just remove it for the load and then re-create it afterwards. Or declare the constraint as deferrable and import everything in a single transaction (which you should do anyway) – a_horse_with_no_name Feb 12 '16 at 14:12
0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.