Rules and Command Status - update/insert/delete rule with series of commands in action

From: "johnlumby(at)hotmail(dot)com" <johnlumby(at)hotmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Rules and Command Status - update/insert/delete rule with series of commands in action
Date: 2024-05-30 18:32:46
Message-ID: PH0PR10MB6959209E2B0F36BE765E9159A3F32@PH0PR10MB6959.namprd10.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Juan Rodrigo Alejandro Burgos Mella 2024-05-30 19:14:40 Re: Dll libpq.dll 32 bits
Previous Message José Mello Júnior 2024-05-30 17:27:56 Re: Dll libpq.dll 32 bits