Some minor changes to pgbench - Mailing list pgsql-patches
From | Joshua D. Drake |
---|---|
Subject | Some minor changes to pgbench |
Date | |
Msg-id | 44EBE2F6.7000303@commandprompt.com Whole thread Raw |
Responses |
Re: Some minor changes to pgbench
(Tom Lane <tgl@sss.pgh.pa.us>)
|
List | pgsql-patches |
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.
pgsql-patches by date: