public inbox for [email protected]  

help / color / mirror / Atom feed

From: [email protected] <[email protected]>
To: [email protected]
Subject: Rules and Command Status - update/insert/delete rule with series of commands in action
Date: Thu, 30 May 2024 14:32:46 -0400
Message-ID: <PH0PR10MB6959209E2B0F36BE765E9159A3F32@PH0PR10MB6959.namprd10.prod.outlook.com> (raw)

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

view thread (2+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Rules and Command Status - update/insert/delete rule with series of commands in action
  In-Reply-To: <PH0PR10MB6959209E2B0F36BE765E9159A3F32@PH0PR10MB6959.namprd10.prod.outlook.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox