[pgpool-hackers: 456] Re: Multi statement query

Tatsuo Ishii ishii at postgresql.org
Thu Feb 13 07:32:30 JST 2014


> On Tue, Feb 11, 2014 at 07:18:16PM +0900, Tatsuo Ishii wrote:
>> Hi pgpool hackers,
>> 
>> Pgpool-II has a long standing restriction regarding "multi-statement
>> query". A multi-statement query is a series of queries separated by
>> ";". An example is "BEGIN;SELECT 1;SELECT 2;END". Currently pgpool-II
>> sends such a query to primary node only (in streaming replication
>> mode) or sends to certain node by just looking at the first query
>> ("BEGIN" in the example).
>> 
>> 1) Analyze each query in a multi-statement query to find a query which
>>    needs to be sent to all DB nodes. "BEGIN" or "END" are such query
>>    in the example above.
>> 
>> 2) If such a query is found, sends to all DB nodes.
>> 
>> 3) Otherwise sends to the primary node.
>> 
>> This seems to work except following case:
>> 
>> SET SQL_inheritance TO off; INSERT INTO t1...;
>> 
>> "SET" needs be sent to all nodes but "INSERT" must only be sent to
>> primary node. It seems it's impossible to satify those conditions at a
>> same time. Probably it's users fault to create sunch a query...
> 
> Are you saying that "SET SQL_inheritance TO off; INSERT INTO t1...;"
> comes to the server as a single string?

Yes, exactly.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp


More information about the pgpool-hackers mailing list