Environment
PgDog: 0.1.40
PostgreSQL: 18.0 (primary + N hot standbys, Patroni-managed)
Pooler mode: transaction
Auth: passthrough_auth = "enabled_plain", auth_type = "scram"
In a multi-node deployment (1 primary + N replicas tagged via role="replica"), PgDog load-balances SELECT statements across replicas. This works great for normal tables, but PostgreSQL UNLOGGED tables are not replicated, their contents only exist on the node where the write happened.
Today there is no way to tell PgDog "for this table / this query / this session, never route to a replica". The result is that a write to an unlogged table on the primary followed by a read in a later transaction returns empty data (or fails outright when the planner tries to access the relation in recovery on the standby).
This isn't an UNLOGGED-only issue : it applies to any database object whose contents differ between primary and replicas: per-node TEMP tables created by sessions that survive a connection check-out, foreign tables that point to local-only resources, etc. - but UNLOGGED is the most common in practice.
Suggested Solution (maybe) Allow per statement/transaction sql hint to tell pgdog this is "primary only" select statement so user can force it with unlogged queries. This is what some other poolers do
Environment
PgDog: 0.1.40
PostgreSQL: 18.0 (primary + N hot standbys, Patroni-managed)
Pooler mode: transaction
Auth: passthrough_auth = "enabled_plain", auth_type = "scram"
In a multi-node deployment (1 primary + N replicas tagged via role="replica"), PgDog load-balances SELECT statements across replicas. This works great for normal tables, but PostgreSQL UNLOGGED tables are not replicated, their contents only exist on the node where the write happened.
Today there is no way to tell PgDog "for this table / this query / this session, never route to a replica". The result is that a write to an unlogged table on the primary followed by a read in a later transaction returns empty data (or fails outright when the planner tries to access the relation in recovery on the standby).
This isn't an UNLOGGED-only issue : it applies to any database object whose contents differ between primary and replicas: per-node TEMP tables created by sessions that survive a connection check-out, foreign tables that point to local-only resources, etc. - but UNLOGGED is the most common in practice.
Suggested Solution (maybe) Allow per statement/transaction sql hint to tell pgdog this is "primary only" select statement so user can force it with unlogged queries. This is what some other poolers do