Re: minimal update
Re: minimal update
От:
David Fetter <david@fetter.org>
Дата:
On Wed, Oct 29, 2008 at 03:48:09PM -0400, Andrew Dunstan wrote: > >>> + /* make sure it's called as a trigger */ >>> + if (!CALLED_AS_TRIGGER(fcinfo)) >>> + elog(ERROR, "suppress_redundant_updates_trigger: must be called as trigger"); >> >> Shouldn't these all be ereport()? > > Good point. > > I'll fix them. > > Maybe we should fix our C sample trigger, from which this was taken. Yes :) Does the attached have the right error code? Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
Re: minimal update
От:
Andrew Dunstan <andrew@dunslane.net>
Дата:
Andrew Dunstan wrote:
>
>
> Tom Lane wrote:
>> Magnus Hagander 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 ----
+
+ Trigger Functions
+
+
+ Currently PostgreSQL</> provides one built in trigger
+ 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.
+
+
+
+ The min_update_trigger</> function can be added to a table
+ like this:
+
+ CREATE TRIGGER _min_update
+ BEFORE UPDATE ON tablename
+ FOR EACH ROW EXECUTE PROCEDURE min_update_trigger();
+
+
+
+ For mare information about creating triggers, see
+ .
+
+
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;
+
Re: minimal update
От:
Andrew Dunstan <andrew@dunslane.net>
Дата:
Kenneth Marshall wrote:
> On Wed, Oct 22, 2008 at 06:05:26PM -0400, Tom Lane wrote:
>
>> Simon Riggs writes:
>>
>>>> On Wed, Oct 22, 2008 at 3:24 PM, Tom Lane 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 ----
+
+ Trigger Functions
+
+
+ Currently PostgreSQL</> provides one built in trigger
+ 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.
+
+
+
+ The suppress_redundant_updates_trigger</> function can be
+ added to a table like this:
+
+ CREATE TRIGGER z_min_update
+ BEFORE UPDATE ON tablename
+ FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger();
+
+ 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.
+
+
+ For more information about creating triggers, see
+ .
+
+
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;
+
Re: minimal update
От:
"Gurjeet Singh" <singh.gurjeet@gmail.com>
Дата:
On Fri, Mar 7, 2008 at 9:40 PM, Bruce Momjian <bruce@momjian.us> wrote:
I am starting to implement this. Do we want to have this trigger function in the server, or in an external module?
Best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad *
18° 32' 57.25"N, 73° 56' 25.42"E - Pune
37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco
http://gurjeet.frihost.net
Mail sent from my BlackLaptop device
I assume don't want a TODO for this? (Suppress UPDATE no changed
columns)
I am starting to implement this. Do we want to have this trigger function in the server, or in an external module?
Best regards,
-----------------------------------------------------------------------------
Andrew Dunstan wrote:
>
>
> Tom Lane wrote:
> > Michael Glaesemann <grzm@seespotcode.net> writes:
> >
> >> What would be the disadvantages of always doing this, i.e., just
> >> making this part of the normal update path in the backend?
> >>
> >
> > (1) cycles wasted to no purpose in the vast majority of cases.
> >
> > (2) visibly inconsistent behavior for apps that pay attention
> > to ctid/xmin/etc.
> >
> > (3) visibly inconsistent behavior for apps that have AFTER triggers.
> >
> > There's enough other overhead in issuing an update (network,
> > parsing/planning/etc) that a sanely coded application should try
> > to avoid issuing no-op updates anyway. The proposed trigger is
> > just a band-aid IMHO.
> >
> > I think having it as an optional trigger is a reasonable compromise.
> >
> >
> >
>
> Right. I never proposed making this the default behaviour, for all these
> good reasons.
>
> The point about making the app try to avoid no-op updates is that this
> can impose some quite considerable code complexity on the app,
> especially where the number of updated fields is large. It's fragile and
> error-prone. A simple switch that can turn a trigger on or off will be
> nicer. Syntax support for that might be even nicer, but there appears to
> be some resistance to that, so I can easily settle for the trigger.
>
> cheers
>
> andrew
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad *
18° 32' 57.25"N, 73° 56' 25.42"E - Pune
37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco
http://gurjeet.frihost.net
Mail sent from my BlackLaptop device
Re: minimal update
От:
"Gurjeet Singh" <singh.gurjeet@gmail.com>
Дата:
On Tue, Mar 18, 2008 at 7:46 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
In fact, I just finished writing the C code and including it in the catalog (Just tested that it's visible in the catalog). I will test it to see if it does actually do what we want it to.
I have incorporated all the suggestions above. Would love to see your code in the meantime.
Here's the C code:
Datum
trig_ignore_duplicate_updates( PG_FUNCTION_ARGS )
{
TriggerData *trigData;
HeapTuple oldTuple;
HeapTuple newTuple;
if (!CALLED_AS_TRIGGER(fcinfo))
elog(ERROR, "trig_ignore_duplicate_updates: not called by trigger manager.");
if( !TRIGGER_FIRED_BY_UPDATE(trigData->tg_event)
&& !TRIGGER_FIRED_BEFORE(trigData->tg_event)
&& !TRIGGER_FIRED_FOR_ROW(trigData->tg_event) )
{
elog(ERROR, "trig_ignore_duplicate_updates: Can only be executed for UPDATE, BEFORE and FOR EACH ROW.");
}
trigData = (TriggerData *) fcinfo->context;
oldTuple = trigData->tg_trigtuple;
newTuple = trigData->tg_newtuple;
if (newTuple->t_len == oldTuple->t_len
&& newTuple->t_data->t_hoff == oldTuple->t_data->t_hoff
&& HeapTupleHeaderGetNatts(newTuple->t_data) == HeapTupleHeaderGetNatts(oldTuple->t_data)
&& (newTuple->t_data->t_infomask & ~HEAP_XACT_MASK)
== (oldTuple->t_data->t_infomask & ~HEAP_XACT_MASK)
&& memcmp( (char*)(newTuple->t_data) + offsetof(HeapTupleHeaderData, t_bits),
(char*)(oldTuple->t_data) + offsetof(HeapTupleHeaderData, t_bits),
newTuple->t_len - offsetof(HeapTupleHeaderData, t_bits) ) == 0 )
{
/* return without crating a new tuple */
return PointerGetDatum( NULL );
}
return PointerGetDatum( trigData->tg_newtuple );
}
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad *
18° 32' 57.25"N, 73° 56' 25.42"E - Pune
37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco
http://gurjeet.frihost.net
Mail sent from my BlackLaptop device
> <mailto:bruce@momjian.us>> wrote:I have the trigger part of this done, in fact. What remains to be done
>
>
> I assume don't want a TODO for this? (Suppress UPDATE no changed
> columns)
>
>
> I am starting to implement this. Do we want to have this trigger
> function in the server, or in an external module?
>
>
is to add it to the catalog and document it. The intention is to make it
a builtin as it will be generally useful. If you want to work on the
remaining parts then I will happily ship you the C code for the trigger.
In fact, I just finished writing the C code and including it in the catalog (Just tested that it's visible in the catalog). I will test it to see if it does actually do what we want it to.
I have incorporated all the suggestions above. Would love to see your code in the meantime.
Here's the C code:
Datum
trig_ignore_duplicate_updates( PG_FUNCTION_ARGS )
{
TriggerData *trigData;
HeapTuple oldTuple;
HeapTuple newTuple;
if (!CALLED_AS_TRIGGER(fcinfo))
elog(ERROR, "trig_ignore_duplicate_updates: not called by trigger manager.");
if( !TRIGGER_FIRED_BY_UPDATE(trigData->tg_event)
&& !TRIGGER_FIRED_BEFORE(trigData->tg_event)
&& !TRIGGER_FIRED_FOR_ROW(trigData->tg_event) )
{
elog(ERROR, "trig_ignore_duplicate_updates: Can only be executed for UPDATE, BEFORE and FOR EACH ROW.");
}
trigData = (TriggerData *) fcinfo->context;
oldTuple = trigData->tg_trigtuple;
newTuple = trigData->tg_newtuple;
if (newTuple->t_len == oldTuple->t_len
&& newTuple->t_data->t_hoff == oldTuple->t_data->t_hoff
&& HeapTupleHeaderGetNatts(newTuple->t_data) == HeapTupleHeaderGetNatts(oldTuple->t_data)
&& (newTuple->t_data->t_infomask & ~HEAP_XACT_MASK)
== (oldTuple->t_data->t_infomask & ~HEAP_XACT_MASK)
&& memcmp( (char*)(newTuple->t_data) + offsetof(HeapTupleHeaderData, t_bits),
(char*)(oldTuple->t_data) + offsetof(HeapTupleHeaderData, t_bits),
newTuple->t_len - offsetof(HeapTupleHeaderData, t_bits) ) == 0 )
{
/* return without crating a new tuple */
return PointerGetDatum( NULL );
}
return PointerGetDatum( trigData->tg_newtuple );
}
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad *
18° 32' 57.25"N, 73° 56' 25.42"E - Pune
37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco
http://gurjeet.frihost.net
Mail sent from my BlackLaptop device
