Re: [Pgpool-general] Multi-statement queries

Matthias Tief Thu, 11 Mar 2010 04:01:54 -0800

As I understand the documentation, when replicate_select is true and theselect-statement is inside a transaction block then it is replicated.Following Tatsuo's comment the rest of the multi-state is replicated too.


[email protected] schrieb:

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

-----Original Message-----
From: Matthias Tief [mailto:[email protected]]
Sent: Wednesday, March 10, 2010 7:21 AM
To: Tatsuo Ishii
Cc: [email protected]; [email protected]; Crespo, Daniel @ SDS;
[email protected]
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 <[email protected]>

Yeah I'm with Daniel on this one, Tatsuo?

--- On Tue, 9/3/10, [email protected] <daniel.cre...@l-

3com.com>

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


--
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