[pgpool-general: 323] Re: Transaction replicated to streaming slave
Tatsuo Ishii
ishii at postgresql.org
Fri Mar 30 09:13:45 JST 2012
Bill,
Here is the patch to implement what described below. Could you please
test it.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp
>> 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
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general
-------------- next part --------------
A non-text attachment was scrubbed...
Name: multi_statement.patch
Type: text/x-patch
Size: 2343 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20120330/60092794/attachment.bin>
More information about the pgpool-general
mailing list