Issue with auto failover with unresponsive host

[lz000]

We have 2 DB instances: a primary instance host1:5432 and a read replica host2:5432

Here is my config:

client = postgres({
            host: "host1:5432,host2:5432",
            database: "postgres",
            username: "",
            password: "",
            target_session_attrs: "prefer-standby",
}

What I found is that it does not auto failover to the primary when the read replica can not be connected (Host exist but not respond). It will just print out error write CONNECT_TIMEOUT host2:5432 and stuck

same issue with primary

client = postgres({
            host: "unresponsive-host:5432,host1:5432",
            database: "postgres",
            username: "",
            password: "",
            target_session_attrs: "primary",
}

it will suck on error write CONNECT_TIMEOUT unresponsive-host:5432

Auto failover seems only work if host does not exist or host refuse connection

[sfrench-sds]

We're experiencing similar issues attempting to target read-write. It almost seems as if target_session_attrs is being ignored because no matter what, the first host listed is always targeted regardless of that host being in read-only or read-write state. We have to switch the order of the two values manually for the switch to occur. We're using Postgres.js 3.4.3 and Postgres 16.2.

[MaxSamsonov]

It really looks like target_session_attrs: 'read-write' is ignored if 1st host is not primary: PostgresError: cannot execute INSERT in a read-only transaction.

But target_session_attrs: 'primary' seems to be working as expected.