Thread

1. Foreign keys in pgbench

   Jeff Janes <jeff.janes@gmail.com> — 2012-05-13T17:07:21Z

   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
   

2. Re: Foreign keys in pgbench

   Peter Geoghegan <peter@2ndquadrant.com> — 2012-05-13T19:03:36Z

   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
   
   

3. Re: Foreign keys in pgbench

   Daniel Farina <daniel@heroku.com> — 2012-05-19T16:19:36Z

   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
   
   

4. Re: Foreign keys in pgbench

   Tom Lane <tgl@sss.pgh.pa.us> — 2012-06-19T22:35:15Z

   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