[Pgpool-general] Multi-statement queries

Thu Mar 11 11:59:23 UTC 2010

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

Daniel.Crespo at l-3com.com schrieb:
> Question: What would happen if the multi-statement string is "BEGIN; SELECT 1; INSERT INTO ...; COMMIT;"?
> 
>> -----Original Message-----
>> From: Matthias Tief [mailto:matthias at itso-berlin.de]
>> Sent: Wednesday, March 10, 2010 7:21 AM
>> To: Tatsuo Ishii
>> Cc: cmartinp at gmail.com; glynastill at yahoo.co.uk; Crespo, Daniel @ SDS;
>> pgpool-general at pgfoundry.org
>> 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 <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
>> --
>> 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

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