Re: Optimize referential integrity checks (todo item)

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.