[pgpool-general: 310] Re: Transaction replicated to streaming slave

Bill W pgpool at aastral.net
Tue Mar 27 14:41:25 JST 2012


Hello Tatsuo,

Thanks for the reply!

Actually the the client is an ODBC driver, which explains the age.
application -> odbc -> pgpool -> postgres.

I don't quite understand why a 'begin' would ever be sent to the
secondary server. Should't pgpool see the 'begin' and assume that
anything important enough to be in a transaction should go to the primary?

Anyway, is there anything I can do at all with pgpool to force this to
go to the primary server?

I tried
backend_weight0 = 1000
backend_weight1 = 0
But that doesn't work.

I don't know how much luck I'll have in being able to change the sql in
the application.  I can try to ask them to patch it but that patch may
or may not be applied.

Thanks,
Bill



On 3/27/12 1:07 AM, Tatsuo Ishii wrote:
> Pgpool-II does not support multi statement query.
> 
> begin;delete from channels where hostname='';delete from channels where hostname='';commit;
> 
> So pgpool only analyzes the first component of the multi statement
> query. i.e. "begin". This is a known limitation and clearly stated in
> pgpool's document.
> 
> Also it seems you use very old client. I notice this from the log:
> 
>> 2012-03-26 20:07:08 DEBUG: pid 19119: pool_read_string: read all from
> 
> pool_read_string is only called if client use old version 2 protocol,
> which was used by PostgreSQL 7.3 or older. I recommend to use more
> modern client software.
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese: http://www.sraoss.co.jp
> 
>> Hello everyone,
>>
>>
>> I'm finding that pgpool is replicating transactions to the streaming
>> slave, even though I believe I've configured pgpool to only communicate
>> with the master.
>>
>> I've verified the log on the slave, and it is indeed receiving that
>> transaction;
>>
>> If I shut down the slave and re-run the query, everything proceeds normally.
>>
>> Does anyone have any advice?
>>
>> Thanks,
>> Bill
>>
>> pgpool-II-3.1.2
>> postgresql-server-9.1.1
>>
>> Log:
>> --------------
>> 2012-03-26 20:07:08 DEBUG: pid 19119: statement2: begin;delete from
>> channels where hostname='';delete from channels where hostname='';commit;
>>
>> 2012-03-26 20:07:08 DEBUG: pid 19119: pool_set_query_in_progress: done
>>
>> 2012-03-26 20:07:08 DEBUG: pid 19119: send_to_where: 3 query:
>> begin;delete from channels where hostname='';delete from channels where
>> hostname='';commit;
>>
>> 2012-03-26 20:07:08 DEBUG: pid 19119: wait_for_query_response: waiting
>> for backend 0 completing the query
>>
>> 2012-03-26 20:07:08 DEBUG: pid 19119: wait_for_query_response: waiting
>> for backend 1 completing the query
>>
>> 2012-03-26 20:07:08 DEBUG: pid 19119: read_kind_from_backend: read kind
>> from 0 th backend C NUM_BACKENDS: 2
>>
>> 2012-03-26 20:07:08 DEBUG: pid 19119: read_kind_from_backend: read kind
>> from 1 th backend C NUM_BACKENDS: 2
>>
>> 2012-03-26 20:07:08 DEBUG: pid 19119: ProcessBackendResponse: kind from
>> backend: C
>>
>> 2012-03-26 20:07:08 DEBUG: pid 19119: pool_read_string: read all from
>> pending data. po:7 len:29
>>
>> 2012-03-26 20:07:08 DEBUG: pid 19119: pool_read_string: read all from
>> pending data. po:7 len:60
>>
>> 2012-03-26 20:07:08 DEBUG: pid 19119: CompletedResponse: string: "BEGIN"
>>
>> 2012-03-26 20:07:08 DEBUG: pid 19119: read_kind_from_backend: read kind
>> from 0 th backend C NUM_BACKENDS: 2
>>
>> 2012-03-26 20:07:08 DEBUG: pid 19119: read_kind_from_backend: read kind
>> from 1 th backend E NUM_BACKENDS: 2
>>
>> 2012-03-26 20:07:08 ERROR: pid 19119: read_kind_from_backend: 1 th kind
>> E does not match with master or majority connection kind C
>>
>> 2012-03-26 20:07:08 DEBUG: pid 19119: pool_read_string: read all from
>> pending data. po:66 len:1
>>
>> 2012-03-26 20:07:08 ERROR: pid 19119: kind mismatch among backends.
>> Possible last query was: "begin;delete from channels where
>> hostname='';delete from channels where hostname='';commit;" kind details
>> are: 0[C] 1[E: ERROR:  cannot execute DELETE in a read-only transaction
>> ]
>>
>> 2012-03-26 20:07:08 LOG:   pid 19119: do_child: exits with status 1 due
>> to error
>> --------------
>> _______________________________________________
>> pgpool-general mailing list
>> pgpool-general at pgpool.net
>> http://www.pgpool.net/mailman/listinfo/pgpool-general


More information about the pgpool-general mailing list