Re: [HACKERS] Optimize referential integrity checks (todo item)

2012-08-27 Thread Bruce Momjian

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 robertmh...@gmail.com wrote:
 
 On Sat, Feb 11, 2012 at 9:06 PM, Vik Reykja vikrey...@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  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Optimize referential integrity checks (todo item)

2012-08-27 Thread Dean Rasheed
On 27 August 2012 19:09, Bruce Momjian br...@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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Optimize referential integrity checks (todo item)

2012-08-27 Thread Bruce Momjian
On Mon, Aug 27, 2012 at 08:35:00PM +0100, Dean Rasheed wrote:
 On 27 August 2012 19:09, Bruce Momjian br...@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  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Optimize referential integrity checks (todo item)

2012-08-27 Thread Dean Rasheed
On 27 August 2012 20:42, Bruce Momjian br...@momjian.us wrote:
 On Mon, Aug 27, 2012 at 08:35:00PM +0100, Dean Rasheed wrote:
 On 27 August 2012 19:09, Bruce Momjian br...@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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Optimize referential integrity checks (todo item)

2012-08-27 Thread Bruce Momjian
On Mon, Aug 27, 2012 at 09:10:35PM +0100, Dean Rasheed wrote:
 On 27 August 2012 20:42, Bruce Momjian br...@momjian.us wrote:
  On Mon, Aug 27, 2012 at 08:35:00PM +0100, Dean Rasheed wrote:
  On 27 August 2012 19:09, Bruce Momjian br...@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  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Optimize referential integrity checks (todo item)

2012-08-27 Thread Tom Lane
Bruce Momjian br...@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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Optimize referential integrity checks (todo item)

2012-08-27 Thread Bruce Momjian
On Mon, Aug 27, 2012 at 04:37:25PM -0400, Tom Lane wrote:
 Bruce Momjian br...@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  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Optimize referential integrity checks (todo item)

2012-08-27 Thread Tom Lane
Bruce Momjian br...@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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Optimize referential integrity checks (todo item)

2012-02-13 Thread Chetan Suttraway
On Sun, Feb 12, 2012 at 7:36 AM, Vik Reykja vikrey...@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

 Website: www.enterprisedb.com
EnterpriseDB Blog : http://blogs.enterprisedb.com
Follow us on Twitter : http://www.twitter.com/enterprisedb
diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c
index 03a974a..09bacb7 100644
--- a/src/backend/utils/adt/ri_triggers.c
+++ b/src/backend/utils/adt/ri_triggers.c
@@ -205,11 +205,11 @@ static void ri_BuildQueryKeyFull(RI_QueryKey *key,
 static void ri_BuildQueryKeyPkCheck(RI_QueryKey *key,
 		const RI_ConstraintInfo *riinfo,
 		int32 constr_queryno);
-static bool ri_KeysEqual(Relation rel, HeapTuple oldtup, HeapTuple newtup,
+static bool ri_KeysUnchanged(Relation rel, HeapTuple oldtup, HeapTuple newtup,
 			 const RI_ConstraintInfo *riinfo, bool rel_is_pk);
-static bool ri_AllKeysUnequal(Relation rel, HeapTuple oldtup, HeapTuple newtup,
+static bool ri_AllKeysChanged(Relation rel, HeapTuple oldtup, HeapTuple newtup,
   const RI_ConstraintInfo *riinfo, bool rel_is_pk);
-static bool ri_OneKeyEqual(Relation rel, int column,
+static bool ri_OneKeyUnchanged(Relation rel, int column,
 			   HeapTuple oldtup, HeapTuple newtup,
 			   const RI_ConstraintInfo *riinfo, bool rel_is_pk);
 static bool ri_AttributesEqual(Oid eq_opr, Oid typeid,
@@ -932,9 +932,9 @@ RI_FKey_noaction_upd(PG_FUNCTION_ARGS)
 			}
 
 			/*
-			 * No need to check anything if old and new keys are equal
+			 * No need to check anything if old and new keys are unchanged
 			 */
-			if (ri_KeysEqual(pk_rel, old_row, new_row, riinfo, true))
+			if (ri_KeysUnchanged(pk_rel, old_row, new_row, riinfo, true))
 			{
 heap_close(fk_rel, RowShareLock);
 return PointerGetDatum(NULL);
@@ -1281,9 +1281,9 @@ RI_FKey_cascade_upd(PG_FUNCTION_ARGS)
 			}
 
 			/*
-			 * No need to do anything if old and new keys are equal
+			 * No need to do anything if old and new keys are unchanged
 			 */
-			if (ri_KeysEqual(pk_rel, old_row, new_row, riinfo, true))
+			if (ri_KeysUnchanged(pk_rel, old_row, new_row, riinfo, true))
 			{
 heap_close(fk_rel, RowExclusiveLock);
 return PointerGetDatum(NULL);
@@ -1646,9 +1646,9 @@ RI_FKey_restrict_upd(PG_FUNCTION_ARGS)
 			}
 
 			/*
-			 * No need to check anything if old and new keys are equal
+			 * No need to check anything if old and new keys are unchanged
 			 */
-			if (ri_KeysEqual(pk_rel, old_row, new_row, riinfo, true))
+			if (ri_KeysUnchanged(pk_rel, old_row, new_row, riinfo, true))
 			{
 heap_close(fk_rel, RowShareLock);
 return PointerGetDatum(NULL);
@@ -1993,9 +1993,9 @@ RI_FKey_setnull_upd(PG_FUNCTION_ARGS)
 			}
 
 			/*
-			 * No need to do anything if old and new keys are equal
+			 * No need to do anything if old and new keys are unchanged
 			 */
-			if (ri_KeysEqual(pk_rel, old_row, new_row, riinfo, true))
+			if (ri_KeysUnchanged(pk_rel, old_row, new_row, riinfo, true))
 			{
 heap_close(fk_rel, RowExclusiveLock);
 return PointerGetDatum(NULL);
@@ -2012,13 +2012,10 @@ RI_FKey_setnull_upd(PG_FUNCTION_ARGS)
 			 * our cached plan, unless the update happens to change all
 			 * columns in the key.	Fortunately, for the most common case of a
 			 * single-column foreign key, this will be true.
-			 *
-			 * In case you're wondering, the inequality check works because we
-			 * know that the old key value has no NULLs (see above).
 			 */
 
 			use_cached_query = (riinfo.confmatchtype == FKCONSTR_MATCH_FULL) ||
-ri_AllKeysUnequal(pk_rel, old_row, new_row,
+ri_AllKeysChanged(pk_rel, old_row, new_row,
   riinfo, true);
 
 			/*
@@ -2064,7 +2061,7 @@ RI_FKey_setnull_upd(PG_FUNCTION_ARGS)
 	 * to changed columns in pk_rel's key
 	 */
 	if (riinfo.confmatchtype == FKCONSTR_MATCH_FULL ||
-		!ri_OneKeyEqual(pk_rel, i, old_row, new_row,
+		!ri_OneKeyUnchanged(pk_rel, i, old_row, new_row,
 		riinfo, true))
 	{
 		appendStringInfo(querybuf,
@@ -2389,9 +2386,9 @@ RI_FKey_setdefault_upd(PG_FUNCTION_ARGS)
 			}
 
 			/*
-			 * No need to do anything if old and new keys are equal
+			 * No need to do anything if old and new keys are unchanged
 			 */
-			if 

Re: [HACKERS] Optimize referential integrity checks (todo item)

2012-02-13 Thread Robert Haas
On Sat, Feb 11, 2012 at 9:06 PM, Vik Reykja vikrey...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Optimize referential integrity checks (todo item)

2012-02-13 Thread Vik Reykja
On Mon, Feb 13, 2012 at 15:25, Robert Haas robertmh...@gmail.com wrote:

 On Sat, Feb 11, 2012 at 9:06 PM, Vik Reykja vikrey...@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.


Re: [HACKERS] Optimize referential integrity checks (todo item)

2012-02-13 Thread Vik Reykja
On Mon, Feb 13, 2012 at 11:02, Chetan Suttraway 
chetan.suttra...@enterprisedb.com wrote:

 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.