Re: minimal update

Andrew Dunstan wrote:
>
>
> Tom Lane wrote:
>> Magnus Hagander <magnus@hagander.net> writes:
>>
>>> In that case, why not put the trigger in core so people can use it
>>> easily?
>>>
>>
>> One advantage of making it a contrib module is that discussing how/when
>> to use it would fit more easily into the structure of the
>> documentation.  There is no place in our docs that a "standard trigger"
>> would fit without seeming like a wart; but a contrib module can document
>> itself pretty much however it wants.
>>
>
> I was thinking a new section on 'trigger functions' of the functions
> and operators chapter, linked from the 'create trigger' page. That
> doesn't seem like too much of a wart.
>
>

There seems to be a preponderance of opinion for doing this as a
builtin. Here is a patch that does it that way, along with docs and
regression test.

cheers

andrew

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    22 Oct 2008 18:35:51 -0000
***************
*** 12817,12820 ****
--- 12817,12845 ----

    </sect1>

+   <sect1 id="functions-trigger">
+    <title>Trigger Functions</title>
+
+    <para>
+       Currently <productname>PostgreSQL</> provides one built in trigger
+       function, <function>min_update_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>min_update_trigger</> function can be added to a table
+       like this:
+ <programlisting>
+ CREATE TRIGGER _min_update
+ BEFORE UPDATE ON tablename
+ FOR EACH ROW EXECUTE PROCEDURE min_update_trigger();
+ </programlisting>
+     </para>
+     <para>
+        For mare 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    22 Oct 2008 18:35:51 -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    22 Oct 2008 18:35:51 -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"
+
+ /*
+  * min_update_trigger
+  *
+  * This trigger function will inhibit an update from being done
+  * if the OLD and NEW records are identical.
+  *
+  */
+
+ Datum
+ min_update_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, "min_update_trigger: not called by trigger manager");
+
+     /* and that it's called on update */
+     if (! TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
+         elog(ERROR, "min_update_trigger: not called on update");
+
+     /* and that it's called before update */
+     if (! TRIGGER_FIRED_BEFORE(trigdata->tg_event))
+         elog(ERROR, "min_update_trigger: not called before update");
+
+     /* and that it's called for each row */
+     if (! TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
+         elog(ERROR, "min_update_trigger: not 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    22 Oct 2008 18:35:52 -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 (  min_update_trigger    PGNSP PGUID 12 1 0 0 f f t f v 0 2279 "" _null_ _null_ _null_
min_update_trigger_null_ _null_ _null_ )); 
+ DESCR("minimal update trigger function");

  /* 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    22 Oct 2008 18:35:52 -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 min_update_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    22 Oct 2008 18:35:52 -0000
***************
*** 537,539 ****
--- 537,564 ----
  NOTICE:  row 2 not changed
  DROP TABLE trigger_test;
  DROP FUNCTION mytrigger();
+ -- minimal update trigger
+ CREATE TABLE min_update_test (
+     f1    text,
+     f2 int,
+     f3 int);
+ INSERT INTO min_update_test VALUES ('a',1,2),('b','2',null);
+ CREATE TRIGGER _min_update
+ BEFORE UPDATE ON min_update_test
+ FOR EACH ROW EXECUTE PROCEDURE min_update_trigger();
+ \set QUIET false
+ UPDATE min_update_test SET f1 = f1;
+ UPDATE 0
+ UPDATE min_update_test SET f2 = f2 + 1;
+ UPDATE 2
+ UPDATE min_update_test SET f3 = 2 WHERE f3 is null;
+ UPDATE 1
+ \set QUIET true
+ SELECT * FROM min_update_test;
+  f1 | f2 | f3
+ ----+----+----
+  a  |  2 |  2
+  b  |  3 |  2
+ (2 rows)
+
+ DROP TABLE min_update_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    22 Oct 2008 18:35:52 -0000
***************
*** 415,417 ****
--- 415,446 ----
  DROP TABLE trigger_test;

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