What I found interesting is that the paper claims they use the mechanism internally for a bunch of relational DB features:
"Lastly, our rule system can provide database services such as views, protection, integrity constraints, and referential integrity simply by applying the rules system in particular ways. Consequently, no special purpose code need be included in POSTGRES to handle these tasks."
Is that still the case or, considering the Rule Manager is not recommended, has it been replaced for internal services?
I remember Stonebraker's Turing Award Lecture, where he talks about their attempt at an "ALWAYS" command not working, so I was wondering whether the Rule Manager talked about here is that thing that didn't work or the thing they replaced it with.
Looking at the talk again, he says that they figured out ALWAYS wouldn't work around 86-87[1] and this paper is 88, so I am guessing this is the replacement.
True. But it's imo sad thing that it is so (at the very least it leads to duplicated functionality and duplicates query tree copies, but it also makes it harder to implement functionality like automatic matview usage etc). I'm quite sure that nobody would implement views in today's postgres that way. If there weren't the fact that there's still resistance to removing rules, I'd argue we should reimplement them.
The answers bring up the topic of updatable views, which got me curious.
In what use-case are updatable views superior to updating directly on the underlying table?
Views are great for bringing together data from multiple tables, but it seems that such views cannot be updatable: (from the docs) "The view must have exactly one entry in its FROM list". Thus it seems, while it might be handy, views can't help you spread an update across tables.
Presumably, a view with `LOCAL CHECK OPTION` or `CASCADED CHECK OPTION` could be granted to a user, providing fine-grained control over the kind of data that user can update. Is this accurate?
> The answers bring up the topic of updatable views, which got me curious.
Even for those, INSTEAD OF triggers provide a better solution these days.
> Views are great for bringing together data from multiple tables, but it seems that such views cannot be updatable: (from the docs) "The view must have exactly one entry in its FROM list". Thus it seems, while it might be handy, views can't help you spread an update across tables.
Note that that's just talking about automatic updatability. If you manually create INSTEAD OF triggers that restriction doesn't exist.
> In what use-case are updatable views superior to updating directly on the underlying table?
It's not uncommon that views are used to keep older applications working in the face of schema chanes, or to merge previously independent databases together. That will often mean that DML to one table affects multiple underlying tables.
https://stackoverflow.com/questions/5894142/what-are-postgre...
TL;DR: don't use them except in a few edge cases.
"Lastly, our rule system can provide database services such as views, protection, integrity constraints, and referential integrity simply by applying the rules system in particular ways. Consequently, no special purpose code need be included in POSTGRES to handle these tasks."
Is that still the case or, considering the Rule Manager is not recommended, has it been replaced for internal services?
I remember Stonebraker's Turing Award Lecture, where he talks about their attempt at an "ALWAYS" command not working, so I was wondering whether the Rule Manager talked about here is that thing that didn't work or the thing they replaced it with.
Looking at the talk again, he says that they figured out ALWAYS wouldn't work around 86-87[1] and this paper is 88, so I am guessing this is the replacement.
[1] https://youtu.be/sEPTZVGk3WY?t=1843
In what use-case are updatable views superior to updating directly on the underlying table?
Views are great for bringing together data from multiple tables, but it seems that such views cannot be updatable: (from the docs) "The view must have exactly one entry in its FROM list". Thus it seems, while it might be handy, views can't help you spread an update across tables.
Presumably, a view with `LOCAL CHECK OPTION` or `CASCADED CHECK OPTION` could be granted to a user, providing fine-grained control over the kind of data that user can update. Is this accurate?
What am I missing?
Even for those, INSTEAD OF triggers provide a better solution these days.
> Views are great for bringing together data from multiple tables, but it seems that such views cannot be updatable: (from the docs) "The view must have exactly one entry in its FROM list". Thus it seems, while it might be handy, views can't help you spread an update across tables.
Note that that's just talking about automatic updatability. If you manually create INSTEAD OF triggers that restriction doesn't exist.
> In what use-case are updatable views superior to updating directly on the underlying table?
It's not uncommon that views are used to keep older applications working in the face of schema chanes, or to merge previously independent databases together. That will often mean that DML to one table affects multiple underlying tables.