Re: Optimize referential integrity checks (todo item)

Any status on this?

---------------------------------------------------------------------------

On Mon, Feb 13, 2012 at 04:34:51PM +0100, Vik Reykja wrote:
> On Mon, Feb 13, 2012 at 15:25, Robert Haas <robertmhaas@gmail.com> wrote:
> 
>     On Sat, Feb 11, 2012 at 9:06 PM, Vik Reykja <vikreykja@gmail.com> wrote:
>     > I decided to take a crack at the todo item created from the following
>     post:
>     > http://archives.postgresql.org/pgsql-performance/2005-10/msg00458.php
>     >
>     > The attached patch makes the desired changes in both code and function
>     > naming.
>     >
>     > It seemed quite easy to do but wasn't marked as easy on the todo, so I'm
>     > wondering if I've missed something.
> 
>     It's kind of hard to say whether you've missed something, because you
>     haven't really explained what problem this is solving; the thread you
>     linked too isn't very clear about that either.  At first blush, it
>     seems like you've renamed a bunch of stuff without making very much
>     change to what actually happens.  Changing lots of copies of "equal"
>     to "unchanged" doesn't seem to me to be accomplishing anything.
> 
> 
> It's very simple really, and most of it is indeed renaming the functions.  The
> "problem" this solves is that foreign key constraints are sometimes checked
> when they don't need to be.  See my example below.
>  
> 
>     > All regression tests pass.
> 
>     You should add some new ones showing how this patch improves the
>     behavior relative to the previous code.  Or if you can't, then you
>     should provide a complete, self-contained test case that a reviewer
>     can use to see how your proposed changes improve things.
> 
> 
> I have no idea how a regression test would be able to see this change, so
> here's a test case that you can follow with the debugger.
> 
> /* initial setup */
> create table a (x int, y int, primary key (x, y));
> create table b (x int, y int, z int, foreign key (x, y) references a);
> insert into a values (1, 2);
> insert into b values (1, null, 3);
> 
> /* seeing the difference */
> update b set z=0;
> 
> When that update is run, it will check if the FK (x, y) has changed to know if
> it needs to verify that the values are present in the other table.  The
> equality functions that do that don't consider two nulls to be equal (per sql
> logic) and so reverified the constraint.  Tom noticed that it didn't need to
> because it hadn't really changed.
> 
> In the above example, the current code will recheck the constraint and the new
> code won't.  It's not really testing equality anymore (because null does not
> equal null), so I renamed them causing a lot of noise in the diff.
>  
> 
>     We're in the middle of a CommitFest right now,
> 
> 
> Yes, I wasn't expecting this to be committed, I just didn't want to lose track
> of it.
>  
> 
>     so please add this patch to the next one if you would like it reviewed:
> 
>     https://commitfest.postgresql.org/action/commitfest_view/open
> 
> 
> Will do.
> 

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +