[pgpool-general-jp: 116] SELECT をマスタにだけ送信するパッチ

[Yoshiyuki Asaba]

浅羽です。

pgpool をレプリケーションモードで動かしていると、たまにデータ不一致を
誤検知することがあり、縮退することがあります。SELECT の件数がタイミン
グによって異なるためです。そこで負荷分散ができない条件下では、SELECT
をマスタだけに送るようにパッチを作りました。添付します。

ただし、これだと select nextval() と select setval() が困るかと思いま
すので、クエリの文字列が先頭から "SELECT nextval" もしくは "SELECT
setval" の場合(大文字、小文字は区別しない、スペースの数は問題なし)は強
制的にレプリケーションさせるようにしています。それ以外にどうしても
SELECT をレプリケーションさせたい場合は、クエリの先頭に
/*REPLICATION*/ のように適当なコメントを追加してください。

結構大きな仕様変更なのでまだコミットしていません。
設定で挙動を選択できるようにしてほしい、等のコメントがありましたらお知
らせください。

--
Yoshiyuki Asaba
y-asaba ＠ sraoss.co.jp


-------------- next part --------------
Index: pool_process_query.c
===================================================================
RCS file: /cvsroot/pgpool/pgpool/pool_process_query.c,v
retrieving revision 1.42
diff -c -r1.42 pool_process_query.c
*** pool_process_query.c	12 Feb 2007 06:19:37 -0000	1.42
--- pool_process_query.c	17 Apr 2007 06:27:32 -0000
***************
*** 115,120 ****
--- 115,122 ----
  static void process_reporting(POOL_CONNECTION *frontend, POOL_CONNECTION_POOL *backend);
  static int reset_backend(POOL_CONNECTION_POOL *backend, int qcnt);
  
+ static int is_select_query(char *sql);
+ static int is_sequence_query(char *sql);
  static int load_balance_enabled(POOL_CONNECTION_POOL *backend, char *sql);
  static void start_load_balance(POOL_CONNECTION_POOL *backend);
  static void end_load_balance(POOL_CONNECTION_POOL *backend);
***************
*** 143,148 ****
--- 145,151 ----
  static int master_slave_was_enabled;	/* master/slave mode was enabled */
  static int internal_transaction_started;		/* to issue table lock command a transaction
  												   has been started internally */
+ static int select_in_transaction = 0; /* non 0 if select query is in transaction */
  static void (*pending_function)(PreparedStatementList *p, PreparedStatement *statement) = NULL;
  static PreparedStatement *pending_prepared_stmt = NULL;
  
***************
*** 723,728 ****
--- 726,746 ----
  		MASTER_SLAVE = 0;
  		master_slave_dml = 1;
  	}
+ 	else if (REPLICATION && is_select_query(string1) && !is_sequence_query(string1))
+ 	{
+ 		int i;
+ 		/* save backend connection slots */
+ 		for (i=0;i<backend->num;i++)
+ 		{
+ 			slots[i] = backend->slots[i];
+ 		}
+ 
+ 		/* send query to master only. */
+ 		replication_was_enabled = 1;
+ 		REPLICATION = 0;
+ 		in_load_balance = 1;
+ 		select_in_transaction = 1;
+ 	}
  
  	/*
  	 * judge if we need to lock the table
***************
*** 842,847 ****
--- 860,881 ----
  		MASTER_SLAVE = 0;
  		master_slave_dml = 1;
  	}
+ 	else if (REPLICATION && is_select_query(stmt->prepared_string) && !is_sequence_query(stmt->prepared_string))
+ 	{
+ 		int i;
+ 
+ 		/* save backend connection slots */
+ 		for (i=0;i<backend->num;i++)
+ 		{
+ 			slots[i] = backend->slots[i];
+ 		}
+ 
+ 		/* send query to master only. */
+ 		replication_was_enabled = 1;
+ 		REPLICATION = 0;
+ 		in_load_balance = 1;
+ 		select_in_transaction = 1;
+ 	}
  
  	for (i = 0;i < backend->num;i++)
  	{
***************
*** 2501,2506 ****
--- 2535,2542 ----
  	{
  		pending_function(&prepared_list, pending_prepared_stmt);
  	}
+ 	else if (kind == 'C' && select_in_transaction)
+ 		select_in_transaction = 0;
  
  	pending_function = NULL;
  	pending_prepared_stmt = NULL;
***************
*** 2588,2593 ****
--- 2624,2636 ----
  			}
  		}
  
+ 		if (select_in_transaction)
+ 		{
+ 			do_command(SECONDARY(backend), "send invalid query from pgpool to abort transaction.",
+ 					   PROTO_MAJOR_V3, 0);
+ 			select_in_transaction = 0;
+ 		}
+ 
  		for (i = 0;i < backend->num;i++)
  		{
  			POOL_CONNECTION *cp = backend->slots[i]->con;
***************
*** 2942,2970 ****
  }
  
  /*
!  * return non 0 if load balance is possible
   */
! static int load_balance_enabled(POOL_CONNECTION_POOL *backend, char *sql)
  {
! 	if (pool_config.load_balance_mode &&
! 		DUAL_MODE &&
! 		MAJOR(backend) == PROTO_MAJOR_V3 &&
! 		TSTATE(backend) == 'I')
  	{
! 		if (pool_config.ignore_leading_white_space)
! 		{
! 			/* ignore leading white spaces */
! 			while (*sql && isspace(*sql))
! 				sql++;
! 		}
  
! 		if (!strncasecmp(sql, "SELECT", 6))
! 			return 1;
  	}
  	return 0;
  }
  
  /*
   * start load balance mode
   */
  static void start_load_balance(POOL_CONNECTION_POOL *backend)
--- 2985,3049 ----
  }
  
  /*
!  * return non 0 if SQL is SELECT statement.
   */
! static int is_select_query(char *sql)
  {
! 	if (pool_config.ignore_leading_white_space)
  	{
! 		/* ignore leading white spaces */
! 		while (*sql && isspace(*sql))
! 			sql++;
! 	}
  
! 	return (!strncasecmp(sql, "SELECT", 6));
! 
! }
! 
! /*
!  * return non 0 if SQL is SELECT statement.
!  */
! static int is_sequence_query(char *sql)
! {
! 	if (pool_config.ignore_leading_white_space)
! 	{
! 		/* ignore leading white spaces */
! 		while (*sql && isspace(*sql))
! 			sql++;
  	}
+ 
+ 	if (strncasecmp(sql, "SELECT", 6))
+ 		return 0;
+ 
+ 	sql += 6;
+ 	while (*sql && isspace(*sql))
+ 		sql++;
+ 
+ 	/* SELECT NEXTVAL('xxx') */
+ 	if (*sql && !strncasecmp(sql, "NEXTVAL", 7))
+ 		return 1;
+ 
+ 	/* SELECT SETVAL('xxx') */
+ 	if (*sql && !strncasecmp(sql, "SETVAL", 6))
+ 		return 1;
+ 
  	return 0;
  }
  
  /*
+  * return non 0 if load balance is possible
+  */
+ static int load_balance_enabled(POOL_CONNECTION_POOL *backend, char *sql)
+ {
+ 	return (pool_config.load_balance_mode &&
+ 			DUAL_MODE &&
+ 			MAJOR(backend) == PROTO_MAJOR_V3 &&
+ 			TSTATE(backend) == 'I' &&
+ 			is_select_query(sql) &&
+ 			!is_sequence_query(sql));
+ }
+ 
+ /*
   * start load balance mode
   */
  static void start_load_balance(POOL_CONNECTION_POOL *backend)