Обсуждение: pgpool-II: cannot use serializable mode in a hot standby
Hi. We have application which explicitly does set default_transaction_isolation to 'serializable' . It is connected to PostgreSQL master/slave cluster through pgpool-II (pgpool2 3.4.3-1.pgdg70+1). Our logs are filling with ERROR: 0A000: cannot use serializable mode in a hot standby DETAIL: "default_transaction_isolation" is set to "serializable". HINT: You can use "SET default_transaction_isolation = 'repeatable read'" to change the default. db netstat, client 10.160.1.38, user nf2pg LOCATION: GetSerializableTransactionSnapshot, predicate.c:1588 STATEMENT: DISCARD ALL It seems pgpool sends these statements to the slave server. Is it pgpool/application misconfiguration or pgpool issue? -- Best regards, Alexander Pyhalov, system administrator of Southern Federal University IT department
On 04/12/2016 01:06 AM, Alexander Pyhalov wrote: > Hi. > > We have application which explicitly does > set default_transaction_isolation to 'serializable' . > It is connected to PostgreSQL master/slave cluster through pgpool-II > (pgpool2 3.4.3-1.pgdg70+1). Our logs are filling with > > ERROR: 0A000: cannot use serializable mode in a hot standby DETAIL: > "default_transaction_isolation" is set to "serializable". HINT: You can > use "SET default_transaction_isolation = 'repeatable read'" to change > the default. db netstat, client 10.160.1.38, user nf2pg LOCATION: > GetSerializableTransactionSnapshot, predicate.c:1588 STATEMENT: DISCARD ALL > > It seems pgpool sends these statements to the slave server. Is it > pgpool/application misconfiguration or pgpool issue? > I would say the above is coming from Postgres not pgpool: http://www.postgresql.org/docs/9.5/interactive/hot-standby.html "The Serializable transaction isolation level is not yet available in hot standby. (See Section 13.2.3 and Section 13.4.1 for details.) An attempt to set a transaction to the serializable isolation level in hot standby mode will generate an error." http://www.postgresql.org/docs/9.5/interactive/applevel-consistency.html#SERIALIZABLE-CONSISTENCY "Warning This level of integrity protection using Serializable transactions does not yet extend to hot standby mode (Section 25.5). Because of that, those using hot standby may want to use Repeatable Read and explicit locking on the master." -- Adrian Klaver adrian.klaver@aklaver.com
On 04/12/2016 16:50, Adrian Klaver wrote: > On 04/12/2016 01:06 AM, Alexander Pyhalov wrote: >> Hi. >> >> We have application which explicitly does >> set default_transaction_isolation to 'serializable' . >> It is connected to PostgreSQL master/slave cluster through pgpool-II >> (pgpool2 3.4.3-1.pgdg70+1). Our logs are filling with >> >> ERROR: 0A000: cannot use serializable mode in a hot standby DETAIL: >> "default_transaction_isolation" is set to "serializable". HINT: You can >> use "SET default_transaction_isolation = 'repeatable read'" to change >> the default. db netstat, client 10.160.1.38, user nf2pg LOCATION: >> GetSerializableTransactionSnapshot, predicate.c:1588 STATEMENT: >> DISCARD ALL >> >> It seems pgpool sends these statements to the slave server. Is it >> pgpool/application misconfiguration or pgpool issue? >> > > I would say the above is coming from Postgres not pgpool: I understand. I mean perhaps pgpool shouldn't forward these statements to slaves. -- Best regards, Alexander Pyhalov, system administrator of Southern Federal University IT department
On 04/12/2016 07:02 AM, Alexander Pyhalov wrote: > On 04/12/2016 16:50, Adrian Klaver wrote: >> On 04/12/2016 01:06 AM, Alexander Pyhalov wrote: >>> Hi. >>> >>> We have application which explicitly does >>> set default_transaction_isolation to 'serializable' . >>> It is connected to PostgreSQL master/slave cluster through pgpool-II >>> (pgpool2 3.4.3-1.pgdg70+1). Our logs are filling with >>> >>> ERROR: 0A000: cannot use serializable mode in a hot standby DETAIL: >>> "default_transaction_isolation" is set to "serializable". HINT: You can >>> use "SET default_transaction_isolation = 'repeatable read'" to change >>> the default. db netstat, client 10.160.1.38, user nf2pg LOCATION: >>> GetSerializableTransactionSnapshot, predicate.c:1588 STATEMENT: >>> DISCARD ALL >>> >>> It seems pgpool sends these statements to the slave server. Is it >>> pgpool/application misconfiguration or pgpool issue? >>> >> >> I would say the above is coming from Postgres not pgpool: > > I understand. I mean perhaps pgpool shouldn't forward these statements > to slaves. > That is probably best handled here: http://www.pgpool.net/mailman/listinfo/pgpool-general or here: http://pgpool.net/mediawiki/index.php/Bug_tracking_system -- Adrian Klaver adrian.klaver@aklaver.com
> On 04/12/2016 16:50, Adrian Klaver wrote: >> On 04/12/2016 01:06 AM, Alexander Pyhalov wrote: >>> Hi. >>> >>> We have application which explicitly does >>> set default_transaction_isolation to 'serializable' . >>> It is connected to PostgreSQL master/slave cluster through pgpool-II >>> (pgpool2 3.4.3-1.pgdg70+1). Our logs are filling with >>> >>> ERROR: 0A000: cannot use serializable mode in a hot standby DETAIL: >>> "default_transaction_isolation" is set to "serializable". HINT: You >>> can >>> use "SET default_transaction_isolation = 'repeatable read'" to change >>> the default. db netstat, client 10.160.1.38, user nf2pg LOCATION: >>> GetSerializableTransactionSnapshot, predicate.c:1588 STATEMENT: >>> DISCARD ALL >>> >>> It seems pgpool sends these statements to the slave server. Is it >>> pgpool/application misconfiguration or pgpool issue? >>> >> >> I would say the above is coming from Postgres not pgpool: > > I understand. I mean perhaps pgpool shouldn't forward these statements > to slaves. Yeah, PostgreSQL used to accept the command on standbys (at least in 9.0). The restriction was added later on. It woule be nice if you send a bug report to the pgpool-II bug tracker to not forget it. http://pgpool.net/mediawiki/index.php/Bug_tracking_system Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp
Tatsuo Ishii писал 13.04.2016 02:36: >> On 04/12/2016 16:50, Adrian Klaver wrote: >>> On 04/12/2016 01:06 AM, Alexander Pyhalov wrote: >> I understand. I mean perhaps pgpool shouldn't forward these statements >> to slaves. > > Yeah, PostgreSQL used to accept the command on standbys (at least in > 9.0). The restriction was added later on. It woule be nice if you send > a bug report to the pgpool-II bug tracker to not forget it. > > http://pgpool.net/mediawiki/index.php/Bug_tracking_system Filed http://www.pgpool.net/mantisbt/view.php?id=191 --- System Administrator of Southern Federal University Computer Center
On Wed, Apr 13, 2016 at 1:30 AM, Alexander Pyhalov <alp@rsu.ru> wrote: > Tatsuo Ishii писал 13.04.2016 02:36: >>> >>> On 04/12/2016 16:50, Adrian Klaver wrote: >>>> >>>> On 04/12/2016 01:06 AM, Alexander Pyhalov wrote: >>> >>> I understand. I mean perhaps pgpool shouldn't forward these statements >>> to slaves. It should not forward transactions which are requested to be SERIALIZABLE to standbys. If you just suppress the SET statement (or substitute REPEATABLE READ), queries in that transaction can return incorrect results. >> Yeah, PostgreSQL used to accept the command on standbys (at least in >> 9.0). The restriction was added later on. ... in 9.1, for a reason. >> It woule be nice if you send >> a bug report to the pgpool-II bug tracker to not forget it. >> >> http://pgpool.net/mediawiki/index.php/Bug_tracking_system > > Filed http://www.pgpool.net/mantisbt/view.php?id=191 As the entry stands at the moment, the suggestions for fixes will allow incorrect query results. See this example, and imagine that the transaction generating the list of receipts for the closed batch is run on the standby before the transaction adding the last receipt commits. Or test it. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Wed, Apr 13, 2016 at 5:54 PM, Kevin Grittner <kgrittn@gmail.com> wrote: > See this example, and imagine that > the transaction generating the list of receipts for the closed > batch is run on the standby before the transaction adding the last > receipt commits. Or test it. https://wiki.postgresql.org/wiki/SSI#Deposit_Report -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
> It should not forward transactions which are requested to be > SERIALIZABLE to standbys. If you just suppress the SET statement > (or substitute REPEATABLE READ), queries in that transaction can > return incorrect results. Yes. Once "SET default_transaction_isolation to serializable" (or its variants) are issued, pgpool-II will redirect all subsequent queries to the primary server until the transaction ends. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp