Re: Libpq support to connect to standby server as priority

From: Haribabu Kommi <kommi(dot)haribabu(at)gmail(dot)com>
To: laurenz(dot)albe(at)cybertec(dot)at
Cc: Jing Wang <jingwangian(at)gmail(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>, "Tsunakawa, Takayuki" <tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com>
Subject: Re: Libpq support to connect to standby server as priority
Date: 2018-07-11 08:00:37
Message-ID: CAJrrPGc2JCp9SAg0-vmwN3jEBn-oYKKu=896Bb9itv8_Jq7MwA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

On Wed, Jul 4, 2018 at 11:14 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

> Haribabu Kommi wrote:
> > On Wed, Jan 24, 2018 at 9:01 AM Jing Wang <jingwangian(at)gmail(dot)com> wrote:
> > > Hi All,
> > >
> > > Recently I put a proposal to support 'prefer-read' parameter in
> target_session_attrs in libpq. Now I updated the patch with adding content
> in the sgml and regression test case.
> > >
> > > Some people may have noticed there is already another patch (
> https://commitfest.postgresql.org/15/1148/ ) which looks similar with
> this. But I would say this patch is more complex than my proposal.
> > >
> > > It is better separate these 2 patches to consider.
> >
> > I also feel prefer-read and read-only options needs to take as two
> different options.
> > prefer-read is simple to support than read-only.
> >
> > Here I attached an updated patch that is rebased to the latest master
> and also
> > fixed some of the corner scenarios.
>

Thanks for the review.

> The patch applies, builds and passes "make check-world".
>
> I think the "prefer-read" functionality is desirable: It is exactly what
> you need
> if you want to use replication for load balancing, and your application
> supports
> different database connections for reading and writing queries.
>
> "read-only" does not have a clear use case in my opinion.
>
> With the patch, PostgreSQL behaves as expected if I have a primary and a
> standby and run:
>
> psql "host=/tmp,/tmp port=5433,5434 target_session_attrs=prefer-read"
>
> But if I stop the standby (port 5434), libpq goes into an endless loop.
>

There was a problem in reusing the primary host index and it leads to loop.
Attached patch fixed the issue.

> Concerning the code:
>
> - The documentation needs some attention. Suggestion:
>
> If this parameter is set to <literal>prefer-read</literal>, connections
> where <literal>SHOW transaction_read_only</literal> returns off are
> preferred.
> If no such connection can be found, a connection that allows read-write
> transactions will be accepted.
>

updated as per you comment.

> - I think the construction with "read_write_host_index" makes the code
> even more
> complicated than it already is.
>
> What about keeping the first successful connection open and storing it
> in a
> variable if we are in "prefer-read" mode.
> If we get the read-only connection we desire, close that cached
> connection,
> otherwise use it.
>

Even if we add a variable to cache the connection, I don't think the logic
of checking
the next host for the read-only host logic may not change, but the extra
connection
request to the read-write host again will be removed.

Regards,
Haribabu Kommi
Fujitsu Australia

Attachment Content-Type Size
0001-Allow-taget-session-attrs-to-accept-prefer-read-opti_v2.patch application/octet-stream 13.7 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Heikki Linnakangas 2018-07-11 08:10:14 Re: Excessive PostmasterIsAlive calls slow down WAL redo
Previous Message Taiki Kondo 2018-07-11 07:58:26 RE: Typo in Japanese translation of psql.