[Pgpool-general] Multi-statement queries

[Glyn Astill]

Ah, I see.

--- On Wed, 10/3/10, Cesar Martin <cmartinp at gmail.com> wrote:

> From: Cesar Martin <cmartinp at gmail.com>
> Subject: Re: [Pgpool-general] Multi-statement queries
> To: "Glyn Astill" <glynastill at yahoo.co.uk>
> Cc: "Matthias Tief" <matthias at itso-berlin.de>, pgpool-general at pgfoundry.org, Daniel.Crespo at l-3com.com
> Date: Wednesday, 10 March, 2010, 8:18
> 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
> 
> 
>