Hello, It might be helpful to point out that the "suppress_redundant_updates" trigger will cause trouble with the normal approach to doing an UPSERT, for example that specified by the "merge_db" function in example 38-2 here: http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE
If you happen to submit a redundant update against a table with the suppress_redundant_updates trigger, using the merge_db function, you'll end up in an infinite loop. It'll similarly break any code where a result of "UPDATE 0" is assumed to indicate that the record does not exist. Suggested changes: 1. Add to http://www.postgresql.org/docs/8.4/static/functions-trigger.htmlthe following copy, or something nicer :) Note that the suppress_redundant_updates trigger invalidates any logic that assumes that the number of rows affected by an UPDATE indicates the existence or non-existence of the associated rows. Specifically, in the case of the merge_db<http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE>function, any redundant updates performed by this function will cause an infinite loop. 2. Add to http://www.postgresql.org/docs/8.4/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLEthe following copy Warning: Using this function or similar logic is incompatible with the suppress_redundant_updates trigger<http://www.postgresql.org/docs/8.4/interactive/functions-trigger.html>, since it is assumed that if zero rows are updated, the row does not exist. Alternatively, the example merge_db function could be reworked to be compatible with the trigger by attempting the insert first, for example: CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS $$ BEGIN BEGIN -- first try to insert the row INSERT INTO db(a,b) VALUES (key, data); EXCEPTION WHEN unique_violation THEN -- key already existed, try updating the row UPDATE db SET b = data WHERE a = key; END; END; $$ LANGUAGE plpgsql; The above is not as robust as the original, since it doesn't retry, and it's a bit of a foot-gun if you don't actually have a unique constraint... Maybe someone else can come up with a version that's strictly better than the old one, and still plays nice with suppress_redundant_updates. Thanks! Mark
