Question: What would happen if the multi-statement string is "BEGIN; SELECT 1; INSERT INTO ...; COMMIT;"?
> -----Original Message----- > From: Matthias Tief [mailto:[email protected]] > Sent: Wednesday, March 10, 2010 7:21 AM > To: Tatsuo Ishii > Cc: [email protected]; [email protected]; Crespo, Daniel @ SDS; > [email protected] > Subject: Re: [Pgpool-general] Multi-statement queries > > Hi, > > As I understand one can avoid this problem by setting replicate_select > = > true, right? But than one will have almost no load balancing anymore. > > Matthias > > Tatsuo Ishii schrieb: > >> Hello there, > >> > >> My experience is that, the problem with multi-statement queries, is > when you > >> run in replication mode and the multi-statement query starts with > "select", > >> and next an "insert", then pgpool runs query in one backend, but > don't > >> replicate insert. In this case backends are not synchronized. > >> With multi-statement query, type select....;select.....;select.... > seems run > >> well. > > > > Not really. What if: > > > > SELECT 1;SELECT nextval(..);... > > -- > > Tatsuo Ishii > > SRA OSS, Inc. Japan > > English: http://www.sraoss.co.jp/index_en.php > > Japanese: http://www.sraoss.co.jp > > > >> Regards. > >> > >> > >> 2010/3/9 Glyn Astill <[email protected]> > >> > >>> Yeah I'm with Daniel on this one, Tatsuo? > >>> > >>> --- On Tue, 9/3/10, [email protected] <daniel.cre...@l- > 3com.com> > >>> wrote: > >>> > >>>> From: [email protected] <[email protected]> > >>>> Subject: Re: [Pgpool-general] Multi-statement queries > >>>> To: "Matthias Tief" <[email protected]>, > >>> [email protected] > >>>> Date: Tuesday, 9 March, 2010, 14:50 > >>>> My experience is also that > >>>> multi-statement queries ("statement1; statement2; ...; > >>>> statementN;") work in pgpool-II, so now I'm confused with > >>>> "pgpool-II cannot process multi-statement query". Also, why > >>>> wouldn't it work if PostgreSQL itself does? I think the idea > >>>> is pgpool-II to work transparently as connecting directly to > >>>> PostgreSQL. > >>>> > >>>> Daniel > >>>> > >>>>> -----Original Message----- > >>>>> From: [email protected] > >>>> [mailto:pgpool-general- > >>>>> [email protected]] > >>>> On Behalf Of Matthias Tief > >>>>> Sent: Tuesday, March 09, 2010 4:11 AM > >>>>> To: [email protected] > >>>>> Subject: Re: [Pgpool-general] Multi-statement queries > >>>>> > >>>>> Thank you for your rapid answer. > >>>>> > >>>>> But for me it is still not clear. What you describe > >>>> seems to be a > >>>>> collection of statements transmitted as a single > >>>> transaction. But this > >>>>> is done very well by pgpool-II in my tests. I am wrong > >>>> with that? > >>>>> So than my question is: What is the difference between > >>>> "multi-statement > >>>>> queries" and a single transaction of multiple > >>>> statements? > >>>>> As I understand pgadmin implicitly wrapps a > >>>> BEGIN/COMMIT block around > >>>>> all statements, that it becomes a single transaction. > >>>>> > >>>>> Looking forward to your answer. > >>>>> > >>>>> Matthias > >>>>> > >>>>> Guillaume Lelarge schrieb: > >>>>>> Le 05/03/2010 09:45, Matthias Tief a écrit : > >>>>>>> Hallo, > >>>>>>> > >>>>>>> we are planning to use pgpool-II as > >>>> replication solution for our > >>>>>>> PostgeSQL application. Currently we evaluate, > >>>> whether the > >>>>> restriction of > >>>>>>> pgpool-II affect our application. In the > >>>> documentation you say: > >>>>>>> "pgpool-II cannot process multi-statement > >>>> query." What do you > >>>>> exactly > >>>>>>> mean with "multi-statement queries". As I > >>>> found out you don't mean > >>>>>>> transactions that contain multiple statments, > >>>> like BEGIN - COMMIT > >>>>>>> blocks. These blocks are "committed" or in > >>>> case of a failure are > >>>>> "rolled > >>>>>>> back" correctly. I would appreciate if you > >>>> could give me an example > >>>>> of > >>>>>>> multi-statement queries. > >>>>>>> > >>>>>> "statement1; statement2; statement3;" in a single > >>>> trip to the > >>>>> backend. > >>>>>> For example: > >>>>>> > >>>>>> "INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES > >>>> (2); INSERT INTO t1 > >>>>>> VALUES (3);" > >>>>>> > >>>>>> is a multi-statement query, whereas > >>>>>> > >>>>>> "INSERT INTO t1 VALUES (1);" > >>>>>> then > >>>>>> "INSERT INTO t1 VALUES (2);" > >>>>>> then > >>>>>> "INSERT INTO t1 VALUES (3);" > >>>>>> > >>>>>> are three one-statement queries. > >>>>>> > >>>>>> pgAdmin uses this quite a lot ("SET > >>>> client_encoding TO utf8; SELECT * > >>>>>> FROM..."). > >>>>>> > >>>>>> > >>>>> -- > >>>>> IT Service Omikron GmbH > >>>>> Mohrenstr. 63-64; D-10117 Berlin > >>>>> Tel.: +49 (030) 220791-30 > >>>>> Fax: +49 (030) 220791-55 > >>>>> Email: [email protected] > >>>>> Internet: www.itso.de > >>>>> Amtsgericht Berlin Charlottenburg HRB 21013 > >>>>> > >>>>> _______________________________________________ > >>>>> Pgpool-general mailing list > >>>>> [email protected] > >>>>> http://pgfoundry.org/mailman/listinfo/pgpool-general > >>>> _______________________________________________ > >>>> Pgpool-general mailing list > >>>> [email protected] > >>>> http://pgfoundry.org/mailman/listinfo/pgpool-general > >>>> > >>> > >>> > >>> _______________________________________________ > >>> Pgpool-general mailing list > >>> [email protected] > >>> http://pgfoundry.org/mailman/listinfo/pgpool-general > >>> > >> > >> > >> -- > >> César Martín Pérez > >> [email protected] > > -- > IT Service Omikron GmbH > Mohrenstr. 63-64; D-10117 Berlin > Tel.: +49 (030) 220791-30 > Fax: +49 (030) 220791-55 > Email: [email protected] > Internet: www.itso.de > Amtsgericht Berlin Charlottenburg HRB 21013 _______________________________________________ Pgpool-general mailing list [email protected] http://pgfoundry.org/mailman/listinfo/pgpool-general
