Обсуждение: Optimize referential integrity checks (todo item)
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. All regression tests pass.
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. All regression tests pass.
Вложения
On Sun, Feb 12, 2012 at 7:36 AM, 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. All regression tests pass.
The patch was not getting applied. Was seeing below message:
postgresql$ git apply /Downloads/unchanged.patch
error: src/backend/utils/adt/ri_triggers.c: already exists in working directory
Have come up with attached patch which hopefully should not have missed any of your changes.
Please verify the changes.
Regards,
Chetan
PS: would like the patch name to be something meaningful.
--
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
The Enterprise PostgreSQL Company
Website: www.enterprisedb.com
EnterpriseDB Blog : http://blogs.enterprisedb.com
Follow us on Twitter : http://www.twitter.com/enterprisedb
Вложения
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. > 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. We're in the middle of a CommitFest right now, so please add this patch to the next one if you would like it reviewed: https://commitfest.postgresql.org/action/commitfest_view/open -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Mon, Feb 13, 2012 at 15:25, Robert Haas <robertmhaas@gmail.com> wrote:
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.
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.
Yes, I wasn't expecting this to be committed, I just didn't want to lose track of it.
Will do.
It's kind of hard to say whether you've missed something, because youOn 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.
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.
On Mon, Feb 13, 2012 at 11:02, Chetan Suttraway <chetan.suttraway@enterprisedb.com> wrote:
Thank you for doing that. What command did you use? I followed the procedure on the wiki [1] but I must be doing something wrong.
[1] http://wiki.postgresql.org/wiki/Working_with_Git#Context_diffs_with_Git
They look good. Thanks again.
The patch was not getting applied. Was seeing below message:
postgresql$ git apply /Downloads/unchanged.patch
error: src/backend/utils/adt/ri_triggers.c: already exists in working directory
Have come up with attached patch which hopefully should not have missed any of your changes.
Thank you for doing that. What command did you use? I followed the procedure on the wiki [1] but I must be doing something wrong.
[1] http://wiki.postgresql.org/wiki/Working_with_Git#Context_diffs_with_Git
Please verify the changes.
They look good. Thanks again.
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. +
On 27 August 2012 19:09, Bruce Momjian <bruce@momjian.us> wrote: > > Any status on this? > Tom took care of it in the last commitfest - http://archives.postgresql.org/pgsql-hackers/2012-06/msg01075.php I think that todo item can now be marked as done. Regards, Dean
On Mon, Aug 27, 2012 at 08:35:00PM +0100, Dean Rasheed wrote: > On 27 August 2012 19:09, Bruce Momjian <bruce@momjian.us> wrote: > > > > Any status on this? > > > > Tom took care of it in the last commitfest - > http://archives.postgresql.org/pgsql-hackers/2012-06/msg01075.php > > I think that todo item can now be marked as done. Is there a TODO item for this? https://wiki.postgresql.org/wiki/Todo -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
On 27 August 2012 20:42, Bruce Momjian <bruce@momjian.us> wrote: > On Mon, Aug 27, 2012 at 08:35:00PM +0100, Dean Rasheed wrote: >> On 27 August 2012 19:09, Bruce Momjian <bruce@momjian.us> wrote: >> > >> > Any status on this? >> > >> >> Tom took care of it in the last commitfest - >> http://archives.postgresql.org/pgsql-hackers/2012-06/msg01075.php >> >> I think that todo item can now be marked as done. > > Is there a TODO item for this? > > https://wiki.postgresql.org/wiki/Todo > It's listed under https://wiki.postgresql.org/wiki/Todo#Referential_Integrity I think the main points mentioned there have now all been taken care of. Regards, Dean
On Mon, Aug 27, 2012 at 09:10:35PM +0100, Dean Rasheed wrote: > On 27 August 2012 20:42, Bruce Momjian <bruce@momjian.us> wrote: > > On Mon, Aug 27, 2012 at 08:35:00PM +0100, Dean Rasheed wrote: > >> On 27 August 2012 19:09, Bruce Momjian <bruce@momjian.us> wrote: > >> > > >> > Any status on this? > >> > > >> > >> Tom took care of it in the last commitfest - > >> http://archives.postgresql.org/pgsql-hackers/2012-06/msg01075.php > >> > >> I think that todo item can now be marked as done. > > > > Is there a TODO item for this? > > > > https://wiki.postgresql.org/wiki/Todo > > > > It's listed under https://wiki.postgresql.org/wiki/Todo#Referential_Integrity > > I think the main points mentioned there have now all been taken care of. Ah, got it. Marked as done. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Bruce Momjian <bruce@momjian.us> writes: > On Mon, Aug 27, 2012 at 09:10:35PM +0100, Dean Rasheed wrote: >> It's listed under https://wiki.postgresql.org/wiki/Todo#Referential_Integrity >> >> I think the main points mentioned there have now all been taken care of. > Ah, got it. Marked as done. IMO the second point is done but the first is not: there's still a question of whether we could remove the trigger-time checks for equality now that there's an upstream filter. Possibly break the TODO entry in two so that you can properly show what's done. regards, tom lane
On Mon, Aug 27, 2012 at 04:37:25PM -0400, Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > On Mon, Aug 27, 2012 at 09:10:35PM +0100, Dean Rasheed wrote: > >> It's listed under https://wiki.postgresql.org/wiki/Todo#Referential_Integrity > >> > >> I think the main points mentioned there have now all been taken care of. > > > Ah, got it. Marked as done. > > IMO the second point is done but the first is not: there's still a > question of whether we could remove the trigger-time checks for equality > now that there's an upstream filter. Possibly break the TODO entry in > two so that you can properly show what's done. OK, can someone do this for me? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Bruce Momjian <bruce@momjian.us> writes:
> On Mon, Aug 27, 2012 at 04:37:25PM -0400, Tom Lane wrote:
>> IMO the second point is done but the first is not: there's still a
>> question of whether we could remove the trigger-time checks for equality
>> now that there's an upstream filter. Possibly break the TODO entry in
>> two so that you can properly show what's done.
> OK, can someone do this for me?
Done.
regards, tom lane