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

On 13 May 2012 18:07, Jeff Janes <jeff(dot)janes(at)gmail(dot)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(at)2ndquadrant(dot)com> wrote:
> On 13 May 2012 18:07, Jeff Janes <jeff(dot)janes(at)gmail(dot)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(dot)janes(at)gmail(dot)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