Thread: Re: [HACKERS] Patch: Implement failover on libpq connect level.
[moving this branch of discussion to pgsql-jdbc] On Tue, Nov 15, 2016 at 10:31 PM, Mithun Cy <mithun.cy@enterprisedb.com> wrote: > JDBC is sending "show transaction_read_only" to find whether it > is master or not. If true, that's insane. That can be different on each connection to the cluster and can change tens of thousands of times per second on any connection! I know of one large shop that sets default_transaction_read_only = true because 99% of their transactions are read only and they use serializable transactions -- which run faster and with less contention when transactions which don't need to write are flagged as read only. It seems safer to them to only turn off the read only property for transactions which might need to write. > Victor's patch also started with it, but later it was transformed into > pg_is_in_recovery > by him as it appeared more appropriate to identify the master / slave. I don't know whether that is ideal, but it is sure a lot better than something which can change with every transaction -- or even within a transaction (in one direction). -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Wed, Nov 16, 2016 at 9:02 AM, Kevin Grittner <kgrittn@gmail.com> wrote: > [moving this branch of discussion to pgsql-jdbc] > > On Tue, Nov 15, 2016 at 10:31 PM, Mithun Cy <mithun.cy@enterprisedb.com> wrote: > >> JDBC is sending "show transaction_read_only" to find whether it >> is master or not. > > If true, that's insane. That can be different on each connection > to the cluster and can change tens of thousands of times per second > on any connection! I don't think that's insane. The command is only being issued at connection startup, and will only be different on different connections if it's been configured that way. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Wed, Nov 16, 2016 at 9:02 AM, Kevin Grittner <kgrittn@gmail.com> wrote: > [moving this branch of discussion to pgsql-jdbc] > > On Tue, Nov 15, 2016 at 10:31 PM, Mithun Cy <mithun.cy@enterprisedb.com> wrote: > >> JDBC is sending "show transaction_read_only" to find whether it >> is master or not. > > If true, that's insane. That can be different on each connection > to the cluster and can change tens of thousands of times per second > on any connection! I don't think that's insane. The command is only being issued at connection startup, and will only be different on different connections if it's been configured that way. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 16 November 2016 at 23:13, Robert Haas <robertmhaas@gmail.com> wrote: > On Wed, Nov 16, 2016 at 9:02 AM, Kevin Grittner <kgrittn@gmail.com> wrote: >> [moving this branch of discussion to pgsql-jdbc] >> >> On Tue, Nov 15, 2016 at 10:31 PM, Mithun Cy <mithun.cy@enterprisedb.com> wrote: >> >>> JDBC is sending "show transaction_read_only" to find whether it >>> is master or not. >> >> If true, that's insane. That can be different on each connection >> to the cluster and can change tens of thousands of times per second >> on any connection! > > I don't think that's insane. The command is only being issued at > connection startup, and will only be different on different > connections if it's been configured that way. I agree. However, I also think we should make sure add a GUC_REPORT var in v10 that lets a client tell whether it's connected to a read/write or read-only node right from the start. This will save an unnecessary round-trip and some annoying log-spam. I'd rather not bind it directly to "in recovery" because we're likely to want to support read-only logical replication nodes in future, but even that would be OK. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
From: pgsql-jdbc-owner@postgresql.org > [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Craig Ringer > >> If true, that's insane. That can be different on each connection to > >> the cluster and can change tens of thousands of times per second on > >> any connection! > > > > I don't think that's insane. The command is only being issued at > > connection startup, and will only be different on different > > connections if it's been configured that way. > > I agree. However, I also think we should make sure add a GUC_REPORT var > in v10 that lets a client tell whether it's connected to a read/write or > read-only node right from the start. This will save an unnecessary > round-trip and some annoying log-spam. Yes, I meant the GUC_REPORT method. I don't think something like read-only and read/write is intuitive as values valuesof target_server_type, because people who want to use reporting apps that use temporary tables have to connect to theprimary, because temp tables are not supported on standbys. I think the current notion of primary/standby is good; thosewho require temp tables need to specify primary. > I'd rather not bind it directly to "in recovery" because we're likely to > want to support read-only logical replication nodes in future, but even > that would be OK. Maybe we should determine the name and value of the connection parameter and GUC-REPORTed variable in light of the logicalreplication. Regards Takayuki Tsunakawa
On 16 November 2016 at 23:13, Robert Haas <robertmhaas@gmail.com> wrote: > On Wed, Nov 16, 2016 at 9:02 AM, Kevin Grittner <kgrittn@gmail.com> wrote: >> [moving this branch of discussion to pgsql-jdbc] >> >> On Tue, Nov 15, 2016 at 10:31 PM, Mithun Cy <mithun.cy@enterprisedb.com> wrote: >> >>> JDBC is sending "show transaction_read_only" to find whether it >>> is master or not. >> >> If true, that's insane. That can be different on each connection >> to the cluster and can change tens of thousands of times per second >> on any connection! > > I don't think that's insane. The command is only being issued at > connection startup, and will only be different on different > connections if it's been configured that way. I agree. However, I also think we should make sure add a GUC_REPORT var in v10 that lets a client tell whether it's connected to a read/write or read-only node right from the start. This will save an unnecessary round-trip and some annoying log-spam. I'd rather not bind it directly to "in recovery" because we're likely to want to support read-only logical replication nodes in future, but even that would be OK. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
From: pgsql-jdbc-owner@postgresql.org > [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Craig Ringer > >> If true, that's insane. That can be different on each connection to > >> the cluster and can change tens of thousands of times per second on > >> any connection! > > > > I don't think that's insane. The command is only being issued at > > connection startup, and will only be different on different > > connections if it's been configured that way. > > I agree. However, I also think we should make sure add a GUC_REPORT var > in v10 that lets a client tell whether it's connected to a read/write or > read-only node right from the start. This will save an unnecessary > round-trip and some annoying log-spam. Yes, I meant the GUC_REPORT method. I don't think something like read-only and read/write is intuitive as values valuesof target_server_type, because people who want to use reporting apps that use temporary tables have to connect to theprimary, because temp tables are not supported on standbys. I think the current notion of primary/standby is good; thosewho require temp tables need to specify primary. > I'd rather not bind it directly to "in recovery" because we're likely to > want to support read-only logical replication nodes in future, but even > that would be OK. Maybe we should determine the name and value of the connection parameter and GUC-REPORTed variable in light of the logicalreplication. Regards Takayuki Tsunakawa