[Pgpool-general] Multi-statement queries

[Tatsuo Ishii]

> 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