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(at)gmail(dot)com> wrote:
>
> On Sat, Feb 11, 2012 at 9:06 PM, Vik Reykja <vikreykja(at)gmail(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +