[Pgpool-general] Multi-statement queries

[Cesar Martin]

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.

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://pgfoundry.org/pipermail/pgpool-general/attachments/20100310/a92dab03/attachment-0001.html>