RE: Libpq support to connect to standby server as priority

From: Haribabu Kommi [mailto:kommi(dot)haribabu(at)gmail(dot)com]
> target_session_attrs checks for the default_transaction_readonly or not?

PG 11 uses transaction_read_only, not default_transaction_readonly. That's fine, because its purpose is to get a read-only session as the name suggests, not to connect to a standby.

> target_server_type checks for whether the server is in recovery or not?

Yes.

> I feel having two options make this feature complex to use it from the user
> point of view?
>
> The need of two options came because of a possibility of a master server
> with default_transaction_readonly set to true. Even if the default
> transaction
> is readonly, it is user changeable parameter, so there shouldn't be any
> problem.

No. It's not good if the user has to be bothered by default_transaction_read_only when he simply wants to a standby.

> how about just adding one parameter that takes the options similar like
> JDBC?
> target_server_type - Master, standby and prefer-standby. (The option names
> can revised based on the common words on the postgresql docs?)

"Getting a read-only session" is not equal to "connecting to a standby", so two different parameters make sense.

> And one more thing, what happens when the server promotes to master but
> the connection requested is standby? I feel we can maintain the existing
> connections
> and later new connections can be redirected? comments?

Ideally, it should be possible for the user to choose the behavior like Oracle below. But that's a separate feature.

9.2 Role Transitions Involving Physical Standby Databases
https://docs.oracle.com/en/database/oracle/oracle-database/18/sbydb/managing-oracle-data-guard-role-transitions.html#GUID-857F6F45-DC1C-4345-BD39-F3BE7D79F1CD
--------------------------------------------------
Keeping Physical Standby Sessions Connected During Role Transition

As of Oracle Database 12c Release 2 (12.2.0.1), when a physical standby database is converted into a primary you have the option to keep any sessions connected to the physical standby connected, without disruption, during the switchover/failover.

To enable this feature, set the STANDBY_DB_PRESERVE_STATES initialization parameter in your init.ora file before the standby instance is started. This parameter applies to physical standby databases only. The allowed values are:

NONE — No sessions on the standby are retained during a switchover/failover. This is the default value.

ALL — User sessions are retained during switchover/failover.

SESSION — User sessions are retained during switchover/failover.
--------------------------------------------------

Would you like to work on this patch? I'm not sure if I can take time, but I'm willing to do it if you don't have enough time.

As Tom mentioned, we need to integrate and clean patches in three mail threads:

* Make a new GUC_REPORT parameter, server_type, to show the server role (primary or standby).
* Add target_server_type libpq connection parameter, whose values are either primary, standby, or prefer_standby.
* Failover timeout, load balancing, etc. that someone proposed in the other thread?

(I wonder which of server_type or server_role feels natural in English.)

Or, would you like to share the work, e.g., libpq and server-side?

Regards
Takayuki Tsunakawa