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

[Tatsuo Ishii]

In theory you are right. In reality it will need major rewirting of
pgpool-II and developers do not have time to deal with that.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> Any reasons why it's not possible to have pgpool split the multi-statement query by using ";", and send one by one instead of making the client worry about this?
> 
> Thanks,
> -Daniel
> 
>> -----Original Message-----
>> From: pgpool-general-bounces at pgfoundry.org [mailto:pgpool-general-
>> bounces at pgfoundry.org] On Behalf Of Tatsuo Ishii
>> Sent: Friday, October 21, 2011 4:11 AM
>> To: admin at bestly.ru
>> Cc: pgpool-general at pgfoundry.org
>> Subject: s::[i] Re: [Pgpool-general] "BEGIN; LOCK TABLE" sends to slave
>> in Master/Slave mode
>> 
>> > 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
>> >
>> _______________________________________________
>> Pgpool-general mailing list
>> Pgpool-general at pgfoundry.org
>> http://pgfoundry.org/mailman/listinfo/pgpool-general