[pgpool-general-jp: 159] Re: pgpoolでのupdateについて

[Yoshiyuki Asaba]

浅羽です。

From: Yoshiyuki Asaba <y-asaba ＠ sraoss.co.jp>
Subject: [pgpool-general-jp: 158] Re: pgpoolでのupdateについて
Date: Mon, 04 Jun 2007 15:28:46 +0900 (JST)

> ただ、1. については CREATE DATABASE など、トランザクション中では実行で
> きないクエリがあるので、SQL パーサを持たない pgpool-I ではちょっとメン
> ドウなところではあります。pgpool-II 用のパッチを添付します。

拡張問合せプロトコルの存在を忘れていましたので、パッチを作り直しました。

--
Yoshiyuki Asaba
y-asaba ＠ sraoss.co.jp
-------------- next part --------------
Index: pool_process_query.c
===================================================================
RCS file: /cvsroot/pgpool/pgpool-II/pool_process_query.c,v
retrieving revision 1.19
diff -c -r1.19 pool_process_query.c
*** pool_process_query.c	2 Jun 2007 00:54:40 -0000	1.19
--- pool_process_query.c	4 Jun 2007 06:31:20 -0000
***************
*** 179,184 ****
--- 179,187 ----
  static POOL_MEMORY_POOL *prepare_memory_context = NULL;
  
  static void query_cache_register(char kind, POOL_CONNECTION *frontend, char *database, char *data, int data_len);
+ static POOL_STATUS start_internal_transaction(POOL_CONNECTION_POOL *backend, Node *node);
+ static POOL_STATUS end_internal_transaction(POOL_CONNECTION_POOL *backend);
+ 
  
  POOL_STATUS pool_process_query(POOL_CONNECTION *frontend, 
  							   POOL_CONNECTION_POOL *backend,
***************
*** 1003,1008 ****
--- 1006,1016 ----
  				return status;
  			}
  		}
+ 		else if (REPLICATION && query == NULL && start_internal_transaction(backend, node))
+ 		{
+ 			free_parser();
+ 			return POOL_ERROR;
+ 		}
  	}
  
  	free_parser();
***************
*** 1090,1095 ****
--- 1098,1107 ----
  			in_load_balance = 1;
  			select_in_transaction = 1;
  		}
+ 		else if (REPLICATION && start_internal_transaction(backend, (Node *)p_stmt->query))
+ 		{
+ 			return POOL_END;
+ 		}
  		pool_memory_delete(pool_memory);
  	}
  	else if (MASTER_SLAVE)
***************
*** 1253,1270 ****
  		/* set transaction state */
  		pool_debug("ReadyForQuery: transaction state: %c", state);
  
! 		for (i=0;i<NUM_BACKENDS;i++)
! 		{
! 			if (!VALID_BACKEND(i))
! 				continue;
! 
! 			CONNECTION(backend, i)->tstate = state;
! 
! 			if (do_command(CONNECTION(backend, i), "COMMIT", PROTO_MAJOR_V3, 1) != POOL_CONTINUE)
! 				return POOL_ERROR;
! 		}
! 
! 		internal_transaction_started = 0;
  	}
  
  	pool_flush(frontend);
--- 1265,1272 ----
  		/* set transaction state */
  		pool_debug("ReadyForQuery: transaction state: %c", state);
  
! 		if (end_internal_transaction(backend) != POOL_CONTINUE)
! 			return POOL_ERROR;
  	}
  
  	pool_flush(frontend);
***************
*** 4020,4042 ****
  
  	snprintf(qbuf, sizeof(qbuf), "LOCK TABLE %s IN SHARE ROW EXCLUSIVE MODE", table);
  
! 	/* if we are not in a transaction block,
! 	 * start a new transaction
! 	 */
! 	if (TSTATE(backend) == 'I')
! 	{
! 		for (i=0;i<NUM_BACKENDS;i++)
! 		{
! 			if (VALID_BACKEND(i))
! 			{
! 				if (do_command(CONNECTION(backend, i), "BEGIN", PROTO_MAJOR_V3, 0) != POOL_CONTINUE)
! 					return POOL_END;
! 			}
! 		}
! 
! 		/* mark that we started new transaction */
! 		internal_transaction_started = 1;
! 	}
  
  	status = POOL_CONTINUE;
  
--- 4022,4029 ----
  
  	snprintf(qbuf, sizeof(qbuf), "LOCK TABLE %s IN SHARE ROW EXCLUSIVE MODE", table);
  
! 	if (start_internal_transaction(backend, (Node *)node) != POOL_CONTINUE)
! 		return POOL_END;
  
  	status = POOL_CONTINUE;
  
***************
*** 4530,4532 ****
--- 4517,4580 ----
  
  	set_ps_display(psbuf, false);
  }
+ 
+ static POOL_STATUS start_internal_transaction(POOL_CONNECTION_POOL *backend, Node *node)
+ {
+ 	int i;
+ 
+ 	/* if we are not in a transaction block,
+ 	 * start a new transaction
+ 	 */
+ 	if (TSTATE(backend) == 'I' &&
+ 		!IsA(node, CreatedbStmt) && !IsA(node, VacuumStmt) && !IsA(node, DropdbStmt))
+ 	{
+ 		for (i=0;i<NUM_BACKENDS;i++)
+ 		{
+ 			if (VALID_BACKEND(i))
+ 			{
+ 				if (do_command(CONNECTION(backend, i), "BEGIN", PROTO_MAJOR_V3, 0) != POOL_CONTINUE)
+ 					return POOL_END;
+ 			}
+ 		}
+ 
+ 		/* mark that we started new transaction */
+ 		internal_transaction_started = 1;
+ 	}
+ 	return POOL_CONTINUE;
+ }
+ 
+ 
+ static POOL_STATUS end_internal_transaction(POOL_CONNECTION_POOL *backend)
+ {
+ 	int i;
+ 
+ 	/* We need to commit from secondary to master. */
+ 	for (i=0;i<NUM_BACKENDS;i++)
+ 	{
+ 		if (VALID_BACKEND(i) && !IS_MASTER_NODE_ID(i))
+ 		{
+ 			if (do_command(CONNECTION(backend, i), "COMMIT", PROTO_MAJOR_V3, 1) != POOL_CONTINUE)
+ 			{
+ 				internal_transaction_started = 0;
+ 				return POOL_END;
+ 			}
+ 		}
+ 	}
+ 
+ 	/* commit on master */
+ 	for (i=0;i<NUM_BACKENDS;i++)
+ 	{
+ 		if (VALID_BACKEND(i) && IS_MASTER_NODE_ID(i))
+ 		{
+ 			if (do_command(CONNECTION(backend, i), "COMMIT", PROTO_MAJOR_V3, 1) != POOL_CONTINUE)
+ 			{
+ 				internal_transaction_started = 0;
+ 				return POOL_END;
+ 			}
+ 			break;
+ 		}
+ 	}
+ 
+ 	internal_transaction_started = 0;
+ 	return POOL_CONTINUE;	
+ }