Foreign key violations on task_run when using a custom PostgreSQL search_path

[danmaxis]

Bug summary

When running Prefect Server v3 with PostgreSQL and a custom search_path, we are consistently encountering foreign key violations where task_run.flow_run_id references a non‑existent record in flow_run.

This suggests that, under certain conditions, the server may be inserting task_run records using a database connection whose search_path does not resolve the same schema used when the corresponding flow_run was created. As a result, the task_run insert fails due to an unresolved foreign key reference.

This issue appears only after configuring a non‑default schema via search_path and does not occur when using the default public schema.

Minimal Reproducible Example

Environment

• Prefect Server v3
• PostgreSQL
• Async engine (postgresql+asyncpg)
• Custom schema (e.g. app_prefect)
• search_path configured via Prefect settings / environment variables

Server configuration

export PREFECT_DATABASE_CONNECTION_URL="postgresql+asyncpg://user:password@host:5432/prefect"

export PREFECT_DATABASE_SQLALCHEMY_CONNECT_ARGS='{
  "search_path": "app_prefect",
  "application_name": "prefect-server"
}'

export PREFECT_DATABASE_MIGRATE_ON_START=true

Reproduction steps

Start Prefect Server with the configuration above.
Run a flow with at least one task.
Observe server logs during execution.

Stack Trace

sqlalchemy.dialects.postgresql.asyncpg.AsyncAdapt_asyncpg_dbapi.IntegrityError:
<class 'asyncpg.exceptions.ForeignKeyViolationError'>:
insert or update on table "task_run" violates foreign key constraint
"fk_task_run__flow_run_id__flow_run"

DETAIL:
Key (flow_run_id)=(cee30874-353e-495a-beaf-521d468f2cb6)
is not present in table "flow_run".

Full trace excerpt:

File ".../prefect/server/services/task_run_recorder.py", line 379, in _record_bulk_task_run_events
  await session.execute(upsert_statement)

sqlalchemy.exc.IntegrityError:
(sqlalchemy.dialects.postgresql.asyncpg.IntegrityError)
<class 'asyncpg.exceptions.ForeignKeyViolationError'>:
insert or update on table "task_run" violates foreign key constraint
"fk_task_run__flow_run_id__flow_run"

Version info

Version:              3.7.1
API version:          0.8.4
Python version:       3.12.12
Git commit:           e914068e
Built:                Sat, May 16, 2026 02:20 AM
OS/Arch:              linux/x86_64
Profile:              local
Server type:          server
Pydantic version:     2.13.4

Additional context

Additional Details

• Both flow_run and task_run tables exist and have correct foreign key definitions.
• The issue only occurs when a custom schema is used via search_path.
• When reverting to the default public schema, the problem disappears.
• This raises the possibility that:
  • Different async connections may not consistently apply the same search_path, or
  • Some internal services may be executing with a different or default search_path, causing schema resolution mismatch.
• The error originates entirely from Prefect server internals (no user code interaction with the database).

Hypothesis
It appears that, when using a custom search_path, Prefect may sometimes attempt to insert task_run records before (or independently of) resolving the corresponding flow_run in the same schema, leading to foreign key violations.
This may indicate that search_path handling is not fully consistent across all async database sessions used by the server.

[desertaxle]

Thanks for opening an issue @danmaxis! I tried to reproduce this in Postgres with a custom schema/search path and was unable to trigger the FK violation using the current Prefect database setting names.

One thing to check: the env vars in the issue body are missing the SERVER / API prefix that Prefect expects. Could you try updating them to:

export PREFECT_API_DATABASE_CONNECTION_URL="postgresql+asyncpg://user:password@host:5432/prefect"

export PREFECT_SERVER_DATABASE_SQLALCHEMY_CONNECT_ARGS='{
  "search_path": "app_prefect",
  "application_name": "prefect-server"
}'

Or alternatively:

export PREFECT_SERVER_DATABASE_SQLALCHEMY_CONNECT_ARGS_SEARCH_PATH="app_prefect"
export PREFECT_SERVER_DATABASE_SQLALCHEMY_CONNECT_ARGS_APPLICATION_NAME="prefect-server"

After restarting the server, prefect config view --show-sources should show the database URL and search path coming from the expected env vars. If the issue still reproduces with those settings, it would be helpful to know whether there are any stale Prefect tables in public and whether the server is running with multiple workers/processes.

[danmaxis]

Sorry for missing your follow up, my issue was caused by some concurrency issues from the pod infrastructure where my prefect server lives and the pg_balancer from my db server. I had to set some configurations to sqlalchemy settings that fixed those errors.