Kenneth Marshall wrote:
On Wed, Oct 22, 2008 at 06:05:26PM -0400, Tom Lane wrote:
Simon Riggs <[EMAIL PROTECTED]> writes:
On Wed, Oct 22, 2008 at 3:24 PM, Tom Lane <[EMAIL PROTECTED]> 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;
+
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
