Re: [GENERAL] pgpool-II: cannot use serializable mode in a hot standby

2016-04-13 Thread Tatsuo Ishii 
> 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

Re: [GENERAL] pgpool-II: cannot use serializable mode in a hot standby

2016-04-13 Thread Kevin Grittner 
On Wed, Apr 13, 2016 at 5:54 PM, Kevin Grittner  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

Re: [GENERAL] pgpool-II: cannot use serializable mode in a hot standby

2016-04-13 Thread Kevin Grittner 
On Wed, Apr 13, 2016 at 1:30 AM, Alexander Pyhalov  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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool-II: cannot use serializable mode in a hot standby

2016-04-13 Thread Alexander Pyhalov 

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



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool-II: cannot use serializable mode in a hot standby

2016-04-12 Thread Tatsuo Ishii 
> 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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool-II: cannot use serializable mode in a hot standby

2016-04-12 Thread Adrian Klaver 

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.kla...@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

Re: [GENERAL] pgpool-II: cannot use serializable mode in a hot standby

2016-04-12 Thread Alexander Pyhalov 

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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool-II: cannot use serializable mode in a hot standby

2016-04-12 Thread Adrian Klaver 

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.kla...@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