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

[Tatsuo Ishii]

> 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