public inbox for [email protected]
help / color / mirror / Atom feedRules and Command Status - update/insert/delete rule with series of commands in action
2+ messages / 2 participants
[nested] [flat]
* Rules and Command Status - update/insert/delete rule with series of commands in action
@ 2024-05-30 18:32 [email protected] <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: [email protected] @ 2024-05-30 18:32 UTC (permalink / raw)
To: [email protected]
The RULE infrastructure permits the programmer to specify a series of
commands in the DO action
from the syntax diagram in the manual :
CREATE [ OR REPLACE ] RULE name AS ON event
TO table [ WHERE condition ]
DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }
The manual described the series of commands as the "rule action" ,
implying (to me) that there is a sense in which the entire series
comprising the action is one operation.
I am specifically interested in the case of update and an example of an
unconditional rule such as
CREATE or REPLACE RULE multi-action AS ON UPDATE TO my_view
DO INSTEAD (
UPDATE my_table_a
SET a_column = value
WHERE OLD.keycolumn = keyvalue;
UPDATE my_table_b
SET b_column = value
WHERE OLD.keycolumn = keyvalue;
);
where my intention is that one and only one of the action commands
should update any row.
This all works except for one thing : the final status, including the
(for me) all-important number of rows updated.
It turns out that in this example, if the UPDATE my_table_b updates
one (or more) rows, the status shows that number, but if the UPDATE
my_table_b updates no rows, the status shows 0 rows updated, even
if one (or more) rows of my_table_a were successfully updated by the
first command. This is not what I want.
The chapter entitled "Rules and Command Status " (approximately chap
number 41.6 depending on version) says
"If there is any unconditional|INSTEAD|rule for the query, then the
original query will not be executed at all. In this case, the server
will return the command status for the last *query* that was inserted by
an|INSTEAD|rule (conditional or unconditional) and is of the same
command type (|INSERT|,|UPDATE|, or|DELETE|) as the original query."
(my bold of the word query).
But what is a query in this context? In my example, is the
last *query* the
. *action* of the last unconditional RULE which executed (only one in
my example but there could be other applicable rules for update of my_view)
OR
. last *command* of the series of commands comprising theaction of the
last unconditional RULE which executed
?
Well, I assume what postgresql actually does is the latter, but surely
there is a case for it to be the former, where the rows_updated of the
action would be the sum of all rows updated by all commands in that
action's series. . In my example, postgresql is telling the
application that no rows were updated when actually one (or more) row
was updated, and the sum of all rows updated is one.
Any thoughts? Any rationales one way or the other? Any interest
in perhaps providing a choice via a configuration parameter?
Cheers, John Lumby
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Rules and Command Status - update/insert/delete rule with series of commands in action
@ 2024-05-30 20:56 David G. Johnston <[email protected]>
parent: [email protected] <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: David G. Johnston @ 2024-05-30 20:56 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]
On Thu, May 30, 2024, 12:32 [email protected] <[email protected]>
wrote:
> Any thoughts?
>
Very little interest exists in working on user-specified rules. They are
practically deprecated.
>
> Any interest in perhaps providing a choice via a configuration
> parameter?
>
Almost certainly not. Configuration should not affect query behavior. If
anything is done it would have to be new syntax. Though I haven't explored
the use case presented. See the first point. I sure don't have the
requisite familiarity here.
David J.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-05-30 20:56 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-05-30 18:32 Rules and Command Status - update/insert/delete rule with series of commands in action [email protected] <[email protected]>
2024-05-30 20:56 ` David G. Johnston <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox