Violation of Foreign Key constraint even though Key exists (using postgresql jdbc driver)

I’ve been banging head against the wall these last few days trying to figure out what’s going on here so any help would be appreciated.

I also posted a question on Stackoverflow here: https://stackoverflow.com/questions/65126245/violation-of-foreign-key-constraint-in-task-metadata-fk-when-launching-a-task

I’m trying to launch Spring Cloud Data Flow (SCDF) with CockroachDB as the underlying persistence layer.

As far as Table Schema creation, and running Spring Tasks and Spring Batch, that seems to function flawlessly with the Hibernate ORM.

Table schema for task_execution:

CREATE  TABLE public.task_execution ( 
	task_execution_id INT8  NOT  NULL, 
	start_time TIMESTAMP  NULL, 
	end_time TIMESTAMP  NULL, 
	task_name VARCHAR(100) NULL, 
	exit_code INT8  NULL, 
	exit_message VARCHAR(2500) NULL, 
	error_message VARCHAR(2500) NULL, 
	last_updated TIMESTAMP  NULL, 
	external_execution_id VARCHAR(255) NULL, 
	parent_execution_id INT8  NULL, 
	CONSTRAINT  "primary" PRIMARY KEY (task_execution_id ASC), 
	FAMILY "primary" (task_execution_id, start_time, end_time, task_name, exit_code, exit_message, error_message, last_updated, external_execution_id, parent_execution_id) 
)

Table schema for task_execution_metadata:

CREATE  TABLE public.task_execution_metadata ( 
	id  INT8  NOT  NULL, 
	task_execution_id INT8  NOT  NULL, 
	task_execution_manifest STRING  NULL, 
	CONSTRAINT  "primary" PRIMARY KEY (id  ASC), 
	CONSTRAINT task_metadata_fk FOREIGN KEY (task_execution_id) REFERENCES public.task_execution(task_execution_id), 
	FAMILY "primary" (id, task_execution_id, task_execution_manifest) 
)

But when I try to launch tasks using SCDF I run into the following exception:

org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO task_execution_metadata (id, task_execution_id, task_execution_manifest) VALUES (?, ?, ?)]; ERROR: insert on table "task_execution_metadata" violates foreign key constraint "task_metadata_fk"
  Detail: Key (task_execution_id)=(14) is not present in table "task_execution".; nested exception is org.postgresql.util.PSQLException: ERROR: insert on table "task_execution_metadata" violates foreign key constraint "task_metadata_fk"
  Detail: Key (task_execution_id)=(14) is not present in table "task_execution".

task_execution_idis a self incrementing identifier so in the logs I post you will see different values for it. In the above log, I had tried to launch a task 14 times already.

Looking at the SQL access log on my node I could see the following order of operations:

‹exec› ‹"PostgreSQL JDBC Driver"› ‹{}› ‹"SELECT taskdeploy0_.id AS id1_4_, taskdeploy0_.object_version AS object_v2_4_, taskdeploy0_.created_on AS created_3_4_, taskdeploy0_.platform_name AS platform4_4_, taskdeploy0_.task_definition_name AS task_def5_4_, taskdeploy0_.task_deployment_id AS task_dep6_4_ FROM task_deployment AS taskdeploy0_ WHERE taskdeploy0_.task_definition_name = $1 ORDER BY taskdeploy0_.created_on ASC LIMIT $2"› ‹{$1:"'run-batch-client'", $2:"1"}› 1.053 0 ‹""› 0
‹exec› ‹"PostgreSQL JDBC Driver"› ‹{}› ‹"SELECT taskdefini0_.definition_name AS definiti1_3_0_, taskdefini0_.description AS descript2_3_0_, taskdefini0_.definition AS definiti3_3_0_ FROM task_definitions AS taskdefini0_ WHERE taskdefini0_.definition_name = $1"› ‹{$1:"'run-batch-client'"}› 0.904 1 ‹""› 0
‹exec› ‹"PostgreSQL JDBC Driver"› ‹{}› ‹"SELECT appregistr0_.id AS id1_0_, appregistr0_.object_version AS object_v2_0_, appregistr0_.default_version AS default_3_0_, appregistr0_.metadata_uri AS metadata4_0_, appregistr0_.name AS name5_0_, appregistr0_.type AS type6_0_, appregistr0_.uri AS uri7_0_, appregistr0_.version AS version8_0_ FROM app_registration AS appregistr0_ WHERE ((appregistr0_.name = $1) AND (appregistr0_.type = $2)) AND (appregistr0_.default_version = true)"› ‹{$1:"'batch-client'", $2:"4"}› 0.953 1 ‹""› 0
‹exec› ‹"PostgreSQL JDBC Driver"› ‹{}› ‹"SELECT nextval('TASK_SEQ')"› ‹{}› 32.725 1 ‹""› 0
‹exec› ‹"PostgreSQL JDBC Driver"› ‹{}› ‹"INSERT INTO task_execution(task_execution_id, exit_code, start_time, task_name, last_updated, external_execution_id, parent_execution_id) VALUES ($1, $2, $3, $4, $5, $6, $7)"› ‹{$1:"14", $2:"NULL", $3:"NULL", $4:"'run-batch-client'", $5:"'2020-12-07 16:55:55.733'", $6:"NULL", $7:"NULL"}› 1.612 1 ‹""› 0
‹exec› ‹"PostgreSQL JDBC Driver"› ‹{}› ‹"SELECT task_execution_manifest FROM task_execution_metadata AS m INNER JOIN task_execution AS e ON m.task_execution_id = e.task_execution_id WHERE e.task_name = $1 ORDER BY e.task_execution_id DESC LIMIT 1 OFFSET 0"› ‹{$1:"'run-batch-client'"}› 2.138 1 ‹""› 0
‹exec› ‹"PostgreSQL JDBC Driver"› ‹{}› ‹"SELECT nextval('task_execution_metadata_seq')"› ‹{}› 3.576 1 ‹""› 0
‹exec› ‹"PostgreSQL JDBC Driver"› ‹{}› ‹"INSERT INTO task_execution_metadata(id, task_execution_id, task_execution_manifest) VALUES ($1, $2, $3)"› ‹{$1:"13", $2:"14", $3:"'{\"taskDeploymentRequest\":{\"definition\":{\"name\":\"run-batch-client\",\"properties\":{\"management.metrics.tags.service\":\"task-application\",\"spring.datasource.username\":\"SA\",\"spring.datasource.url\":\"jdbc:postgresql://localhost:26257/batchdb?ssl=false\",\"spring.datasource.driverClassName\":\"org.postgresql.Driver\", <SNIP>\"--spring.cloud.task.executionid=14\"]},\"platformName\":\"default\"}'"}› 1.007 1 ‹"insert on table \"task_execution_metadata\" violates foreign key constraint \"task_metadata_fk\""› 0

A foreign key violation occurs on task_execution_id = 14 even though it was inserted into the task_execution table in a previous insertion operation.

However, if I try and reproduce the issue with a small Spring JPA application, with two tables task and sub_task of a similar scheme to SCDF’s task_exection and task_execution_metadata, using the same postgresql driver:

‹exec› ‹"PostgreSQL JDBC Driver"› ‹{}› ‹"INSERT INTO task(body, id) VALUES ($1, $2)"› ‹{$1:"'task'", $2:"3"}› 0.467 1 ‹""› 0
‹exec› ‹"PostgreSQL JDBC Driver"› ‹{}› ‹"SELECT subtask0_.id AS id1_0_0_, subtask0_.body AS body2_0_0_, subtask0_.parent_id AS parent_i3_0_0_ FROM sub_task AS subtask0_ WHERE subtask0_.id = $1"› ‹{$1:"3"}› 0.546 0 ‹""› 0
‹exec› ‹"PostgreSQL JDBC Driver"› ‹{}› ‹"SELECT task0_.id AS id1_1_0_, task0_.body AS body2_1_0_ FROM task AS task0_ WHERE task0_.id = $1"› ‹{$1:"3"}› 0.367 1 ‹""› 0
‹exec› ‹"PostgreSQL JDBC Driver"› ‹{}› ‹"INSERT INTO sub_task(body, parent_id, id) VALUES ($1, $2, $3)"› ‹{$1:"'sub-task'", $2:"3", $3:"3"}› 0.961 1 ‹""› 0

The same insertion call does not incur the foreign key violation.

At this point I am out of ideas and would really appreciate any thoughts or ideas on the issue.

Hi, I see that you have created an an issue on Github, so I will make sure we take a look as soon as we can. Based on the SQL log, I’m surprised this error is happening.