[Pgpool-general] "BEGIN; LOCK TABLE" sends to slave in Master/Slave mode

[Tatsuo Ishii]

> Thank you for your reply!
> 
> But I have no way to change the request because the client proprietary.
> Is it possible in pgpool or modify the query or not to send to slave?
> Help white_function_list or black_function_list?

No.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> В Птн, 21/10/2011 в 15:23 +0900, Tatsuo Ishii пишет:
>> From: Успенский Игорь <admin at bestly.ru>
>> Subject: [Pgpool-general] "BEGIN; LOCK TABLE" sends to slave in Master/Slave mode
>> Date: Fri, 21 Oct 2011 09:48:48 +0400
>> Message-ID: <1319176128.17769.42.camel at admin.bestly.ru>
>> 
>> > Hello!
>> > 
>> > I using pgpool2 v3.1 and two PostgreSQL 9.0.4 (Master/Slave with stream
>> > replication)
>> > 
>> > Client send query "BEGIN;LOCK TABLE _1SCONNECT IN EXCLUSIVE MODE;" and
>> > pgpool sends this query master and slave, after slave print to log
>> > "cannot execute LOCK TABLE during recovery". Why pgpool sends this query
>> > to slave? How to change this behavior?
>> 
>> It's a know limitation of pgpool-II.
>> 
>> From:
>> http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool-en.html#restriction
>> 
>> > pgpool-II cannot process multi-statement queries. 
>> 
>> BEGIN;LOCK TABLE _1SCONNECT IN EXCLUSIVE MODE; is recognized as
>> "BEGIN;" statement by pgpool-II and it thinks that it needed to sent
>> to both master and slave.
>> 
>> The solution is to avoid multi-statement queries like BEGIN;LOCK TABLE_1SCONNECT IN EXCLUSIVE MODE;
>> 
>> Instead:
>> 
>> BEGIN;
>> LOCK TABLE _1SCONNECT IN EXCLUSIVE MODE;
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese: http://www.sraoss.co.jp
>