Re: Libpq support to connect to standby server as priority

From: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
To: "Tsunakawa, Takayuki" <tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com>, 'Tom Lane' <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Dave Cramer <pg(at)fastcrypt(dot)com>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Haribabu Kommi <kommi(dot)haribabu(at)gmail(dot)com>, Jing Wang <jingwangian(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Libpq support to connect to standby server as priority
Date: 2019-01-18 08:27:04
Message-ID: 0621b594bf61d457775c0b9042d0d2f0495d9e58.camel@cybertec.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Tsunakawa, Takayuki wrote:
> From: Laurenz Albe [mailto:laurenz(dot)albe(at)cybertec(dot)at]
> > I think that transaction_read_only is good.
> >
> > If it is set to false, we are sure to be on a replication primary or
> > stand-alone server, which is enough to know for the load balancing use case.
>
> As Tatsuo-san said, setting default_transaction_read_only leads to a misjudgement of the primary.

Yes, you can have a false negative, i.e. fail to recognize a primary server.

> > I deem it unlikely that someone will set default_transaction_read_only to
> > FALSE and then complain that the feature is not working as expected, but
> > again
> > I cannot prove that claim.
>
> I wonder what default_transaction_read_only exists for. For maing the database by default
> and allowing only specific users to write to the database with "CREATE/ALTER USER SET
> default_transaction_read_only = false"?

I'd guess that the main use of default_transaction_read_only is to make sure an
application (that isn't smart enough to change the parameter) won't modify any data.

> I'm sorry to repeat myself, but anyway, I think we need a method to connect to a standby
> as the original desire, because the primary instance may be read only by default while
> only limited users update data. That's for reducing the burdon on the primary and
> minimizing the impact on users who update data. For example,
>
> * run data reporting on the standby
> * backup the database from the standby
> * cascade replication from the standby

I see.

But then the new value should not be called "prefer-read", because that would be
misleading. It would also not be related to the existing "read-write".

For what you have in mind, there should be the options "primary-required" and
"standby-preferred", however we implement them.

Have there been a lot of complaints that the existing "read-write" is not good
enough to detect replication primaries?

> > As Robert said, transaction_read_only might even give you the option to
> > use the feature for more than just load balancing between replication master
> > and standby.
>
> What use case do you think of? If you want to load balance the workload between
> the primary and standbys, we can follow PgJDBC -- targetServerType=any.

One use case I can think of is logical replication (or other replication methods like
Slony). You can use the feature by setting default_transaction_read_only = on
on the standby.

Yours,
Laurenz Albe

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Kyotaro HORIGUCHI 2019-01-18 08:33:30 Re: Protect syscache from bloating with negative cache entries
Previous Message Fabien COELHO 2019-01-18 08:26:49 Re: PSA: we lack TAP test coverage on NetBSD and OpenBSD