8.4: suppress_redundant_updates trigger vs. "Upsert" logic

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

Mark Reid wrote:

> It'll similarly break any code where a result of "UPDATE 0" is assumed to
> indicate that the record does not exist.

I wonder if this could be helped if the trigger had a way of overriding
the emitted command tag.

There are countless reports of trouble because somebody has an INSTEAD
rule in the table, and the tag emits something that's not quite
acceptable for some outer software layer. The problem is that there's
no way at all to make the command tag behave!

For example, we could offer a new SPI function, say SPI_settag(), which
sets the command tag string. PL/pgSQL could expose this via a new
command, for example COMMANDTAG.

So if there's a function that is used in a INSTEAD rule for (say) an
UPDATE, this function would finish with COMMANDTAG 'UPDATE num' and make
the external framework happy.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

2009/9/7 Alvaro Herrera <alvherre(at)commandprompt(dot)com>:
> Mark Reid wrote:
>
>> It'll similarly break any code where a result of "UPDATE 0" is assumed to
>> indicate that the record does not exist.
>
> I wonder if this could be helped if the trigger had a way of overriding
> the emitted command tag.
>
> There are countless reports of trouble because somebody has an INSTEAD
> rule in the table, and the tag emits something that's not quite
> acceptable for some outer software layer.  The problem is that there's
> no way at all to make the command tag behave!
>
> For example, we could offer a new SPI function, say SPI_settag(), which
> sets the command tag string.  PL/pgSQL could expose this via a new
> command, for example COMMANDTAG.

Isn't better to solve the the correct diagnostics for INSTEAD rules or triggers?

Pavel

>
> So if there's a function that is used in a INSTEAD rule for (say) an
> UPDATE, this function would finish with COMMANDTAG 'UPDATE num' and make
> the external framework happy.
>
> --
> Alvaro Herrera                                http://www.CommandPrompt.com/
> The PostgreSQL Company - Command Prompt, Inc.
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Pavel Stehule escribió:
> 2009/9/7 Alvaro Herrera <alvherre(at)commandprompt(dot)com>:

> > There are countless reports of trouble because somebody has an INSTEAD
> > rule in the table, and the tag emits something that's not quite
> > acceptable for some outer software layer.  The problem is that there's
> > no way at all to make the command tag behave!
> >
> > For example, we could offer a new SPI function, say SPI_settag(), which
> > sets the command tag string.  PL/pgSQL could expose this via a new
> > command, for example COMMANDTAG.
>
> Isn't better to solve the the correct diagnostics for INSTEAD rules or triggers?

As far as I can tell it's not possible to do better without letting the
user put their hands on the tag. The problem is figuring out what
command should determine the tag.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Pavel Stehule escribi:
>> Isn't better to solve the the correct diagnostics for INSTEAD rules or triggers?

> As far as I can tell it's not possible to do better without letting the
> user put their hands on the tag.

And how is the user going to do better? For example, what if there are
two triggers trying to set the result, perhaps because two different
commands have been generated by DO ALSO rules?

I don't think that "put it on the user's shoulders" is a good solution.

regards, tom lane

Tom Lane escribió:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > Pavel Stehule escribi:
> >> Isn't better to solve the the correct diagnostics for INSTEAD rules or triggers?
>
> > As far as I can tell it's not possible to do better without letting the
> > user put their hands on the tag.
>
> And how is the user going to do better? For example, what if there are
> two triggers trying to set the result, perhaps because two different
> commands have been generated by DO ALSO rules?

We would allow the user to set a policy. This provides the mechanism
for doing it. Right now there is no mechanism at all and we fail to do
anything.

> I don't think that "put it on the user's shoulders" is a good solution.

Is there a better idea?

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

2009/9/8 Alvaro Herrera <alvherre(at)commandprompt(dot)com>:
> Tom Lane escribió:
>> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
>> > Pavel Stehule escribió:
>> >> Isn't better to solve the the correct diagnostics for INSTEAD rules or triggers?
>>
>> > As far as I can tell it's not possible to do better without letting the
>> > user put their hands on the tag.
>>
>> And how is the user going to do better?  For example, what if there are
>> two triggers trying to set the result, perhaps because two different
>> commands have been generated by DO ALSO rules?
>
> We would allow the user to set a policy.  This provides the mechanism
> for doing it.  Right now there is no mechanism at all and we fail to do
> anything.
>
>> I don't think that "put it on the user's shoulders" is a good solution.
>
> Is there a better idea?

we should to count rows on storage level - and then (via GUC) we
should to return global count or table count.

???

>
> --
> Alvaro Herrera                                http://www.CommandPrompt.com/
> The PostgreSQL Company - Command Prompt, Inc.
>

On Sep 7, 2009, at 6:10 PM, Tom Lane wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
>> Pavel Stehule escribió:
>>> Isn't better to solve the the correct diagnostics for INSTEAD
>>> rules or triggers?
>
>> As far as I can tell it's not possible to do better without
>> letting the
>> user put their hands on the tag.
>
> And how is the user going to do better? For example, what if there
> are
> two triggers trying to set the result, perhaps because two different
> commands have been generated by DO ALSO rules?

It depends on what the user is trying to accomplish. If the ALSO rule
is just doing auditing type stuff, then they probably don't want that
included in the result.

I don't see this is being different from having to get the rules
correct in the first place; all we're doing here is adding the
ability to return a meaningful result from the rules back to the client.

BTW, the real-world case we have are updatable views on top of a
union. In this case we'd want the result to reflect the updates that
occurred in all the tables, not just in the last table in the rule.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828