Re: Libpq support to connect to standby server as priority

On Fri, Feb 8, 2019 at 8:16 PM Tsunakawa, Takayuki <
tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com> wrote:

> 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.
>

Thanks for correction, yes it uses the transaction_readonly.

> > 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.
>

OK. Understood.
so if we are going to differentiate between readonly and standby types,
then I still
feel that adding a prefer-read to target_session_attrs is still valid
improvement.

But the above improvement can be enhanced once the base work of GUC_REPORT
is finished.

> > 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.
> --------------------------------------------------
>

Yes, the above feature is completely a different role enhancement feature,
that can taken up separately.

> 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?
>

Yes, I want to work on this patch, hopefully by next commitfest. In case if
I didn't get time,
I can ask for your help.

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

server_type may be good as it stands with connection option
(target_server_type).

Regards,
Haribabu Kommi
Fujitsu Australia