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

[Tatsuo Ishii]

> On Thu, Feb 13, 2014 at 07:32:30AM +0900, Tatsuo Ishii wrote:
>> > 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.
> 
> I must be missing something.  Why does a BEGIN/END block or SET need to
> be sent to all nodes?  I understand INSERT only should be sent to the
> master.

I don't want to devide the query string into each query part because
it's very hard to process.

However there's a room to enhance this. Here's the revised algorithm:

1) If the query includes SET command which must be sent to all the DB
   nodes:

   1-1) If the query also includes any command which must be sent to
      the primary node, send the query to the primary node. Subsequent
      queries are sent to the primary node only until the session ends.

	  example:SET SQL_inheritance TO off; INSERT INTO t1...;"

   1-2) Otherwise the query is sent to all the DB nodes.

	  example:SET SQL_inheritance TO off; SELECT * FROM t1;"

2) If the query does not include SET command which must be sent to all
   the DB, nor include SQL commands which must be sent to the primary
   node, the query string can be sent to the standby node.

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