Thread: manually setting the command tag (was Re: 8.4: suppress_redundant_updates trigger vs. "Upsert" logic)

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@commandprompt.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Pavel Stehule escribió:
> 2009/9/7 Alvaro Herrera <alvherre@commandprompt.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@commandprompt.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@commandprompt.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@commandprompt.com>:
> Tom Lane escribió:
>> Alvaro Herrera <alvherre@commandprompt.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@commandprompt.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@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828