public inbox for [email protected]help / color / mirror / Atom feed
Re: Rules and Command Status - update/insert/delete rule with series of commands in action 4+ messages / 3 participants [nested] [flat]
* Re: Rules and Command Status - update/insert/delete rule with series of commands in action @ 2024-05-30 21:19 Adrian Klaver <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Adrian Klaver @ 2024-05-30 21:19 UTC (permalink / raw) To: [email protected] <[email protected]>; [email protected] On 5/30/24 11:32, [email protected] 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 [email protected] ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Rules and Command Status - update/insert/delete rule with series of commands in action @ 2024-05-30 22:34 [email protected] <[email protected]> parent: Adrian Klaver <[email protected]> 0 siblings, 2 replies; 4+ messages in thread From: [email protected] @ 2024-05-30 22:34 UTC (permalink / raw) To: [email protected]; +Cc: Adrian Klaver <[email protected]>; David G. Johnston <[email protected]> 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Rules and Command Status - update/insert/delete rule with series of commands in action @ 2024-05-31 07:15 Alban Hertroys <[email protected]> parent: [email protected] <[email protected]> 1 sibling, 0 replies; 4+ messages in thread From: Alban Hertroys @ 2024-05-31 07:15 UTC (permalink / raw) To: John Lumby <[email protected]>; +Cc: pgsql-general <[email protected]>; Adrian Klaver <[email protected]>; David G Johnston <[email protected]> > On 31 May 2024, at 00:34, [email protected] 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. ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Rules and Command Status - update/insert/delete rule with series of commands in action @ 2024-05-31 14:53 Adrian Klaver <[email protected]> parent: [email protected] <[email protected]> 1 sibling, 0 replies; 4+ messages in thread From: Adrian Klaver @ 2024-05-31 14:53 UTC (permalink / raw) To: [email protected] <[email protected]>; [email protected]; +Cc: David G. Johnston <[email protected]> On 5/30/24 15:34, [email protected] 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 [email protected] ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-05-31 14:53 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-05-30 21:19 Re: Rules and Command Status - update/insert/delete rule with series of commands in action Adrian Klaver <[email protected]> 2024-05-30 22:34 ` [email protected] <[email protected]> 2024-05-31 07:15 ` Alban Hertroys <[email protected]> 2024-05-31 14:53 ` Adrian Klaver <[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