Error when not declaring primary for read only DB

[sandangel]

For read only DB, I only declare database with replica role, then got an error:

no primary

[levkk]

Makes sense if you tried to send a write query. Do you have a log of what query you ran?

Edit: Are you using sharding or just read/write split?

[levkk]

I think it's the SET query again. Adding fix in #161.

[sandangel]

Hi, I tried adding primary to the read only db (bar) as well in case my app may issue a write request, now both foo and bar db have primary and replica. However when I pull the latest commit, I get the error again:

cannot execute INSERT in a read-only transaction

# https://github.com/pgdogdev/pgdog/blob/main/pgdog.toml
[general]
host = "0.0.0.0"
port = 6432
shutdown_timeout = 5_000
openmetrics_port = 9930
query_timeout = 1_000
checkout_timeout = 1_000
connect_timeout = 1_000
idle_timeout = 30_000
prepared_statements = "extended"
passthrough_auth = "enabled_plain"
default_pool_size = 100
workers = 10
min_pool_size = 1
pooler_mode = "transaction"
load_balancing_strategy = "least_active_connections"
auth_type = "trust"

[admin]
user = "pgdog"
password = "pgdog"

[[databases]]
name = "postgres"
host = "localhost"
role = "primary"
port = 5432

[[databases]]
name = "foo"
host = "localhost"
role = "primary"
port = 5432

[[databases]]
name = "foo"
host = "localhost"
port = 5433
role = "replica"

[[databases]]
name = "bar"
host = "localhost"
role = "replica"
port = 5433

[[databases]]
name = "bar"
host = "localhost"
role = "primary"        # <===================== adding primary for bar
port = 5432

[tcp]
retries = 3
time = 1000
interval = 1000
user_timeout = 1000

[levkk]

Sounds good, I'm going to repro on my side and provide a fix.

[levkk]

This is what's going on I suspect:

https://github.com/pgdogdev/pgdog/pull/163/files#diff-7bdb0f51abe23c4391bfaee80662402016b513c171a45df64f83740a03a3021dR154-R161

The ORM starts a read query then, inside the same transaction, a write query. PgDog routes transactions, not individual queries, so the transaction fails.

Another thing we could try to confirm is enable query_log and share the queries you're actually running:

[general]
query_log = "queries.log"

After running your test, if you're still getting errors, could you share the queries.log file with me, so I can see what are the sequence of queries that trigger the error exactly? Also output with RUST_LOG=debug would be super helpful. Query logger only runs in debug mode, so make sure to run pgdog with cargo run, not cargo run --release.

Thanks!

[levkk]

@sandangel Try the latest commit and set:

[general]
read_write_strategy = "conservative"

This should route all explicit transactions to the primary.

[levkk]

Ok finally I think I understand the root cause here. It's the async protocol again. I think I got it this time in #164 .

[sandangel]

get a new error now. where as pgpool or pgcat just work.

sqlalchemy.exc.DBAPIError: (sqlalchemy.dialects.postgresql.asyncpg.Error) <class 'asyncpg.exceptions.ConnectionDoesNotExistError'>: connection was closed in the middle of operation

[levkk]

Alright, I'll keep digging. Thanks for reporting this! It would be really helpful if you could share the log output from pgdog so I can see the actual error that took place.