Foreign key constraint violations occur in autocommit mode under normal concurrency (single Postgres server)

[mkreidenweis-schulmngr]

Summary

On PgDog v0.1.20, our production setup frequently encounters foreign key constraint errors in places where PostgreSQL's consistency guarantees should apply (single Postgres instance, no sharding or replication). We run Sequelize ORM (using node-postgres) in autocommit mode, through PgDog transaction pool mode. These errors did not occur previously when connecting our app servers directly to PostgreSQL with identical Sequelize settings.

Scenario

• Application (Sequelize ORM, autocommit mode—no explicit transactions in the affected code paths) creates a parent row (e.g. Appointment) via INSERT ... RETURNING id.
• Immediately afterwards (as part of the same API request), it performs an UPDATE or INSERT to a child table referencing this newly inserted row (e.g. Proposal.appointmentId = newAppointment.id).
• Occasionally (but unpredictably), PostgreSQL responds with a foreign key violation: "Key (appointmentId)=(...) is not present in table ...", although inspecting the database a moment later confirms the row does exist.
• If the operation is retried (by the user), a new parent row is created and is successfully referenced, while the originally created parent row remains visible in the database.
• This occurs with moderate concurrency across several PgDog frontends and app servers.
• Some other parallel code paths and user actions do use explicit transactions when accessing other tables on the same database.
• No explicit transactions are used for these queries (autocommit mode).

Observations

• The problem did not occur with direct Postgres connections, nor does it occur if we explicitly wrap both operations in a sequelize.transaction(...) block.
• PostgreSQL error 23503 occurs: "Key (X) is not present in table Y", but querying immediately confirms the row exists.
• Occurs both for INSERT and UPDATE statements on child/ join tables with FK constraints.
• Upgrading from PgDog 0.1.17 to 0.1.20 eliminated ProtocolOutOfSync and dirty connection errors, but this FK issue persists.
• We cannot reliably reproduce this on demand — it appears randomly under production load.
• Setting connection_recovery = "drop" made the situation a lot worse on v0.1.17.

Minimal Code Example

const proposal = Proposal.findOne(...);
// Not wrapped in an explicit transaction - autocommit mode
const appointment = await Appointment. create({ ... });
await proposal.setAppointment(appointment);
// The FK violation has occurred on the setAppointment() call

(This logic previously never produced FK errors when connected directly to Postgres.)

PgDog Configuration

[general]
port                      =   5432
workers                   =   4
default_pool_size         =   10
pooler_mode               =   "transaction"
healthcheck_interval      =   30_000
idle_healthcheck_interval =   30_000
idle_healthcheck_delay    =   5_000
healthcheck_timeout       =   5_000
rollback_timeout          =   5_000
load_balancing_strategy   =   "round_robin"
read_write_strategy       =   "conservative"
tls_verify                =   "prefer"
shutdown_timeout          =   90_000
prepared_statements       =   "extended"
passthrough_auth          =   "disabled"
connect_timeout           =   5_000
connect_attempts          =   1
connect_attempt_delay     =   0
query_timeout             =   90_000
checkout_timeout          =   5_000
dry_run                   =   false
idle_timeout              =   55_000
mirror_queue              =   128
mirror_exposure           =   1.0
auth_type                 =   "scram"
cross_shard_disabled      =   false
openmetrics_port = 9090
openmetrics_namespace = "pgdog_"
server_lifetime = 86400000
log_connections = true
log_disconnections = true
expanded_explain = false
[[databases]]
name = "xxx"
host = "x.x.x.x"
port = 5432
shard = 0
pool_size = 35
min_pool_size = 5

Environment

• PgDog v0.1.20 (in Kubernetes)
• PostgreSQL 15.10.0
• Sequelize ORM (v6)
• Multiple PgDog instances, all connecting to the same single Postgres instance
• Moderate, real-world web application concurrency

[mkreidenweis-schulmngr]

Something else, that might be related: We've (very rarely) seen these new errors in our PostgreSQL log, what we didn't see in the past few months, before deploying PgDog:

WARNING:  there is no transaction in progress

WARNING:  SET TRANSACTION can only be used in transaction blocks

[levkk]

This could be an artifact of transaction pooling and the client setting transaction isolation settings expecting them to be preserved between queries, but PgDog multiplexes servers and clients. For example, the INSERT query runs on server connection 0, while the SELECT / UPDATE will run on server connection 1, with potentially different transaction serialization settings.

Everything works inside a transaction because PgDog keeps the same connection tied to the client until the client sends COMMIT. Postgres transactions guarantee a consistent view of the database in that scenario, incl. FK constraints.

I want to confirm something.

You said you don't have any replicas. By default, this means our query parser is disabled so we aren't handling SET commands. If you enable it, by setting query_parser_enabled = true, do you see an improvement? We would be intercepting all SET commands, preserving session state for each client, including transaction serialization settings.

Before doing this in prod, bump up the memory PgDog is allowed to allocate (I'm assuming you're using the default 1CPU;1GB from our helm chart, which normally should be fine, but I don't know enough about your queries to be sure), since we'll be caching queries in memory to avoid calling the SQL parser repeatedly.