Some minor changes to pgbench

2006/08/22
* New schema contributed by Joshua Drake

* The schema now uses foreign keys to more accurately reflect a finacial DDL

* The history table now has a primary key that uses a serial

* The respective balance columns have been increased to int8 to deal
with larger values

* Initalization will be done in a new schema/namespace, pgbench will
exit if this schema/namespace exists

* The new DDL should allow both Mammoth Replicator and Slony to be
tested using pgbench (at least basic replication)

--

    === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
    Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/


*** pgbench.c    2006-08-22 20:14:54.525176500 -0700
--- pgbench_new.c    2006-08-22 21:50:40.072250750 -0700
***************
*** 136,146 ****
  static char *tpc_b = {
      "\\set nbranches :tps\n"
      "\\set ntellers 10 * :tps\n"
!     "\\set naccounts 100000 * :tps\n"
      "\\setrandom aid 1 :naccounts\n"
      "\\setrandom bid 1 :nbranches\n"
      "\\setrandom tid 1 :ntellers\n"
      "\\setrandom delta -5000 5000\n"
      "BEGIN;\n"
      "UPDATE accounts SET abalance = abalance + :delta WHERE aid = :aid;\n"
      "SELECT abalance FROM accounts WHERE aid = :aid;\n"
--- 136,147 ----
  static char *tpc_b = {
      "\\set nbranches :tps\n"
      "\\set ntellers 10 * :tps\n"
!     "\\set naccounts 100000 * :tps\n"
      "\\setrandom aid 1 :naccounts\n"
      "\\setrandom bid 1 :nbranches\n"
      "\\setrandom tid 1 :ntellers\n"
      "\\setrandom delta -5000 5000\n"
+     "SET SEARCH_PATH = pgbench;\n"
      "BEGIN;\n"
      "UPDATE accounts SET abalance = abalance + :delta WHERE aid = :aid;\n"
      "SELECT abalance FROM accounts WHERE aid = :aid;\n"
***************
*** 154,164 ****
  static char *simple_update = {
      "\\set nbranches :tps\n"
      "\\set ntellers 10 * :tps\n"
!     "\\set naccounts 100000 * :tps\n"
      "\\setrandom aid 1 :naccounts\n"
      "\\setrandom bid 1 :nbranches\n"
      "\\setrandom tid 1 :ntellers\n"
      "\\setrandom delta -5000 5000\n"
      "BEGIN;\n"
      "UPDATE accounts SET abalance = abalance + :delta WHERE aid = :aid;\n"
      "SELECT abalance FROM accounts WHERE aid = :aid;\n"
--- 155,166 ----
  static char *simple_update = {
      "\\set nbranches :tps\n"
      "\\set ntellers 10 * :tps\n"
!         "\\set naccounts 100000 * :tps\n"
      "\\setrandom aid 1 :naccounts\n"
      "\\setrandom bid 1 :nbranches\n"
      "\\setrandom tid 1 :ntellers\n"
      "\\setrandom delta -5000 5000\n"
+     "SET SEARCH_PATH = pgbench;\n"
      "BEGIN;\n"
      "UPDATE accounts SET abalance = abalance + :delta WHERE aid = :aid;\n"
      "SELECT abalance FROM accounts WHERE aid = :aid;\n"
***************
*** 168,175 ****

  /* -S case */
  static char *select_only = {
!     "\\set naccounts 100000 * :tps\n"
      "\\setrandom aid 1 :naccounts\n"
      "SELECT abalance FROM accounts WHERE aid = :aid;\n"
  };

--- 170,178 ----

  /* -S case */
  static char *select_only = {
!     "\\set naccounts 100000 * :tps\n"
      "\\setrandom aid 1 :naccounts\n"
+     "SET SEARCH_PATH = pgbench;\n"
      "SELECT abalance FROM accounts WHERE aid = :aid;\n"
  };

***************
*** 215,221 ****
          return (NULL);
      }

!     res = PQexec(con, "SET search_path = public");
      if (PQresultStatus(res) != PGRES_COMMAND_OK)
      {
          fprintf(stderr, "%s", PQerrorMessage(con));
--- 218,224 ----
          return (NULL);
      }

!     res = PQexec(con, "SET search_path = pgbench");
      if (PQresultStatus(res) != PGRES_COMMAND_OK)
      {
          fprintf(stderr, "%s", PQerrorMessage(con));
***************
*** 715,743 ****
      PGconn       *con;
      PGresult   *res;
      static char *DDLs[] = {
          "drop table branches",
!         "create table branches(bid int not null,bbalance int,filler char(88))",
          "drop table tellers",
!         "create table tellers(tid int not null,bid int,tbalance int,filler char(84))",
          "drop table accounts",
!         "create table accounts(aid int not null,bid int,abalance int,filler char(84))",
          "drop table history",
!     "create table history(tid int,bid int,aid int,delta int,mtime timestamp,filler char(22))"};
!     static char *DDLAFTERs[] = {
!         "alter table branches add primary key (bid)",
!         "alter table tellers add primary key (tid)",
!     "alter table accounts add primary key (aid)"};
!

      char        sql[256];

      int            i;
!
      if ((con = doConnect()) == NULL)
          exit(1);

      for (i = 0; i < (sizeof(DDLs) / sizeof(char *)); i++)
!     {
          res = PQexec(con, DDLs[i]);
          if (strncmp(DDLs[i], "drop", 4) && PQresultStatus(res) != PGRES_COMMAND_OK)
          {
--- 718,752 ----
      PGconn       *con;
      PGresult   *res;
      static char *DDLs[] = {
+             "drop schema pgbench cascade",
+             "create schema pgbench",
+             "set search_path = pgbench",
          "drop table branches",
!         "create table branches(bid int not null primary key,bbalance int8,filler char(88))",
          "drop table tellers",
!         "create table tellers(tid int not null primary key,bid int references branches(bid),tbalance int8,filler
char(84))",
          "drop table accounts",
!         "create table accounts(aid int not null primary key,bid int references branches(bid),abalance int8,filler
char(84))",
          "drop table history",
!         "create table history(hid serial primary key, tid int references tellers(tid),bid int references branches
(bid),aidint references accounts (aid),delta int,mtime timestamp,filler char(22))"}; 

      char        sql[256];

      int            i;
!
      if ((con = doConnect()) == NULL)
          exit(1);
+     /* Let's make sure we are not going to blow anything away */
+
+     res = PQexec(con, "select nspname from pg_namespace where nspname = 'pgbench'");
+         if (PQntuples(res) != 0)
+         {
+             fprintf(stderr, "pgbench schema already exists, exiting...\n");
+             exit(1);
+         }

      for (i = 0; i < (sizeof(DDLs) / sizeof(char *)); i++)
!     {
          res = PQexec(con, DDLs[i]);
          if (strncmp(DDLs[i], "drop", 4) && PQresultStatus(res) != PGRES_COMMAND_OK)
          {
***************
*** 848,867 ****
  #endif   /* NOT_USED */
          }
      }
-     fprintf(stderr, "set primary key...\n");
-     for (i = 0; i < (sizeof(DDLAFTERs) / sizeof(char *)); i++)
-     {
-         res = PQexec(con, DDLAFTERs[i]);
-         if (PQresultStatus(res) != PGRES_COMMAND_OK)
-         {
-             fprintf(stderr, "%s", PQerrorMessage(con));
-             exit(1);
-         }
-         PQclear(res);
-     }
-
      /* vacuum */
!     fprintf(stderr, "vacuum...");
      res = PQexec(con, "vacuum analyze");
      if (PQresultStatus(res) != PGRES_COMMAND_OK)
      {
--- 857,864 ----
  #endif   /* NOT_USED */
          }
      }
      /* vacuum */
!     fprintf(stderr, "vacuum analyze...");
      res = PQexec(con, "vacuum analyze");
      if (PQresultStatus(res) != PGRES_COMMAND_OK)
      {
*** README.pgbench    2006-08-22 22:06:04.810043250 -0700
--- README.pgbench.new    2006-08-22 22:02:32.844796250 -0700
***************
*** 43,52 ****

      pgbench -i <dbname>

!       where <dbname> is the name of database. pgbench uses four tables
!       accounts, branches, history and tellers. These tables will be
!       destroyed. Be very careful if you have tables having same
!       names. Default test data contains:

      table        # of tuples
      -------------------------
--- 43,53 ----

      pgbench -i <dbname>

!       where <dbname> is the name of database. pgbench will create a new
!       schema named pgbench within your database to create its tables. If
!       pgbench detects that the schema alreday exists it will exit.
!       pgbench uses four tables accounts, branches, history and tellers.
!       Default test data contains:

      table        # of tuples
      -------------------------
***************
*** 235,240 ****
--- 236,253 ----

  o History

+ 2006/08/22
+     * New schema contributed by Joshua Drake
+
+     * The schema now uses foreign keys to more accurately reflect a finacial DDL
+     * The history table now has a primary key that uses a serial
+     * The respective balance columns have been increased to int8 to deal with
+     larger values
+     * Initalization will be done in a new schema/namespace, pgbench will exit
+     if this schema/namespace exists
+         * The new DDL should allow both Mammoth Replicator and Slony to be tested using
+     pgbench (at least basic replication)
+
  2006/07/26
      * New features contributed by Tomoaki Sato.