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

[Tatsuo Ishii]

> Hello Tatuso,
> 
> Thanks explaining the situation.
> 
> I understand the need for the change because of the JDBC drivers.
> 
> Is there any way to add a configuration option to enable/disable the old
> behavior?
> 
> master_slave_replicate_transactions = on/off

Well, I don't want to add another switch to smooth over broken
behavior:-) I'd think making pgpool a little cleverer to send any
multi statement querires only to primary.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> Thanks,
> Bill
> 
> 
> On 3/27/12 10:48 AM, Tatsuo Ishii wrote:
>>> 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?
>> 
>> Actually "begin" used to be sent to the primary only before
>> 3.1.x. After following commit, "begin" is sent to standby as well. I
>> didn't know the change breaks your applications. Sorry.
>> 
>> authorToshihiro Kitagawa <kitagawa at sraoss.co.jp>
>>  Fri, 12 Aug 2011 06:46:04 +0000 (06:46 +0000)
>> committerToshihiro Kitagawa <kitagawa at sraoss.co.jp>
>>  Fri, 12 Aug 2011 06:46:04 +0000 (06:46 +0000)
>> commit876aa5433aceff40ad0f136cbd95d8c48f17e546
>> tree1f138faa5cd62c8e66c49dba3a4e31577f29d1d5tree | snapshot
>> parent0f99cff342692c820e9b1787c39928878c3906fbcommit | diff
>> 
>> Fix bug which cannot use the cursors of JDBC driver on standby node.
>> In master/slave mode, the transaction commands come to be sent to
>> all nodes by this fix because the cursors of JDBC driver cannot use
>> without transactions.
>> 
>> But if the transaction command is "BEGIN READ WRITE" or "START TRANSACTION
>> READ WRITE", pgpool-II sends "BEGIN" instead of it to standby node.
>> The reason is because standby nodes disallow "READ WRITE" option.
>> 
>>> 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.
>> 
>> Because the parameter only applied to SELECTs.
>> 
>>> 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.
>> 
>> Are you using JDBC driver? If not, downgrading to 3.0.x. might help you.
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese: http://www.sraoss.co.jp
>> 
>>> 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