I have some code that generates the following log entries (annotated with #). The code takes place inside a transaction. A record is inserted. Then I get the of the newly inserted record, then I explicitly select that record (this is just to illustrate the problem). Then another insert is attempted and fails because of a constraint that requires one of the values entered to be an existing value in another table. But we have just verified that that value exists in the other table! # successful insert Topic Create (0.001205) INSERT INTO topics ("inlibrary", "irx_display_title", "irx_title", "title", "creation_stamp", "last_update_stamp", "url", "mime_type", "irx_doc_path", "dr_title", "irx_machine_id", "source", "keywords") VALUES('t', NULL, 'd gdssg sd5 gd5g ', NULL, '2006-03-29 11:13:42', '2006-03-29 11:13:42', NULL, NULL, '/irx/xml/DGdssgSd5Gd5g.xml', 'dgbtev sgdg d', 'DGdssgSd5Gd5g', NULL, NULL) SQL (0.000440) SELECT currval('inforx.topics_id_seq') # output of a logger.info statement spitting out the id of the newly inserted topic HERE IS THE TOPIC ID: 3126 # i explicitly select the topic: Topic Load (0.000649) SELECT * FROM topics WHERE (topics.id = 3126) LIMIT 1 # and print out a logger statement showing that the topic exists: topic exists TopicSubtopic Create (0.000000) PGError: ERROR: insert or update on table "topic_subtopic" violates foreign key constraint "fk_topic_subtopic_subtopic_1" DETAIL: Key (child_topic_key)=(3126) is not present in table "topics". : INSERT INTO topic_subtopic ("child_topic_key", "topic_key", "display_header") VALUES(3126, 2, 'Medications') The constraint on topic_subtopic is: ALTER TABLE ONLY topic_subtopic ADD CONSTRAINT fk_topic_subtopic_subtopic_1 FOREIGN KEY (child_topic_key) REFERENCES topics(id) ON UPDATE NO ACTION ON DELETE NO ACTION; We already know there is an ID of 3126 in topics....why did the code seem to forget it exists? Remember this all takes place within the same transaction.
on 2006-03-29 23:19