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

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(dot)klaver(at)aklaver(dot)com