[Pgpool-general] Multi-statement queries

Wed Mar 10 14:49:02 UTC 2010

Question: What would happen if the multi-statement string is "BEGIN; SELECT 1; INSERT INTO ...; COMMIT;"?

> -----Original Message-----
> From: Matthias Tief [mailto:matthias at itso-berlin.de]
> Sent: Wednesday, March 10, 2010 7:21 AM
> To: Tatsuo Ishii
> Cc: cmartinp at gmail.com; glynastill at yahoo.co.uk; Crespo, Daniel @ SDS;
> pgpool-general at pgfoundry.org
> 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 <glynastill at yahoo.co.uk>
> >>
> >>> Yeah I'm with Daniel on this one, Tatsuo?
> >>>
> >>> --- On Tue, 9/3/10, Daniel.Crespo at l-3com.com <Daniel.Crespo at l-
> 3com.com>
> >>> wrote:
> >>>
> >>>> From: Daniel.Crespo at l-3com.com <Daniel.Crespo at l-3com.com>
> >>>> Subject: Re: [Pgpool-general] Multi-statement queries
> >>>> To: "Matthias Tief" <matthias at itso-berlin.de>,
> >>> pgpool-general at pgfoundry.org
> >>>> 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: pgpool-general-bounces at pgfoundry.org
> >>>> [mailto:pgpool-general-
> >>>>> bounces at pgfoundry.org]
> >>>> On Behalf Of Matthias Tief
> >>>>> Sent: Tuesday, March 09, 2010 4:11 AM
> >>>>> To: pgpool-general at pgfoundry.org
> >>>>> 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: matthias.tief at itso-berlin.de
> >>>>> Internet: www.itso.de
> >>>>> Amtsgericht Berlin Charlottenburg HRB 21013
> >>>>>
> >>>>> _______________________________________________
> >>>>> Pgpool-general mailing list
> >>>>> Pgpool-general at pgfoundry.org
> >>>>> http://pgfoundry.org/mailman/listinfo/pgpool-general
> >>>> _______________________________________________
> >>>> Pgpool-general mailing list
> >>>> Pgpool-general at pgfoundry.org
> >>>> http://pgfoundry.org/mailman/listinfo/pgpool-general
> >>>>
> >>>
> >>>
> >>> _______________________________________________
> >>> Pgpool-general mailing list
> >>> Pgpool-general at pgfoundry.org
> >>> http://pgfoundry.org/mailman/listinfo/pgpool-general
> >>>
> >>
> >>
> >> --
> >> César Martín Pérez
> >> cmartinp at gmail.com
> 
> --
> IT Service Omikron GmbH
> Mohrenstr. 63-64; D-10117 Berlin
> Tel.: +49 (030) 220791-30
> Fax: +49 (030) 220791-55
> Email: matthias.tief at itso-berlin.de
> Internet: www.itso.de
> Amtsgericht Berlin Charlottenburg HRB 21013