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

[Daniel.Crespo at l-3com.com]

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