pgbench_loop_v2.patch

diff --git a/contrib/pgbench/pgbench.c b/contrib/pgbench/pgbench.c
new file mode 100644
index b0e6991..899820d
*** a/contrib/pgbench/pgbench.c
--- b/contrib/pgbench/pgbench.c
*************** char	   *index_tablespace = NULL;
*** 138,143 ****
--- 138,144 ----
  								 * -s instead */
  #define ntellers	10
  #define naccounts	100000
+ #define plpgsql_batch_size	512
  
  bool		use_log;			/* log transaction latencies to a file */
  bool		is_connect;			/* establish connection for each transaction */
*************** static char *select_only = {
*** 282,287 ****
--- 283,293 ----
  	"SELECT abalance FROM pgbench_accounts WHERE aid = :aid;\n"
  };
  
+ /* -P case */
+ static char *select_only_plpgsql = {
+ 	"SELECT pgbench_query(" CppAsString2(naccounts) " *:scale," CppAsString2(plpgsql_batch_size) ");\n"
+ };
+ 
  /* Function prototypes */
  static void setalarm(int seconds);
  static void *threadRun(void *arg);
*************** printResults(int ttype, int normal_xacts
*** 1765,1770 ****
--- 1771,1778 ----
  		s = "Update only pgbench_accounts";
  	else if (ttype == 1)
  		s = "SELECT only";
+ 	else if (ttype == 4)
+ 		s = "SELECT only using PL/pgSQL";
  	else
  		s = "Custom query";
  
*************** printResults(int ttype, int normal_xacts
*** 1787,1792 ****
--- 1795,1807 ----
  	}
  	printf("tps = %f (including connections establishing)\n", tps_include);
  	printf("tps = %f (excluding connections establishing)\n", tps_exclude);
+ 	if (ttype==4)
+ 	{
+ 		printf("selects per second = %f (including connections establishing)\n", 
+ 				tps_include*plpgsql_batch_size);
+ 		printf("selects per second = %f (excluding connections establishing)\n", 
+ 				tps_exclude*plpgsql_batch_size);
+ 	};
  
  	/* Report per-command latencies */
  	if (is_latencies)
*************** main(int argc, char **argv)
*** 1845,1851 ****
  	int			is_no_vacuum = 0;		/* no vacuum at all before testing? */
  	int			do_vacuum_accounts = 0; /* do vacuum accounts before testing? */
  	int			ttype = 0;		/* transaction type. 0: TPC-B, 1: SELECT only,
! 								 * 2: skip update of branches and tellers */
  	int			optindex;
  	char	   *filename = NULL;
  	bool		scale_given = false;
--- 1860,1868 ----
  	int			is_no_vacuum = 0;		/* no vacuum at all before testing? */
  	int			do_vacuum_accounts = 0; /* do vacuum accounts before testing? */
  	int			ttype = 0;		/* transaction type. 0: TPC-B, 1: SELECT only,
! 								 * 2: skip update of branches and tellers 
! 								 * 3: custom sql, 
! 								 * 4: SELECT-only using plpgsql*/
  	int			optindex;
  	char	   *filename = NULL;
  	bool		scale_given = false;
*************** main(int argc, char **argv)
*** 1910,1916 ****
  	state = (CState *) xmalloc(sizeof(CState));
  	memset(state, 0, sizeof(CState));
  
! 	while ((c = getopt_long(argc, argv, "ih:nvp:dSNc:j:Crs:t:T:U:lf:D:F:M:", long_options, &optindex)) != -1)
  	{
  		switch (c)
  		{
--- 1927,1933 ----
  	state = (CState *) xmalloc(sizeof(CState));
  	memset(state, 0, sizeof(CState));
  
! 	while ((c = getopt_long(argc, argv, "ih:nvp:dSNPc:j:Crs:t:T:U:lf:D:F:M:", long_options, &optindex)) != -1)
  	{
  		switch (c)
  		{
*************** main(int argc, char **argv)
*** 1938,1943 ****
--- 1955,1963 ----
  			case 'N':
  				ttype = 2;
  				break;
+ 			case 'P':
+ 				ttype = 4;
+ 				break;
  			case 'c':
  				nclients = atoi(optarg);
  				if (nclients <= 0 || nclients > MAXCLIENTS)
*************** main(int argc, char **argv)
*** 2196,2201 ****
--- 2216,2241 ----
  					scale);
  	}
  
+ 	if (ttype == 4)
+ 	{
+ 		executeStatement(con,
+ 			"create or replace function pgbench_query(scale integer,size integer) "
+ 			"RETURNS integer AS $$ "
+ 			"DECLARE sum integer default 0; "
+ 			"amount integer; "
+ 			"account_id integer; "
+ 			"BEGIN FOR i IN 1..size LOOP "
+ 			"   account_id=1+floor(random()*scale); "
+ 			"   SELECT abalance into strict amount FROM pgbench_accounts "
+ 			"      WHERE aid = account_id; "
+ 			"   sum := sum + amount; "
+ 			"END LOOP; "
+ 			"return sum; "
+ 			"END $$ LANGUAGE plpgsql ");
+ 		if (debug)
+ 			fprintf(stderr, "plgsql function created.\n");
+ 	};
+ 
  	/*
  	 * :scale variables normally get -s or database scale, but don't override
  	 * an explicit -D switch
*************** main(int argc, char **argv)
*** 2249,2254 ****
--- 2289,2299 ----
  			num_files = 1;
  			break;
  
+ 		case 4:
+ 			sql_files[0] = process_builtin(select_only_plpgsql);
+ 			num_files = 1;
+ 			break;
+ 
  		default:
  			break;
  	}
diff --git a/doc/src/sgml/pgbench.sgml b/doc/src/sgml/pgbench.sgml
new file mode 100644
index 1b0905f..13ef6cf
*** a/doc/src/sgml/pgbench.sgml
--- b/doc/src/sgml/pgbench.sgml
*************** pgbench <optional> <replaceable>options<
*** 347,352 ****
--- 347,375 ----
       </varlistentry>
  
       <varlistentry>
+       <term><option>-P</option></term>
+       <listitem>
+        <para>
+ 		Perform select-only transactions instead of TPC-B-like test, but 
+ 		executes the selects in a loop in PL/pgSQL.  This is similar to 
+ 		<option>-S</>, but it removes the overhead of inter-process communication 
+ 		between the <application>pgbench</> program and the database server by 
+ 		executing 512 select statments per transaction and per network round trip.
+ 		It incidentally also reduces the frequency of begining and ending
+ 		transactions and taking and releasing object-level locks.
+        </para>
+        <para>
+ 		When <application>pgbench</> is run at a scale in which the data set fits
+ 		memory, network overhead can be the main bottle-neck.  This option allows
+ 		that effect to be quantified by reducing that overhead.  When used with a 
+ 		data set that does not fit in memory, this option is not meaningfully 
+ 		different than <option>-S</>, and its use may cause the desired running 
+ 		time (requested with <option>-T</>) to substantially overshoot its target.
+        </para>
+       </listitem>
+      </varlistentry>
+ 
+      <varlistentry>
        <term><option>-r</option></term>
        <listitem>
         <para>