Re: minimal update

Kenneth Marshall wrote:
> On Wed, Oct 22, 2008 at 06:05:26PM -0400, Tom Lane wrote:
>
>> Simon Riggs <simon@2ndQuadrant.com> writes:
>>
>>>> On Wed, Oct 22, 2008 at 3:24 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>>>
>>>>> "Minimal" really fails to convey the point here IMHO.  How about
>>>>> something like "suppress_no_op_updates_trigger"?
>>>>>
>>> I think it means something to us, but "no op" is a very technical phrase
>>> that probably doesn't travel very well.
>>>
>> Agreed --- I was hoping someone could improve on that part.  The only
>> other words I could come up with were "empty" and "useless", neither of
>> which seem quite le mot juste ...
>>
>>             regards, tom lane
>>
>>
> redundant?
>
>
>

I think I like this best of all the suggestions -
suppress_redundant_updates_trigger() is what I have now.

If there's no further discussion, I'll go ahead and commit this in a day
or two.

cheers

andrew
? GNUmakefile
? config.log
? config.status
? contrib/spi/.deps
? src/Makefile.global
? src/backend/postgres
? src/backend/access/common/.deps
? src/backend/access/gin/.deps
? src/backend/access/gist/.deps
? src/backend/access/hash/.deps
? src/backend/access/heap/.deps
? src/backend/access/index/.deps
? src/backend/access/nbtree/.deps
? src/backend/access/transam/.deps
? src/backend/bootstrap/.deps
? src/backend/catalog/.deps
? src/backend/catalog/postgres.bki
? src/backend/catalog/postgres.description
? src/backend/catalog/postgres.shdescription
? src/backend/commands/.deps
? src/backend/executor/.deps
? src/backend/lib/.deps
? src/backend/libpq/.deps
? src/backend/main/.deps
? src/backend/nodes/.deps
? src/backend/optimizer/geqo/.deps
? src/backend/optimizer/path/.deps
? src/backend/optimizer/plan/.deps
? src/backend/optimizer/prep/.deps
? src/backend/optimizer/util/.deps
? src/backend/parser/.deps
? src/backend/port/.deps
? src/backend/postmaster/.deps
? src/backend/regex/.deps
? src/backend/rewrite/.deps
? src/backend/snowball/.deps
? src/backend/snowball/snowball_create.sql
? src/backend/storage/buffer/.deps
? src/backend/storage/file/.deps
? src/backend/storage/freespace/.deps
? src/backend/storage/ipc/.deps
? src/backend/storage/large_object/.deps
? src/backend/storage/lmgr/.deps
? src/backend/storage/page/.deps
? src/backend/storage/smgr/.deps
? src/backend/tcop/.deps
? src/backend/tsearch/.deps
? src/backend/utils/.deps
? src/backend/utils/probes.h
? src/backend/utils/adt/.deps
? src/backend/utils/cache/.deps
? src/backend/utils/error/.deps
? src/backend/utils/fmgr/.deps
? src/backend/utils/hash/.deps
? src/backend/utils/init/.deps
? src/backend/utils/mb/.deps
? src/backend/utils/mb/conversion_procs/conversion_create.sql
? src/backend/utils/mb/conversion_procs/ascii_and_mic/.deps
? src/backend/utils/mb/conversion_procs/cyrillic_and_mic/.deps
? src/backend/utils/mb/conversion_procs/euc_cn_and_mic/.deps
? src/backend/utils/mb/conversion_procs/euc_jis_2004_and_shift_jis_2004/.deps
? src/backend/utils/mb/conversion_procs/euc_jp_and_sjis/.deps
? src/backend/utils/mb/conversion_procs/euc_kr_and_mic/.deps
? src/backend/utils/mb/conversion_procs/euc_tw_and_big5/.deps
? src/backend/utils/mb/conversion_procs/latin2_and_win1250/.deps
? src/backend/utils/mb/conversion_procs/latin_and_mic/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_ascii/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_big5/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_cyrillic/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_euc_cn/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_euc_jis_2004/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_euc_jp/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_euc_kr/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_euc_tw/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_gb18030/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_gbk/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_iso8859/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_iso8859_1/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_johab/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_shift_jis_2004/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_sjis/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_uhc/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_win/.deps
? src/backend/utils/misc/.deps
? src/backend/utils/mmgr/.deps
? src/backend/utils/resowner/.deps
? src/backend/utils/sort/.deps
? src/backend/utils/time/.deps
? src/bin/initdb/.deps
? src/bin/initdb/initdb
? src/bin/pg_config/.deps
? src/bin/pg_config/pg_config
? src/bin/pg_controldata/.deps
? src/bin/pg_controldata/pg_controldata
? src/bin/pg_ctl/.deps
? src/bin/pg_ctl/pg_ctl
? src/bin/pg_dump/.deps
? src/bin/pg_dump/pg_dump
? src/bin/pg_dump/pg_dumpall
? src/bin/pg_dump/pg_restore
? src/bin/pg_resetxlog/.deps
? src/bin/pg_resetxlog/pg_resetxlog
? src/bin/psql/.deps
? src/bin/psql/psql
? src/bin/scripts/.deps
? src/bin/scripts/clusterdb
? src/bin/scripts/createdb
? src/bin/scripts/createlang
? src/bin/scripts/createuser
? src/bin/scripts/dropdb
? src/bin/scripts/droplang
? src/bin/scripts/dropuser
? src/bin/scripts/reindexdb
? src/bin/scripts/vacuumdb
? src/include/pg_config.h
? src/include/stamp-h
? src/interfaces/ecpg/compatlib/.deps
? src/interfaces/ecpg/compatlib/exports.list
? src/interfaces/ecpg/compatlib/libecpg_compat.so.3.1
? src/interfaces/ecpg/ecpglib/.deps
? src/interfaces/ecpg/ecpglib/exports.list
? src/interfaces/ecpg/ecpglib/libecpg.so.6.1
? src/interfaces/ecpg/include/ecpg_config.h
? src/interfaces/ecpg/pgtypeslib/.deps
? src/interfaces/ecpg/pgtypeslib/exports.list
? src/interfaces/ecpg/pgtypeslib/libpgtypes.so.3.1
? src/interfaces/ecpg/preproc/.deps
? src/interfaces/ecpg/preproc/ecpg
? src/interfaces/libpq/.deps
? src/interfaces/libpq/exports.list
? src/interfaces/libpq/libpq.so.5.2
? src/pl/plperl/.deps
? src/pl/plperl/SPI.c
? src/pl/plpgsql/src/.deps
? src/pl/plpython/.deps
? src/pl/tcl/.deps
? src/pl/tcl/modules/pltcl_delmod
? src/pl/tcl/modules/pltcl_listmod
? src/pl/tcl/modules/pltcl_loadmod
? src/port/.deps
? src/port/pg_config_paths.h
? src/test/regress/.deps
? src/test/regress/log
? src/test/regress/pg_regress
? src/test/regress/results
? src/test/regress/testtablespace
? src/test/regress/tmp_check
? src/test/regress/expected/constraints.out
? src/test/regress/expected/copy.out
? src/test/regress/expected/create_function_1.out
? src/test/regress/expected/create_function_2.out
? src/test/regress/expected/largeobject.out
? src/test/regress/expected/largeobject_1.out
? src/test/regress/expected/misc.out
? src/test/regress/expected/tablespace.out
? src/test/regress/sql/constraints.sql
? src/test/regress/sql/copy.sql
? src/test/regress/sql/create_function_1.sql
? src/test/regress/sql/create_function_2.sql
? src/test/regress/sql/largeobject.sql
? src/test/regress/sql/misc.sql
? src/test/regress/sql/tablespace.sql
? src/timezone/.deps
? src/timezone/zic
Index: doc/src/sgml/func.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.450
diff -c -r1.450 func.sgml
*** doc/src/sgml/func.sgml    14 Oct 2008 17:12:32 -0000    1.450
--- doc/src/sgml/func.sgml    29 Oct 2008 19:26:53 -0000
***************
*** 12817,12820 ****
--- 12817,12850 ----

    </sect1>

+   <sect1 id="functions-trigger">
+    <title>Trigger Functions</title>
+
+    <para>
+       Currently <productname>PostgreSQL</> provides one built in trigger
+       function, <function>suppress_redundant_updates_trigger</>,
+       which will prevent any update
+       that does not actually change the data in the row from taking place, in
+       contrast to the normal behaviour which always performs the update
+       regardless of whether or not the data has changed.
+     </para>
+
+     <para>
+       The <function>suppress_redundant_updates_trigger</> function can be
+       added to a table like this:
+ <programlisting>
+ CREATE TRIGGER z_min_update
+ BEFORE UPDATE ON tablename
+ FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger();
+ </programlisting>
+       In many cases, you would want to fire this trigger last for each row.
+       Bearing in mind that triggers fire in name order, you would then
+       choose a trigger name that comes after then name of any other trigger
+       you might have on the table.
+     </para>
+     <para>
+        For more information about creating triggers, see
+         <xref linkend="SQL-CREATETRIGGER">.
+     </para>
+   </sect1>
  </chapter>
Index: src/backend/utils/adt/Makefile
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/Makefile,v
retrieving revision 1.69
diff -c -r1.69 Makefile
*** src/backend/utils/adt/Makefile    19 Feb 2008 10:30:08 -0000    1.69
--- src/backend/utils/adt/Makefile    29 Oct 2008 19:26:53 -0000
***************
*** 25,31 ****
      tid.o timestamp.o varbit.o varchar.o varlena.o version.o xid.o \
      network.o mac.o inet_net_ntop.o inet_net_pton.o \
      ri_triggers.o pg_lzcompress.o pg_locale.o formatting.o \
!     ascii.o quote.o pgstatfuncs.o encode.o dbsize.o genfile.o \
      tsginidx.o tsgistidx.o tsquery.o tsquery_cleanup.o tsquery_gist.o \
      tsquery_op.o tsquery_rewrite.o tsquery_util.o tsrank.o \
      tsvector.o tsvector_op.o tsvector_parser.o \
--- 25,31 ----
      tid.o timestamp.o varbit.o varchar.o varlena.o version.o xid.o \
      network.o mac.o inet_net_ntop.o inet_net_pton.o \
      ri_triggers.o pg_lzcompress.o pg_locale.o formatting.o \
!     ascii.o quote.o pgstatfuncs.o encode.o dbsize.o genfile.o trigfuncs.o \
      tsginidx.o tsgistidx.o tsquery.o tsquery_cleanup.o tsquery_gist.o \
      tsquery_op.o tsquery_rewrite.o tsquery_util.o tsrank.o \
      tsvector.o tsvector_op.o tsvector_parser.o \
Index: src/backend/utils/adt/trigfuncs.c
===================================================================
RCS file: src/backend/utils/adt/trigfuncs.c
diff -N src/backend/utils/adt/trigfuncs.c
*** /dev/null    1 Jan 1970 00:00:00 -0000
--- src/backend/utils/adt/trigfuncs.c    29 Oct 2008 19:26:53 -0000
***************
*** 0 ****
--- 1,73 ----
+ /*-------------------------------------------------------------------------
+  *
+  * trigfuncs.c
+  *    Builtin functions for useful trigger support.
+  *
+  *
+  * Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
+  * Portions Copyright (c) 1994, Regents of the University of California
+  *
+  * $PostgreSQL:$
+  *
+  *-------------------------------------------------------------------------
+  */
+
+
+
+ #include "postgres.h"
+ #include "commands/trigger.h"
+ #include "access/htup.h"
+
+ /*
+  * suppress_redundant_updates_trigger
+  *
+  * This trigger function will inhibit an update from being done
+  * if the OLD and NEW records are identical.
+  *
+  */
+
+ Datum
+ suppress_redundant_updates_trigger(PG_FUNCTION_ARGS)
+ {
+     TriggerData *trigdata = (TriggerData *) fcinfo->context;
+     HeapTuple   newtuple, oldtuple, rettuple;
+     HeapTupleHeader newheader, oldheader;
+
+     /* make sure it's called as a trigger */
+     if (!CALLED_AS_TRIGGER(fcinfo))
+         elog(ERROR, "suppress_redundant_updates_trigger: must be called as trigger");
+
+     /* and that it's called on update */
+     if (! TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
+         elog(ERROR, "suppress_redundant_updates_trigger: may only be called on update");
+
+     /* and that it's called before update */
+     if (! TRIGGER_FIRED_BEFORE(trigdata->tg_event))
+         elog(ERROR, "suppress_redundant_updates_trigger: may only be called before update");
+
+     /* and that it's called for each row */
+     if (! TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
+         elog(ERROR, "suppress_redundant_updates_trigger: may only be called for each row");
+
+     /* get tuple data, set default return */
+     rettuple  = newtuple = trigdata->tg_newtuple;
+     oldtuple = trigdata->tg_trigtuple;
+
+     newheader = newtuple->t_data;
+     oldheader = oldtuple->t_data;
+
+     if (newtuple->t_len == oldtuple->t_len &&
+         newheader->t_hoff == oldheader->t_hoff &&
+         (HeapTupleHeaderGetNatts(newheader) ==
+          HeapTupleHeaderGetNatts(oldheader) ) &&
+         ((newheader->t_infomask & ~HEAP_XACT_MASK) ==
+          (oldheader->t_infomask & ~HEAP_XACT_MASK) )&&
+         memcmp(((char *)newheader) + offsetof(HeapTupleHeaderData, t_bits),
+                ((char *)oldheader) + offsetof(HeapTupleHeaderData, t_bits),
+                newtuple->t_len - offsetof(HeapTupleHeaderData, t_bits)) == 0)
+     {
+         rettuple = NULL;
+     }
+
+     return PointerGetDatum(rettuple);
+ }
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.520
diff -c -r1.520 pg_proc.h
*** src/include/catalog/pg_proc.h    14 Oct 2008 17:12:33 -0000    1.520
--- src/include/catalog/pg_proc.h    29 Oct 2008 19:26:55 -0000
***************
*** 2290,2295 ****
--- 2290,2298 ----
  DATA(insert OID = 1686 (  pg_get_keywords        PGNSP PGUID 12 10 400 0 f f t t s 0 2249 "" "{25,18,25}" "{o,o,o}"
"{word,catcode,catdesc}"pg_get_keywords _null_ _null_ _null_ )); 
  DESCR("list of SQL keywords");

+ /* utility minimal update trigger */
+ DATA(insert OID = 1619 (  suppress_redundant_updates_trigger    PGNSP PGUID 12 1 0 0 f f t f v 0 2279 "" _null_
_null__null_ suppress_redundant_updates_trigger _null_ _null_ _null_ )); 
+ DESCR("trigger func to  suppress updates when new and old records match");

  /* Generic referential integrity constraint triggers */
  DATA(insert OID = 1644 (  RI_FKey_check_ins        PGNSP PGUID 12 1 0 0 f f t f v 0 2279 "" _null_ _null_ _null_
RI_FKey_check_ins_null_ _null_ _null_ )); 
Index: src/include/utils/builtins.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/utils/builtins.h,v
retrieving revision 1.324
diff -c -r1.324 builtins.h
*** src/include/utils/builtins.h    13 Oct 2008 16:25:20 -0000    1.324
--- src/include/utils/builtins.h    29 Oct 2008 19:26:55 -0000
***************
*** 899,904 ****
--- 899,907 ----
  extern Datum RI_FKey_setdefault_del(PG_FUNCTION_ARGS);
  extern Datum RI_FKey_setdefault_upd(PG_FUNCTION_ARGS);

+ /* trigfuncs.c */
+ extern Datum suppress_redundant_updates_trigger(PG_FUNCTION_ARGS);
+
  /* encoding support functions */
  extern Datum getdatabaseencoding(PG_FUNCTION_ARGS);
  extern Datum database_character_set(PG_FUNCTION_ARGS);
Index: src/test/regress/expected/triggers.out
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/expected/triggers.out,v
retrieving revision 1.24
diff -c -r1.24 triggers.out
*** src/test/regress/expected/triggers.out    1 Feb 2007 19:10:30 -0000    1.24
--- src/test/regress/expected/triggers.out    29 Oct 2008 19:26:56 -0000
***************
*** 537,539 ****
--- 537,564 ----
  NOTICE:  row 2 not changed
  DROP TABLE trigger_test;
  DROP FUNCTION mytrigger();
+ -- minimal update trigger
+ CREATE TABLE min_updates_test (
+     f1    text,
+     f2 int,
+     f3 int);
+ INSERT INTO min_updates_test VALUES ('a',1,2),('b','2',null);
+ CREATE TRIGGER z_min_update
+ BEFORE UPDATE ON min_updates_test
+ FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger();
+ \set QUIET false
+ UPDATE min_updates_test SET f1 = f1;
+ UPDATE 0
+ UPDATE min_updates_test SET f2 = f2 + 1;
+ UPDATE 2
+ UPDATE min_updates_test SET f3 = 2 WHERE f3 is null;
+ UPDATE 1
+ \set QUIET true
+ SELECT * FROM min_updates_test;
+  f1 | f2 | f3
+ ----+----+----
+  a  |  2 |  2
+  b  |  3 |  2
+ (2 rows)
+
+ DROP TABLE min_updates_test;
Index: src/test/regress/sql/triggers.sql
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/sql/triggers.sql,v
retrieving revision 1.13
diff -c -r1.13 triggers.sql
*** src/test/regress/sql/triggers.sql    26 Jun 2006 17:24:41 -0000    1.13
--- src/test/regress/sql/triggers.sql    29 Oct 2008 19:26:56 -0000
***************
*** 415,417 ****
--- 415,446 ----
  DROP TABLE trigger_test;

  DROP FUNCTION mytrigger();
+
+
+ -- minimal update trigger
+
+ CREATE TABLE min_updates_test (
+     f1    text,
+     f2 int,
+     f3 int);
+
+ INSERT INTO min_updates_test VALUES ('a',1,2),('b','2',null);
+
+ CREATE TRIGGER z_min_update
+ BEFORE UPDATE ON min_updates_test
+ FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger();
+
+ \set QUIET false
+
+ UPDATE min_updates_test SET f1 = f1;
+
+ UPDATE min_updates_test SET f2 = f2 + 1;
+
+ UPDATE min_updates_test SET f3 = 2 WHERE f3 is null;
+
+ \set QUIET true
+
+ SELECT * FROM min_updates_test;
+
+ DROP TABLE min_updates_test;
+