Re: [Pgpool-general] Multi-statement queries

Matthias Tief Wed, 10 Mar 2010 04:21:24 -0800

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] <[email protected]>
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

Re: [Pgpool-general] Multi-statement queries

Reply via email to