primary_reads_enabled doesn't behave as expected when pool_mode = "session"

[osawyerr]

Describe the bug When primary_reads_enabled = false and pool_mode = "session" if a psql client connects to the primary node (via pgcat) on first connection, it ignores primary_reads_enabled = false. i.e. select queries are always sent from the primary node and never the replica.

To Reproduce

[pools.simple_db]
pool_mode = "session"
default_role = "replica"
query_parser_enabled = true
primary_reads_enabled = false
sharding_function = "pg_bigint_hash"

[pools.simple_db.shards.0]
servers = [
    [ "127.0.0.1", 5432, "primary" ],
    [ "localhost", 5431, "replica" ]
]

Expected behavior One would expect that the inserts go to the primary while the selects go to the replicas.

[osawyerr]

Yeah it appears that the when primary_reads_enabled = false and pool_mode = "session". The behaviour is not as expected. primary_reads_enabled = false seems to be completely ignored.

When not executing statements as Admin, could this be because in the Client, the Server is setup before we call infer() which could change the details of which Server the Client should be pointing to (i.e. it could change the role)?

[levkk]

Try pool_mode = "transaction". In session mode, the client is bound to the first server it talks to for the duration of the client connection.

[osawyerr]

Yes, pool_mode = "transaction" works but doesn't allow for our interactivity use case.

My ticket is describing that pool_mode = "session" doesn't seem to work as expected. If its bound to the first server for the duration of the connection, it could cause writes to occur (and fail) on the replicas. We've seen this happen before as well. i.e. when pool_mode = "session" pgGat sometimes tries to write to the replica, which then fails because our replicas are read only. This doesn't seem like the desired behaviour.