Foreign keys in pgbench
I think that pgbench should it make it easy to assess the impact of
foreign key constraints.
The attached adds a --foreign-keys option to initialization mode which
creates all the relevant constraints between the default tables.
I changed the order of the table DDLs so that upon reinitialization
the tables are dropped in an order that does not lead to dependency
problems.
I'll add it CFNext.
Thanks,
Jeff
Attachments:
pgbench_key_v1.patchapplication/octet-stream; name=pgbench_key_v1.patchDownload
diff --git a/contrib/pgbench/pgbench.c b/contrib/pgbench/pgbench.c
new file mode 100644
index 2e147d9..182293e
*** a/contrib/pgbench/pgbench.c
--- b/contrib/pgbench/pgbench.c
*************** int fillfactor = 100;
*** 125,130 ****
--- 125,135 ----
int unlogged_tables = 0;
/*
+ * create foreign key contraints on the tables
+ */
+ int foreign_key = 0;
+
+ /*
* tablespace selection
*/
char *tablespace = NULL;
*************** usage(const char *progname)
*** 349,354 ****
--- 354,361 ----
" create tables in the specified tablespace\n"
" --unlogged-tables\n"
" create tables as unlogged tables\n"
+ " --foreign-keys\n"
+ " create foreign key constraints between tables\n"
"\nBenchmarking options:\n"
" -c NUM number of concurrent database clients (default: 1)\n"
" -C establish new connection for each transaction\n"
*************** init(void)
*** 1274,1282 ****
};
struct ddlinfo DDLs[] = {
{
! "pgbench_branches",
! "bid int not null,bbalance int,filler char(88)",
! 1
},
{
"pgbench_tellers",
--- 1281,1289 ----
};
struct ddlinfo DDLs[] = {
{
! "pgbench_history",
! "tid int,bid int,aid int,delta int,mtime timestamp,filler char(22)",
! 0
},
{
"pgbench_tellers",
*************** init(void)
*** 1289,1297 ****
1
},
{
! "pgbench_history",
! "tid int,bid int,aid int,delta int,mtime timestamp,filler char(22)",
! 0
}
};
static char *DDLAFTERs[] = {
--- 1296,1304 ----
1
},
{
! "pgbench_branches",
! "bid int not null,bbalance int,filler char(88)",
! 1
}
};
static char *DDLAFTERs[] = {
*************** init(void)
*** 1299,1304 ****
--- 1306,1318 ----
"alter table pgbench_tellers add primary key (tid)",
"alter table pgbench_accounts add primary key (aid)"
};
+ static char *DDLKEYs[] = {
+ "alter table pgbench_tellers add foreign key (bid) references pgbench_branches",
+ "alter table pgbench_accounts add foreign key (bid) references pgbench_branches",
+ "alter table pgbench_history add foreign key (bid) references pgbench_branches",
+ "alter table pgbench_history add foreign key (tid) references pgbench_tellers",
+ "alter table pgbench_history add foreign key (aid) references pgbench_accounts",
+ };
PGconn *con;
PGresult *res;
*************** init(void)
*** 1420,1425 ****
--- 1434,1448 ----
executeStatement(con, buffer);
}
+
+
+ if (foreign_key) {
+ fprintf(stderr, "set foreign key...\n");
+ for (i = 0; i < lengthof(DDLKEYs); i++)
+ {
+ executeStatement(con, DDLKEYs[i]);
+ }
+ }
/* vacuum */
fprintf(stderr, "vacuum...");
*************** main(int argc, char **argv)
*** 1864,1869 ****
--- 1887,1893 ----
{"index-tablespace", required_argument, NULL, 3},
{"tablespace", required_argument, NULL, 2},
{"unlogged-tables", no_argument, &unlogged_tables, 1},
+ {"foreign-keys", no_argument, &foreign_key, 1},
{NULL, 0, NULL, 0}
};
diff --git a/doc/src/sgml/pgbench.sgml b/doc/src/sgml/pgbench.sgml
new file mode 100644
index 16ac69f..226ccc3
*** a/doc/src/sgml/pgbench.sgml
--- b/doc/src/sgml/pgbench.sgml
*************** pgbench <optional> <replaceable>options<
*** 209,214 ****
--- 209,223 ----
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>--foreign-keys</option></term>
+ <listitem>
+ <para>
+ Create foreign key contraints between the standard tables
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</para>
On 13 May 2012 18:07, Jeff Janes <jeff.janes@gmail.com> wrote:
I think that pgbench should it make it easy to assess the impact of
foreign key constraints.
I agree in principle. I favour being more inclusive about pgbench
options, even if the need for such options is only marginal, which
this isn't - I personally would have found it very useful recently.
pgbench is an expert-level tool, and I find arguments against adding
more options along the lines of "that will distract beginner users"
completely unconvincing.
--
Peter Geoghegan http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services
On Sun, May 13, 2012 at 3:03 PM, Peter Geoghegan <peter@2ndquadrant.com> wrote:
On 13 May 2012 18:07, Jeff Janes <jeff.janes@gmail.com> wrote:
I think that pgbench should it make it easy to assess the impact of
foreign key constraints.I agree in principle. I favour being more inclusive about pgbench
options, even if the need for such options is only marginal, which
this isn't - I personally would have found it very useful recently.
pgbench is an expert-level tool, and I find arguments against adding
more options along the lines of "that will distract beginner users"
completely unconvincing.
If it is a common position that people should probably be making
better (to say, more) use of foreign key constraints -- something I
agree with, although my colleagues have identified non-performance
usability gaps that have to do with unit testing, resetting tables,
deferred constraints, and cascading deletes -- it's probably a good
idea to do our best to ensure that using them does not regress
performance badly, at least.
I might give a different answer if FK constraints had better
penetration in applications and they were viewed as "just the cost of
doing business", but that is not the case.
All in all, though, I think the usability problems trump performance,
and what's interesting is those usability problems are only seen in
development, and not production. I mention this information because
it may help you qualify my level of support for this idea.
The goal would be for foreign keys to become usable enough that a
framework like ActiveRecord might just use them by default. The
recent inclusion of much more powerful query compilers, default(!) use
of named prepared statements (perhaps even prematurely, given the
problem with generic selectivity estimates), and hstore suggests that
this time might yet come. Caveat being that I haven't researched any
specific objections from ActiveRecord people yet.
--
fdr
Jeff Janes <jeff.janes@gmail.com> writes:
I think that pgbench should it make it easy to assess the impact of
foreign key constraints.
The attached adds a --foreign-keys option to initialization mode which
creates all the relevant constraints between the default tables.
I had need of this for testing what I'm doing with foreign keys,
so I went ahead and gave it a quick review (just cosmetic changes)
and committed it.
regards, tom lane