[Pgpool-general] Multi-statement queries

[Matthias Tief]

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