Skip to content

Commit f0dae70

Browse files
adunstanadunstan
committed
suppress_redundant_updates_trigger function.
1 parent 4ff0468 commit f0dae70

File tree

7 files changed

+194
-5
lines changed

7 files changed

+194
-5
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 52 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.452 2008/11/03 17:51:12 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.453 2008/11/03 20:17:20 adunstan Exp $ -->
22

33
<chapter id="functions">
44
<title>Functions and Operators</title>
@@ -12846,4 +12846,55 @@ SELECT (pg_stat_file('filename')).modification;
1284612846

1284712847
</sect1>
1284812848

12849+
<sect1 id="functions-trigger">
12850+
<title>Trigger Functions</title>
12851+
12852+
<indexterm>
12853+
<primary>suppress_redundant_updates_trigger</primary>
12854+
</indexterm>
12855+
12856+
<para>
12857+
Currently <productname>PostgreSQL</> provides one built in trigger
12858+
function, <function>suppress_redundant_updates_trigger</>,
12859+
which will prevent any update
12860+
that does not actually change the data in the row from taking place, in
12861+
contrast to the normal behaviour which always performs the update
12862+
regardless of whether or not the data has changed. (This normal behaviour
12863+
makes updates run faster, since no checking is required, and is also
12864+
useful in certain cases.)
12865+
</para>
12866+
12867+
<para>
12868+
Ideally, you should normally avoid running updates that don't actually
12869+
change the data in the record. Redundant updates can cost considerable
12870+
unnecessary time, especially if there are lots of indexes to alter,
12871+
and space in dead rows that will eventually have to be vacuumed.
12872+
However, detecting such situations in client code is not
12873+
always easy, or even possible, and writing expressions to detect
12874+
them can be error-prone. An alternative is to use
12875+
<function>suppress_redundant_updates_trigger</>, which will skip
12876+
updates that don't change the data. You should use this with care,
12877+
however. The trigger takes a small but non-trivial time for each record,
12878+
so if most of the records affected by an update are actually changed,
12879+
use of this trigger will actually make the update run slower.
12880+
</para>
12881+
12882+
<para>
12883+
The <function>suppress_redundant_updates_trigger</> function can be
12884+
added to a table like this:
12885+
<programlisting>
12886+
CREATE TRIGGER z_min_update
12887+
BEFORE UPDATE ON tablename
12888+
FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger();
12889+
</programlisting>
12890+
In most cases, you would want to fire this trigger last for each row.
12891+
Bearing in mind that triggers fire in name order, you would then
12892+
choose a trigger name that comes after the name of any other trigger
12893+
you might have on the table.
12894+
</para>
12895+
<para>
12896+
For more information about creating triggers, see
12897+
<xref linkend="SQL-CREATETRIGGER">.
12898+
</para>
12899+
</sect1>
1284912900
</chapter>

‎src/backend/utils/adt/Makefile

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,7 @@
11
#
22
# Makefile for utils/adt
33
#
4-
# $PostgreSQL: pgsql/src/backend/utils/adt/Makefile,v 1.69 2008/02/19 10:30:08 petere Exp $
4+
# $PostgreSQL: pgsql/src/backend/utils/adt/Makefile,v 1.70 2008/11/03 20:17:20 adunstan Exp $
55
#
66

77
subdir = src/backend/utils/adt
@@ -25,7 +25,7 @@ OBJS = acl.o arrayfuncs.o array_userfuncs.o arrayutils.o bool.o \
2525
tid.o timestamp.o varbit.o varchar.o varlena.o version.o xid.o \
2626
network.o mac.o inet_net_ntop.o inet_net_pton.o \
2727
ri_triggers.o pg_lzcompress.o pg_locale.o formatting.o \
28-
ascii.o quote.o pgstatfuncs.o encode.o dbsize.o genfile.o \
28+
ascii.o quote.o pgstatfuncs.o encode.o dbsize.o genfile.o trigfuncs.o \
2929
tsginidx.o tsgistidx.o tsquery.o tsquery_cleanup.o tsquery_gist.o \
3030
tsquery_op.o tsquery_rewrite.o tsquery_util.o tsrank.o \
3131
tsvector.o tsvector_op.o tsvector_parser.o \

‎src/backend/utils/adt/trigfuncs.c

Lines changed: 77 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,77 @@
1+
/*-------------------------------------------------------------------------
2+
*
3+
* trigfuncs.c
4+
* Builtin functions for useful trigger support.
5+
*
6+
*
7+
* Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
8+
* Portions Copyright (c) 1994, Regents of the University of California
9+
*
10+
* $PostgreSQL: pgsql/src/backend/utils/adt/trigfuncs.c,v 1.1 2008/11/03 20:17:20 adunstan Exp $
11+
*
12+
*-------------------------------------------------------------------------
13+
*/
14+
15+
16+
17+
#include "postgres.h"
18+
#include "commands/trigger.h"
19+
#include "access/htup.h"
20+
21+
/*
22+
* suppress_redundant_updates_trigger
23+
*
24+
* This trigger function will inhibit an update from being done
25+
* if the OLD and NEW records are identical.
26+
*
27+
*/
28+
29+
Datum
30+
suppress_redundant_updates_trigger(PG_FUNCTION_ARGS)
31+
{
32+
TriggerData *trigdata = (TriggerData *) fcinfo->context;
33+
HeapTuple newtuple, oldtuple, rettuple;
34+
HeapTupleHeader newheader, oldheader;
35+
36+
/* make sure it's called as a trigger */
37+
if (!CALLED_AS_TRIGGER(fcinfo))
38+
elog(ERROR, (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
39+
errmsg("suppress_redundant_updates_trigger: must be called as trigger")));
40+
41+
/* and that it's called on update */
42+
if (! TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
43+
ereport(ERROR, (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
44+
errmsg( "suppress_redundant_updates_trigger: may only be called on update")));
45+
46+
/* and that it's called before update */
47+
if (! TRIGGER_FIRED_BEFORE(trigdata->tg_event))
48+
ereport(ERROR, (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
49+
errmsg( "suppress_redundant_updates_trigger: may only be called before update")));
50+
51+
/* and that it's called for each row */
52+
if (! TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
53+
ereport(ERROR, (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
54+
errmsg( "suppress_redundant_updates_trigger: may only be called for each row")));
55+
56+
/* get tuple data, set default return */
57+
rettuple = newtuple = trigdata->tg_newtuple;
58+
oldtuple = trigdata->tg_trigtuple;
59+
60+
newheader = newtuple->t_data;
61+
oldheader = oldtuple->t_data;
62+
63+
if (newtuple->t_len == oldtuple->t_len &&
64+
newheader->t_hoff == oldheader->t_hoff &&
65+
(HeapTupleHeaderGetNatts(newheader) ==
66+
HeapTupleHeaderGetNatts(oldheader) ) &&
67+
((newheader->t_infomask & ~HEAP_XACT_MASK) ==
68+
(oldheader->t_infomask & ~HEAP_XACT_MASK) )&&
69+
memcmp(((char *)newheader) + offsetof(HeapTupleHeaderData, t_bits),
70+
((char *)oldheader) + offsetof(HeapTupleHeaderData, t_bits),
71+
newtuple->t_len - offsetof(HeapTupleHeaderData, t_bits)) == 0)
72+
{
73+
rettuple = NULL;
74+
}
75+
76+
return PointerGetDatum(rettuple);
77+
}

‎src/include/catalog/pg_proc.h

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.521 2008/11/03 17:51:13 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.522 2008/11/03 20:17:20 adunstan Exp $
1111
*
1212
* NOTES
1313
* The script catalog/genbki.sh reads this file and generates .bki
@@ -1580,6 +1580,9 @@ DESCR("convert int8 to oid");
15801580
DATA(insert OID = 1288 ( int8 PGNSP PGUID 12 1 0 0 f f t f i 1 20 "26" _null_ _null_ _null_ oidtoi8 _null_ _null_ _null_ ));
15811581
DESCR("convert oid to int8");
15821582

1583+
DATA(insert OID = 1291 ( 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_ ));
1584+
DESCR("trigger to suppress updates when new and old records match");
1585+
15831586
DATA(insert OID = 1292 ( tideq PGNSP PGUID 12 1 0 0 f f t f i 2 16 "27 27" _null_ _null_ _null_ tideq _null_ _null_ _null_ ));
15841587
DESCR("equal");
15851588
DATA(insert OID = 1293 ( currtid PGNSP PGUID 12 1 0 0 f f t f v 2 27 "26 27" _null_ _null_ _null_ currtid_byreloid _null_ _null_ _null_ ));
@@ -2289,6 +2292,7 @@ DESCR("result type of a function");
22892292

22902293
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_ ));
22912294
DESCR("list of SQL keywords");
2295+
22922296
DATA(insert OID = 1619 ( pg_typeof PGNSP PGUID 12 1 0 0 f f f f i 1 2206 "2276" _null_ _null_ _null_ pg_typeof _null_ _null_ _null_ ));
22932297
DESCR("returns the type of the argument");
22942298

‎src/include/utils/builtins.h

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.325 2008/11/03 17:51:13 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.326 2008/11/03 20:17:20 adunstan Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -900,6 +900,9 @@ extern Datum RI_FKey_setnull_upd(PG_FUNCTION_ARGS);
900900
extern Datum RI_FKey_setdefault_del(PG_FUNCTION_ARGS);
901901
extern Datum RI_FKey_setdefault_upd(PG_FUNCTION_ARGS);
902902

903+
/* trigfuncs.c */
904+
extern Datum suppress_redundant_updates_trigger(PG_FUNCTION_ARGS);
905+
903906
/* encoding support functions */
904907
extern Datum getdatabaseencoding(PG_FUNCTION_ARGS);
905908
extern Datum database_character_set(PG_FUNCTION_ARGS);

‎src/test/regress/expected/triggers.out

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -537,3 +537,28 @@ NOTICE: row 1 not changed
537537
NOTICE: row 2 not changed
538538
DROP TABLE trigger_test;
539539
DROP FUNCTION mytrigger();
540+
-- minimal update trigger
541+
CREATE TABLE min_updates_test (
542+
f1 text,
543+
f2 int,
544+
f3 int);
545+
INSERT INTO min_updates_test VALUES ('a',1,2),('b','2',null);
546+
CREATE TRIGGER z_min_update
547+
BEFORE UPDATE ON min_updates_test
548+
FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger();
549+
\set QUIET false
550+
UPDATE min_updates_test SET f1 = f1;
551+
UPDATE 0
552+
UPDATE min_updates_test SET f2 = f2 + 1;
553+
UPDATE 2
554+
UPDATE min_updates_test SET f3 = 2 WHERE f3 is null;
555+
UPDATE 1
556+
\set QUIET true
557+
SELECT * FROM min_updates_test;
558+
f1 | f2 | f3
559+
----+----+----
560+
a | 2 | 2
561+
b | 3 | 2
562+
(2 rows)
563+
564+
DROP TABLE min_updates_test;

‎src/test/regress/sql/triggers.sql

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -415,3 +415,32 @@ UPDATE trigger_test SET f3 = NULL;
415415
DROP TABLE trigger_test;
416416

417417
DROP FUNCTION mytrigger();
418+
419+
420+
-- minimal update trigger
421+
422+
CREATE TABLE min_updates_test (
423+
f1 text,
424+
f2 int,
425+
f3 int);
426+
427+
INSERT INTO min_updates_test VALUES ('a',1,2),('b','2',null);
428+
429+
CREATE TRIGGER z_min_update
430+
BEFORE UPDATE ON min_updates_test
431+
FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger();
432+
433+
\set QUIET false
434+
435+
UPDATE min_updates_test SET f1 = f1;
436+
437+
UPDATE min_updates_test SET f2 = f2 + 1;
438+
439+
UPDATE min_updates_test SET f3 = 2 WHERE f3 is null;
440+
441+
\set QUIET true
442+
443+
SELECT * FROM min_updates_test;
444+
445+
DROP TABLE min_updates_test;
446+

0 commit comments

Comments
 (0)