Thread

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

   johnlumby@hotmail.com <johnlumby@hotmail.com> — 2024-05-30T18:32:46Z

   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
   
   
   

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

   David G. Johnston <david.g.johnston@gmail.com> — 2024-05-30T20:56:06Z

   On Thu, May 30, 2024, 12:32 johnlumby@hotmail.com <johnlumby@hotmail.com>
   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.
   

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

   Adrian Klaver <adrian.klaver@aklaver.com> — 2024-05-30T21:19:52Z

   On 5/30/24 11:32, johnlumby@hotmail.com wrote:
   
   > Any thoughts?      Any rationales one way or the other?    Any interest 
   > in perhaps providing a choice via a configuration parameter?
   
   1) As to using rules, stop now before you invest the time to find out is 
   a road you don't want to go down.
   
   2) Use INSTEAD OF triggers:
   
   https://www.postgresql.org/docs/current/sql-createtrigger.html
   
   
   
   > 
   > 
   > Cheers, John Lumby
   > 
   > 
   > 
   
   -- 
   Adrian Klaver
   adrian.klaver@aklaver.com
   
   
   
   
   

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

   johnlumby@hotmail.com <johnlumby@hotmail.com> — 2024-05-30T22:34:23Z

   On 5/30/24 4:56 PM, David G. Johnston wrote:
   >
   > Very little interest exists in working on user-specified rules.  They 
   > are practically deprecated.
   
   
   Ah  -   pity  -   see my last comment to Adrian's
   
   
   >
   >     Any interest in perhaps providing a choice via a configuration
   >     parameter?
   >
   >
   >  If anything is done it would have to be new syntax.
   >
   >
   A much bigger task surely.
   
   
   On 5/30/24 5:19 PM, Adrian Klaver wrote:
   >
   > 2) Use INSTEAD OF triggers:
   >
   >
   
   Unfortunately the same functionality as in my example with the RULE is 
   not supported for triggers on views :   from the manual
   
   |INSTEAD OF| triggers may only be defined on views, *and only at row level*;
   
   
   A RULE is essentially a statement-level operation which is what I need 
   for this particular case.      A row-level trigger would not work 
   because it cannot "see" the query causing it to be fired, and also ,  
   (most importantly) is not fired at all if no rows match the original 
   query,     whereas a RULE is always in effect regardless of which rows 
   are involved. before.      I should add that the RULE I showed in my 
   example is not the only RULE being used on this view  -    there are 
   other conditional RULEs,   and the combined effect is of being able to 
   change the effect of the original statement into a set of new 
   statements,   one of which does what is needed.
   
   
   And if you are now inclined to say "well,    maybe the application 
   itself is poorly written and should be changed"  -   I would have to 
   agree,    but that is not mine to change.
   
   
   But I suppose that my next question,   given what you both say about the 
   RULE system being a dead-end,  is whether there is any likelihood of 
   supporting an INSTEAD OF trigger on a view at statement level?   Maybe 
   that stands more chance of going somewhere?
   
   
   
   Cheers,     John Lumby
   
   

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

   Tom Lane <tgl@sss.pgh.pa.us> — 2024-05-31T01:34:07Z

   "johnlumby@hotmail.com" <johnlumby@hotmail.com> writes:
   > But I suppose that my next question,   given what you both say about the 
   > RULE system being a dead-end,  is whether there is any likelihood of 
   > supporting an INSTEAD OF trigger on a view at statement level?   Maybe 
   > that stands more chance of going somewhere?
   
   Perhaps, but I don't know of anyone working on it.
   
   IIRC, the row-level-only restriction dates from before we had
   implemented transition tables, so that at the time there was
   nothing very meaningful that a statement-level trigger could do.
   Maybe that problem has gone away, though I've not thought
   about it very hard.
   
   			regards, tom lane
   
   
   
   

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

   Alban Hertroys <haramrae@gmail.com> — 2024-05-31T07:15:54Z

   > On 31 May 2024, at 00:34, johnlumby@hotmail.com wrote:
   > 
   > On 5/30/24 4:56 PM, David G. Johnston wrote:
   
   (…)
   
   >>  If anything is done it would have to be new syntax.  
   >> 
   >> 
   > A much bigger task surely. 
   > 
   > On 5/30/24 5:19 PM, Adrian Klaver wrote:
   >> 
   >> 2) Use INSTEAD OF triggers: 
   >> 
   >> 
   > 
   > Unfortunately the same functionality as in my example with the RULE is not supported for triggers on views :   from the manual
   > INSTEAD OF triggers may only be defined on views, and only at row level;
   > 
   > A RULE is essentially a statement-level operation which is what I need for this particular case.      A row-level trigger would not work because it cannot "see" the query causing it to be fired,   and also ,  (most importantly) is not fired at all if no rows match the original query,     whereas a RULE is always in effect regardless of which rows are involved. before.      I should add that the RULE I showed in my example is not the only RULE being used on this view  -    there are other conditional RULEs,   and the combined effect is of being able to change the effect of the original statement into a set of new statements,   one of which does what is needed.
   > 
   > And if you are now inclined to say "well,    maybe the application itself is poorly written and should be changed"  -   I would have to agree,    but that is not mine to change.
   > 
   > But I suppose that my next question,   given what you both say about the RULE system being a dead-end,  is whether there is any likelihood of supporting an INSTEAD OF trigger on a view at statement level?   Maybe that stands more chance of going somewhere?
   
   What you’re attempting to do boils down to adding a virtualisation layer over the database.
   
   Several middleware products exist that provide data virtualisation, products that are accessed as a database (or as a web service, or both) that pass on queries to connected systems. The virtualisation layer rewrites those queries between the data sources and the user-visible virtual database connection and between generalised SQL and native dialects and languages.
   
   If existing products support your particular use-case though, namely rewriting operational data-storage queries to data-source specific DML statements and then report the correct number of affected rows back, I don’t know.
   
   However, an important reason that PG rules are deprecated (as I understand it) is that it is very hard to get right for generated columns, which are operations with side-effects (such as incrementing a sequence value, for example) that are included in those queries rewritten by the specified rules.
   I doubt that a data virtualisation layer would be able to solve that particular problem.
   
   Nevertheless, considering what path you’re on, they may be worth looking at. I don’t think there are any open-source initiatives (unfortunately), they’re all commercial products AFAIK, and not cheap. With a suitable use-case they can be rather valuable tools too though.
   
   Regards,
   
   Alban Hertroys
   --
   Als je de draak wilt steken met iemand,
   dan helpt het,
   als die een punthoofd heeft.
   
   
   
   
   
   
   
   

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

   Adrian Klaver <adrian.klaver@aklaver.com> — 2024-05-31T14:53:46Z

   On 5/30/24 15:34, johnlumby@hotmail.com wrote:
   > 
   > On 5/30/24 4:56 PM, David G. Johnston wrote:
   >>
   >> Very little interest exists in working on user-specified rules.  They 
   >> are practically deprecated.
   > 
   > 
   > Ah  -   pity  -   see my last comment to Adrian's
   > 
   > 
   >>
   >>     Any interest in perhaps providing a choice via a configuration
   >>     parameter?
   >>
   >>
   >>  If anything is done it would have to be new syntax.
   >>
   >>
   > A much bigger task surely.
   > 
   > 
   > On 5/30/24 5:19 PM, Adrian Klaver wrote:
   >>
   >> 2) Use INSTEAD OF triggers:
   >>
   >>
   > 
   > Unfortunately the same functionality as in my example with the RULE is 
   > not supported for triggers on views :   from the manual
   > 
   > |INSTEAD OF| triggers may only be defined on views, *and only at row level*;
   > 
   > 
   > A RULE is essentially a statement-level operation which is what I need 
   > for this particular case.      A row-level trigger would not work 
   > because it cannot "see" the query causing it to be fired, and also ,  
   > (most importantly) is not fired at all if no rows match the original 
   > query,     whereas a RULE is always in effect regardless of which rows 
   > are involved. before.      I should add that the RULE I showed in my 
   > example is not the only RULE being used on this view  -    there are 
   > other conditional RULEs,   and the combined effect is of being able to 
   > change the effect of the original statement into a set of new 
   > statements,   one of which does what is needed.
   > 
   
   Basically don't give the user what they asked for, give them some 
   cobbled together  on the fly version. Who decides that what the user 
   needs? Seems to me this the point at which to have a discussion with the 
   application developers about having the application asking the correct 
   questions, rather then going down the road of bait and switch.
   
   
   > 
   > And if you are now inclined to say "well,    maybe the application 
   > itself is poorly written and should be changed"  -   I would have to 
   > agree,    but that is not mine to change.
   
   > 
   > 
   > But I suppose that my next question,   given what you both say about the 
   > RULE system being a dead-end,  is whether there is any likelihood of 
   > supporting an INSTEAD OF trigger on a view at statement level?   Maybe 
   > that stands more chance of going somewhere?
   > 
   > 
   > 
   > Cheers,     John Lumby
   > 
   > 
   
   -- 
   Adrian Klaver
   adrian.klaver@aklaver.com