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),aid int 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.
  
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq

Reply via email to