Skip site navigation (1) Skip section navigation (2)

8.4: suppress_redundant_updates trigger vs. "Upsert" logic

From: Mark Reid <mark(at)markreid(dot)org>
To: pgsql-docs(at)postgresql(dot)org
Subject: 8.4: suppress_redundant_updates trigger vs. "Upsert" logic
Date: 2009-09-04 15:15:36
Message-ID: 293cb3e40909040815s274eb699x1977392cc06707f5@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-docspgsql-hackers
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

Responses

pgsql-docs by date

Next:From: Alvaro HerreraDate: 2009-09-07 14:05:13
Subject: no refentry in acronyms.sgml?
Previous:From: Alvaro HerreraDate: 2009-09-03 22:04:44
Subject: Re: [GENERAL] What happens when syslog gets blocked?

pgsql-hackers by date

Next:From: Josh BerkusDate: 2009-09-04 18:37:24
Subject: Re: Eliminating VACUUM FULL WAS: remove flatfiles.c
Previous:From: Simon RiggsDate: 2009-09-04 14:17:47
Subject: Re: Hot Standby, max_connections andmax_prepared_transactions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group