64-bit pgbench V2

Attached is an updated second rev of the patch I sent a few months ago,
to expand pgbench to support database scales larger than around
4,294--where the 32-bit integer for the account number overflows in the
current version.  The current limit makes for about a 60GB database.
Last week I ran this on a system with 72GB of RAM, which are already
quite common, and wasn't able to get a test that didn't fit in RAM.
Without a bug fix here I am concerned that pgbench will ship in 9.0
already obsolete for the generation of hardware is it going to be
deployed on.

The main tricky part was figuring how to convert the \setshell
implementation.  That uses strtol to parse the number that should have
been returned by the shell call.  It turns out there are a stack of ways
to do something similar but return 64 bits instead:

* strtoll is defined by ISO C99
* strtoq was used on some earlier BSD systems
* MSVC has _strtoi64 for signed and _strtoui64 for unsigned 64bit integers

According to the glib docs at
http://www.gnu.org/software/gnulib/manual/html_node/strtoll.html ,
stroll is missing on HP-UX 11, OSF/1 5.1, Interix 3.5, so one of the
HP-UX boxes might be a useful testbed for what works on a trickier platform.

For prototype purposes, I wrote the patch to include some minimal logic
to map the facility available to strtoint64, falling back to the 32-bit
strtol if that's the best available.  There are three ways I could
forsee this going:

1) Keep this ugly bit of code isolated to pgbench
2) Move it to src/include/c.h where the other 64-bit int abstraction is done
3) Push the problem toward autoconf

I don't have a clear argument for or against those individual options,
they all seem reasonable from some perspectives.

The only open issue I'm not sure about is whether the situation where
the code falls back to 32-bits should be documented, or even a warning
produced if you create something at a scale without some strtoll
available.  Given that it only impacts the \setrandom case, it's not
really a disaster that it might not work, so long as there's
documentation explaining the potential limitations.  I'll write those if
necessary, but I think that some testing on known tricky platforms that
I don't have setup here is the best next step, so I'm looking for
feedback on that.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us

diff --git a/contrib/pgbench/pgbench.c b/contrib/pgbench/pgbench.c
index c830dee..e6621e2 100644
--- a/contrib/pgbench/pgbench.c
+++ b/contrib/pgbench/pgbench.c
@@ -60,6 +60,20 @@
 #define INT64_MAX    INT64CONST(0x7FFFFFFFFFFFFFFF)
 #endif

+/* Try to find a string to 64-bit integer implementation */
+#ifndef strtoint64
+#ifdef strtoll
+#define strtoint64    strtoll
+#elif defined(_strtoi64)
+#define strtoi64    _strtoi64
+#elif defined(strtoq)
+#define strtoi64    strtoq
+#else
+/* Fall back to 32 bit version if no 64-bit version is available */
+#define strtoi64    strtol
+#endif
+#endif
+
 /*
  * Multi-platform pthread implementations
  */
@@ -310,14 +324,14 @@ usage(const char *progname)
 }

 /* random number generator: uniform distribution from min to max inclusive */
-static int
-getrand(int min, int max)
+static int64
+getrand(int64 min, int64 max)
 {
     /*
      * Odd coding is so that min and max have approximately the same chance of
      * being selected as do numbers between them.
      */
-    return min + (int) (((max - min + 1) * (double) random()) / (MAX_RANDOM_VALUE + 1.0));
+    return min + (int64) (((max - min + 1) * (double) random()) / (MAX_RANDOM_VALUE + 1.0));
 }

 /* call PQexec() and exit() on failure */
@@ -627,7 +641,7 @@ runShellCommand(CState *st, char *variable, char **argv, int argc)
     FILE       *fp;
     char        res[64];
     char       *endptr;
-    int            retval;
+    int64            retval;

     /*
      * Join arguments with whilespace separaters. Arguments starting with
@@ -700,7 +714,7 @@ runShellCommand(CState *st, char *variable, char **argv, int argc)
     }

     /* Check whether the result is an integer and assign it to the variable */
-    retval = (int) strtol(res, &endptr, 10);
+    retval = strtoll(res, &endptr, 19);
     while (*endptr != '\0' && isspace((unsigned char) *endptr))
         endptr++;
     if (*res == '\0' || *endptr != '\0')
@@ -708,7 +722,7 @@ runShellCommand(CState *st, char *variable, char **argv, int argc)
         fprintf(stderr, "%s: must return an integer ('%s' returned)\n", argv[0], res);
         return false;
     }
-    snprintf(res, sizeof(res), "%d", retval);
+    snprintf(res, sizeof(res), INT64_FORMAT, retval);
     if (!putVariable(st, "setshell", variable, res))
         return false;

@@ -956,8 +970,9 @@ top:
         if (pg_strcasecmp(argv[0], "setrandom") == 0)
         {
             char       *var;
-            int            min,
-                        max;
+            int64        min,
+                        max,
+                        rand;
             char        res[64];

             if (*argv[2] == ':')
@@ -997,15 +1012,16 @@ top:

             if (max < min || max > MAX_RANDOM_VALUE)
             {
-                fprintf(stderr, "%s: invalid maximum number %d\n", argv[0], max);
+                fprintf(stderr, "%s: invalid maximum number " INT64_FORMAT "\n", argv[0], max);
                 st->ecnt++;
                 return true;
             }
+            rand=getrand(min,max);

 #ifdef DEBUG
-            printf("min: %d max: %d random: %d\n", min, max, getrand(min, max));
+            printf("min: " INT64_FORMAT " max: " INT64_FORMAT " random: " INT64_FORMAT "\n", min, max, rand);
 #endif
-            snprintf(res, sizeof(res), "%d", getrand(min, max));
+            snprintf(res, sizeof(res), INT64_FORMAT, rand);

             if (!putVariable(st, argv[0], argv[1], res))
             {
@@ -1188,7 +1204,7 @@ init(void)
         "drop table if exists pgbench_tellers",
         "create table pgbench_tellers(tid int not null,bid int,tbalance int,filler char(84)) with (fillfactor=%d)",
         "drop table if exists pgbench_accounts",
-        "create table pgbench_accounts(aid int not null,bid int,abalance int,filler char(84)) with (fillfactor=%d)",
+        "create table pgbench_accounts(aid bigint not null,bid int,abalance int,filler char(80)) with
(fillfactor=%d)",
         "drop table if exists pgbench_history",
         "create table pgbench_history(tid int,bid int,aid int,delta int,mtime timestamp,filler char(22))"
     };
@@ -1201,7 +1217,7 @@ init(void)
     PGconn       *con;
     PGresult   *res;
     char        sql[256];
-    int            i;
+    int64        i;

     if ((con = doConnect()) == NULL)
         exit(1);
@@ -1229,13 +1245,13 @@ init(void)

     for (i = 0; i < nbranches * scale; i++)
     {
-        snprintf(sql, 256, "insert into pgbench_branches(bid,bbalance) values(%d,0)", i + 1);
+        snprintf(sql, 256, "insert into pgbench_branches(bid,bbalance) values(" INT64_FORMAT ",0)", i + 1);
         executeStatement(con, sql);
     }

     for (i = 0; i < ntellers * scale; i++)
     {
-        snprintf(sql, 256, "insert into pgbench_tellers(tid,bid,tbalance) values (%d,%d,0)",
+        snprintf(sql, 256, "insert into pgbench_tellers(tid,bid,tbalance) values (" INT64_FORMAT "," INT64_FORMAT
",0)",
                  i + 1, i / ntellers + 1);
         executeStatement(con, sql);
     }
@@ -1260,9 +1276,9 @@ init(void)

     for (i = 0; i < naccounts * scale; i++)
     {
-        int            j = i + 1;
+        int64            j = i + 1;

-        snprintf(sql, 256, "%d\t%d\t%d\t\n", j, i / naccounts + 1, 0);
+        snprintf(sql, 256, INT64_FORMAT "\t" INT64_FORMAT "\t%d\t\n", j, i / naccounts + 1, 0);
         if (PQputline(con, sql))
         {
             fprintf(stderr, "PQputline failed\n");
@@ -1270,7 +1286,7 @@ init(void)
         }

         if (j % 10000 == 0)
-            fprintf(stderr, "%d tuples done.\n", j);
+            fprintf(stderr, INT64_FORMAT " tuples done.\n", j);
     }
     if (PQputline(con, "\\.\n"))
     {