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

From johnlumby@hotmail.com
Subject Rules and Command Status - update/insert/delete rule with series of commands in action
Date
Msg-id PH0PR10MB6959209E2B0F36BE765E9159A3F32@PH0PR10MB6959.namprd10.prod.outlook.com
Whole thread Raw
Responses Re: Rules and Command Status - update/insert/delete rule with series of commands in action
Re: Rules and Command Status - update/insert/delete rule with series of commands in action
List 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 the  action 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



pgsql-general by date:

Previous
From: José Mello Júnior
Date:
Subject: Re: Dll libpq.dll 32 bits
Next
From: Juan Rodrigo Alejandro Burgos Mella
Date:
Subject: Re: Dll libpq.dll 32 bits