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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 withERROR: 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 ALLIt 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 withERROR: 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 ALLIt 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 withERROR: 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 ALLIt 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 withERROR: 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 ALLIt 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
Filed http://www.pgpool.net/mantisbt/view.php?id=191
---
System Administrator of Southern Federal University Computer Center
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general