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.