RE: Libpq support to connect to standby server as priority

From: Haribabu Kommi [mailto:kommi.haribabu@gmail.com]
> IMO, if we try to use only pg_is_in_recovery() only to decide to connect,
> we may not
> support all the target_session_attrs that are possible. how about using
> both to decide?

I favor adding a new parameter like target_server_type whose purpose is to select the server role.  That aligns better with the PgJDBC's naming, which conveys consistency to PostgreSQL users.  Again, the original desire should have been to connect to a standby to eliminate the burdon on the primary, where the primary may be read-only by default and only a limited group of users are allowed to write to the database.

I don't know what kind of realistic use cases there are that request read-only session in the logical replication configuration.  I think we can just leave target_session_attrs as what it is now in PostgreSQL 11, for compatibility and possibly future new use cases.


> Master/read-write -- recovery = false and default_transaction_read_only
> = false
> Standby/read-only -- recovery = true
> prefer-standby/prefer-read -- recovery = true or
> default_transaction_read_only = true
> any -- Nothing to be verified
> 
> 
> I feel above verifications can cover for both physical and logical
> replication.

As for prefer-standby/prefer-read, if host parameter specifies host1,host2 in this order, and host1 is the primary with default_transaction_read_only=true, does the app get a connection to host1?  I want to connect to host2 (standby) only if host1 is down.


Regards
Takayuki Tsunakawa