Thread: Rules: A Modest Proposal
Folks, At the moment, user-accessible RULEs have, as far as I know, just two sane uses: * Writing to VIEWs * Routing writes to partitions And the second is pretty thin, given the performance issues for numbers of partitions over 2. What say we see about addressing those problems separately, and removing user-accessible RULEs entirely? There are already patches to deal with the first, at least for the kinds of VIEWs where this can be deduced automatically, and people are starting to take on the second. The one remaining (as in nobody's really addressed it with code) issue would be triggers on VIEWs. As other systems have done it, it's clearly not essentially impossible. What would be needed? Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
2009/10/4 David Fetter <david@fetter.org>: > Folks, > > At the moment, user-accessible RULEs have, as far as I know, just two > sane uses: > > * Writing to VIEWs > * Routing writes to partitions somebody use it as instead triggers. And I am sure, so there are people, who use it for writable views. regards Pavel Stehule > > And the second is pretty thin, given the performance issues for > numbers of partitions over 2. > > What say we see about addressing those problems separately, and > removing user-accessible RULEs entirely? > > There are already patches to deal with the first, at least for the > kinds of VIEWs where this can be deduced automatically, and people are > starting to take on the second. > > The one remaining (as in nobody's really addressed it with code) issue > would be triggers on VIEWs. As other systems have done it, it's > clearly not essentially impossible. What would be needed? > > Cheers, > David. > -- > David Fetter <david@fetter.org> http://fetter.org/ > Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter > Skype: davidfetter XMPP: david.fetter@gmail.com > > Remember to vote! > Consider donating to Postgres: http://www.postgresql.org/about/donate > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
> There are already patches to deal with the first, at least for the > kinds of VIEWs where this can be deduced automatically, and people are > starting to take on the second. How would we deal with VIEWs which weren't simple enough for automated updating, then? I don't think that removing a major feature, one which some users have written applications around, is even feasible. What would be the benefit of this radical proposal? --Josh Berkus
On Sun, Oct 04, 2009 at 11:42:45AM -0700, Josh Berkus wrote: > > > There are already patches to deal with the first, at least for the > > kinds of VIEWs where this can be deduced automatically, and people are > > starting to take on the second. > > How would we deal with VIEWs which weren't simple enough for automated > updating, then? > > I don't think that removing a major feature, one which some users have > written applications around, is even feasible. > > What would be the benefit of this radical proposal? > > --Josh Berkus > When you speak of writing to a view, what do you mean exactly? Are we saying refresh a view or update the parent tables of a view? -- --Dan
Dan Colish wrote: > When you speak of writing to a view, what do you mean exactly? Are we saying > refresh a view or update the parent tables of a view? > > > He means INSERT, UPDATE and DELETE operations on the view. cheers andrew
On Sun, Oct 04, 2009 at 08:48:15PM +0200, Pavel Stehule wrote: > 2009/10/4 David Fetter <david@fetter.org>: > > Folks, > > > > At the moment, user-accessible RULEs have, as far as I know, just two > > sane uses: > > > > * Writing to VIEWs > > * Routing writes to partitions > > somebody use it as instead triggers. Some people also shoot themselves in the foot. They're mostly a foot-gun. > And I am sure, so there are people, who use it for writable views. That *is* the first case I mentioned. Your point is? Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Sun, Oct 04, 2009 at 03:15:10PM -0400, Andrew Dunstan wrote: > > > Dan Colish wrote: > >When you speak of writing to a view, what do you mean exactly? Are we saying > >refresh a view or update the parent tables of a view? > > > > > > He means INSERT, UPDATE and DELETE operations on the view. > > cheers > > andrew > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers How would you resolve where to perform these operations in the parent tables? I have not discovered a good way to determine which tables a user would desire to alter if the view contains a subset of data from the parent and these subsets do not include the primary keys. Even with primary keys as members of a view, there is a good potential for side effects. For example, consider the following tables: usernames, student_grades, course_listings If you have a view joining all three tables and delete one row in the view, you have the potential for deleting too much data from a parent table; ie, you choose to delete a username and its associated grades but also end up deleteing a course. You could also delete a course and end up deleting all the usernames and the grades associated. -- --Dan
On Sun, Oct 04, 2009 at 11:42:45AM -0700, Josh Berkus wrote: > > There are already patches to deal with the first, at least for the > > kinds of VIEWs where this can be deduced automatically, and people > > are starting to take on the second. > > How would we deal with VIEWs which weren't simple enough for > automated updating, then? View triggers, as proposed. > I don't think that removing a major feature, one which some users > have written applications around, is even feasible. *I've* written an application around them, and frankly, they are a giant foot-gun in every case that's not already handle-able other ways. > What would be the benefit of this radical proposal? The radical proposal was the RULE system. It's been tested now, and it's pretty much failed. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Sun, Oct 4, 2009 at 3:34 PM, David Fetter <david@fetter.org> wrote: >> What would be the benefit of this radical proposal? > > The radical proposal was the RULE system. It's been tested now, and > it's pretty much failed. You still haven't explained what actual benefit we'd get out of doing this. I agree that rules, except for SELECT rules, don't seem to be very useful. Perhaps others have found them so, but I have found triggers to be a better fit for everything that I ever want to do. Every time I think, hmm, maybe I could use a rule for that, I reread the chapter and change my mind. However, there is a very real possibility that there are people out there who have applications that are based on the way rules work today. If we were to remove support for rules, they would not be able to upgrade past 8.4. That seems to me to be the sort of thing that we wouldn't want to do unless we had a good reason - and the closest you've come to saying what you think that reason might be is "they're mostly a foot-gun", which I don't find very compelling. I think we want to be moving in the direction of making upgrading easier, not more difficult, and that means maintaining backward compatibility even for features that are of marginal utility, unless they're getting in the way of something else. ...Robert
2009/10/4 David Fetter <david@fetter.org>: > On Sun, Oct 04, 2009 at 08:48:15PM +0200, Pavel Stehule wrote: >> 2009/10/4 David Fetter <david@fetter.org>: >> > Folks, >> > >> > At the moment, user-accessible RULEs have, as far as I know, just two >> > sane uses: >> > >> > * Writing to VIEWs >> > * Routing writes to partitions >> >> somebody use it as instead triggers. > > Some people also shoot themselves in the foot. They're mostly a > foot-gun. it same as inheritance. BEFORE triggers should be a problem to (in some cases) > >> And I am sure, so there are people, who use it for writable views. > > That *is* the first case I mentioned. Your point is? sorry updateable views, is correct name. I know, so rules are dangerous gun, but I know so there are people, who use it. And actually we don't have a substitutions. I thing so if pg drop a rules. then it needs true updateable views and instead triggers. And maybe some as audit tools. When you would to to drop some functionality, then you have to propose a substitution. Pavel > > Cheers, > David. > -- > David Fetter <david@fetter.org> http://fetter.org/ > Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter > Skype: davidfetter XMPP: david.fetter@gmail.com > > Remember to vote! > Consider donating to Postgres: http://www.postgresql.org/about/donate >
David, > The radical proposal was the RULE system. It's been tested now, and > it's pretty much failed. I don't think you've demonstrated that. I know *you* don't like RULEs, but others do. I could propose that UUIDs are a bankrupt concept (which I believe) and therefore we should drop the UUID contrib module, but I don't think I'd get very far. --Josh
On Sun, Oct 04, 2009 at 01:25:31PM -0700, Josh Berkus wrote: > David, > > > The radical proposal was the RULE system. It's been tested now, > > and it's pretty much failed. > > I don't think you've demonstrated that. I know *you* don't like > RULEs, but others do. It's less about like or dislike and more about facing up to the reality that we've got a major legacy foot-gun left over from the experimentation of the Berkeley days. You'll recall we removed time travel for much less good reasons, namely performance, as opposed to actually breaking stuff. What people actually use RULEs for successfully, I've named. I'm proposing we cover those cases, deprecate (not depreciate ;) RULEs in the cycle or two following that coverage, and remove them after that. > I could propose that UUIDs are a bankrupt concept (which I believe) > and therefore we should drop the UUID contrib module, but I don't > think I'd get very far. UUIDs are much harder to shoot yourself with. :) Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Sun, Oct 04, 2009 at 04:07:40PM -0400, Robert Haas wrote: > On Sun, Oct 4, 2009 at 3:34 PM, David Fetter <david@fetter.org> wrote: > >> What would be the benefit of this radical proposal? > > > > The radical proposal was the RULE system. It's been tested now, > > and it's pretty much failed. > > You still haven't explained what actual benefit we'd get out of > doing this. Removing land mines is a benefit. > I agree that rules, except for SELECT rules, don't seem to be very > useful. Perhaps others have found them so, but I have found > triggers to be a better fit for everything that I ever want to do. > Every time I think, hmm, maybe I could use a rule for that, I reread > the chapter and change my mind. It's people who either don't read the chapter or don't change their mind who get in all that trouble. Actually using RULEs is just cruisin' for a bruisin'. > However, there is a very real possibility that there are people out > there who have applications that are based on the way rules work > today. If we were to remove support for rules, they would not be able > to upgrade past 8.4. That seems to me to be the sort of thing that we > wouldn't want to do unless we had a good reason - and the closest > you've come to saying what you think that reason might be is "they're > mostly a foot-gun", which I don't find very compelling. In another post, I proposed a deprecation and removal strategy. If someone has a use case I haven't named, they've yet to chime in. Of course, it's a little early yet, but I've seen a *lot* of PostgreSQL deployments, and none of them had RULEs for anything but the cases I mentioned. > I think we want to be moving in the direction of making upgrading > easier, not more difficult, and that means maintaining backward > compatibility even for features that are of marginal utility, unless > they're getting in the way of something else. Well, there's a utilitarian argument for not having land mines in our code. To call what RULEs can do to your assumptions about how things work (data integrity, etc.) in PostgreSQL, "astonishing" would be an understatement. As for the upgrades, you've made an interesting point. I suspect that for the cases mentioned, there could be a mechanical way to do what needs doing. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
* David Fetter (david@fetter.org) wrote:
> On Sun, Oct 04, 2009 at 04:07:40PM -0400, Robert Haas wrote:
> > > The radical proposal was the RULE system. It's been tested now,
> > > and it's pretty much failed.
> >
> > You still haven't explained what actual benefit we'd get out of
> > doing this.
>
> Removing land mines is a benefit.
Removing useful functionality without replacing it is definitely worse.
Do we have a patch which implements the necessary mechanics to replace
RULEs, even for the specific situations you list? Until then, I don't
think there's much to discuss.
Thanks,
Stephen
On Sun, October 4, 2009 1:48 pm, Pavel Stehule wrote: > 2009/10/4 David Fetter <david@fetter.org>: >> Folks, >> >> At the moment, user-accessible RULEs have, as far as I know, just two >> sane uses: >> >> * Writing to VIEWs >> * Routing writes to partitions > > somebody use it as instead triggers. And I am sure, so there are > people, who use it for writable views. We have such a rule (instead of a trigger) in our SaaS app. I'm lobbying to remove it, and make it a real trigger, but that hasn't happened yet. so there are folks out there. > > regards > Pavel Stehule > >> >> And the second is pretty thin, given the performance issues for >> numbers of partitions over 2. >> >> What say we see about addressing those problems separately, and >> removing user-accessible RULEs entirely? >> >> There are already patches to deal with the first, at least for the >> kinds of VIEWs where this can be deduced automatically, and people are >> starting to take on the second. >> >> The one remaining (as in nobody's really addressed it with code) issue >> would be triggers on VIEWs.  As other systems have done it, it's >> clearly not essentially impossible.  What would be needed? >> >> Cheers, >> David. >> -- >> David Fetter <david@fetter.org> http://fetter.org/ >> Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter >> Skype: davidfetter    XMPP: david.fetter@gmail.com >> >> Remember to vote! >> Consider donating to Postgres: http://www.postgresql.org/about/donate >> >> -- >> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-hackers >> > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 512-248-2683 E-Mail: ler@lerctr.org US Mail: 430 Valona Loop, Round Rock, TX 78681-3893
On Oct 4, 2009, at 1:57 PM, David Fetter wrote: > It's less about like or dislike and more about facing up to the > reality that we've got a major legacy foot-gun left over from the > experimentation of the Berkeley days. I think you're going to need to be a bit more concrete than that. In what way is it a foot-gun? What examples can you provide? What, exactly, are the issues? Perhaps, given concrete examples of issues with RULEs, we could look at addressing those problems rather than throwing out the baby (let alone put the baby in concrete -- sorry, the metaphors are getting away from me). Best, David
On Sun, Oct 4, 2009 at 6:42 PM, David Fetter <david@fetter.org> wrote: >> I agree that rules, except for SELECT rules, don't seem to be very >> useful. Perhaps others have found them so, but I have found >> triggers to be a better fit for everything that I ever want to do. >> Every time I think, hmm, maybe I could use a rule for that, I reread >> the chapter and change my mind. > > It's people who either don't read the chapter or don't change their > mind who get in all that trouble. Actually using RULEs is just > cruisin' for a bruisin'. Well, it's not our custom to tailor our feature set to people who aren't willing or able to read the instructions. If we're going to start removing all the features that will bite you in the posterior in such cases, can we start with NOT IN and the application of IS NULL/IS NOT NULL to records? Because I'd bet good money those bite VASTLY more people than anything involving rules. > As for the upgrades, you've made an interesting point. I suspect that > for the cases mentioned, there could be a mechanical way to do what > needs doing. Only if the new system is pretty darn similar to how the existing system works. But at this point this is all hand-waving, as we have no design for anything that could replace what we have now even for the use cases you think are important (which I'm also unconvinced cover what everyone else thinks are important, but that's a separate issue). ...Robert
David E. Wheeler wrote: > On Oct 4, 2009, at 1:57 PM, David Fetter wrote: > > >It's less about like or dislike and more about facing up to the > >reality that we've got a major legacy foot-gun left over from the > >experimentation of the Berkeley days. > > I think you're going to need to be a bit more concrete than that. In > what way is it a foot-gun? What examples can you provide? What, > exactly, are the issues? While I don't agree with David Fetter's premise, I think rehashing how we handle VIEWs would be a good step towards updatable views. Right now, the implementation of that is stalled precisely because of the rule system. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
On Sun, Oct 04, 2009 at 08:54:56PM -0400, Alvaro Herrera wrote: > David E. Wheeler wrote: > > On Oct 4, 2009, at 1:57 PM, David Fetter wrote: > > > > >It's less about like or dislike and more about facing up to the > > >reality that we've got a major legacy foot-gun left over from the > > >experimentation of the Berkeley days. > > > > I think you're going to need to be a bit more concrete than that. In > > what way is it a foot-gun? What examples can you provide? What, > > exactly, are the issues? > > While I don't agree with David Fetter's premise, I think rehashing how > we handle VIEWs would be a good step towards updatable views. Right > now, the implementation of that is stalled precisely because of the rule > system. > I am not sure where that view implemenation is, but I doubt its stalled because of the rule system. You can definitely create updatable views using rules. However, I'm not sure updatable views are a good thing in most scenarios. I see way too much damage as a likely outcome. Rules are one of the great generative features of postgres and I see no reason to cut them. Features should not be limited just because they can be used incorrectly, since they can also be used in other correct/interesting ways we have yet to think up. -- --Dan
On Sun, Oct 4, 2009 at 8:54 PM, Alvaro Herrera <alvherre@commandprompt.com> wrote: > David E. Wheeler wrote: >> On Oct 4, 2009, at 1:57 PM, David Fetter wrote: >> >> >It's less about like or dislike and more about facing up to the >> >reality that we've got a major legacy foot-gun left over from the >> >experimentation of the Berkeley days. >> >> I think you're going to need to be a bit more concrete than that. In >> what way is it a foot-gun? What examples can you provide? What, >> exactly, are the issues? > > While I don't agree with David Fetter's premise, I think rehashing how > we handle VIEWs would be a good step towards updatable views. Right > now, the implementation of that is stalled precisely because of the rule > system. This is the last I remember hearing of it, which seems to suggest that only a week's worth of work (maybe a bit more for those of us who are not Tom Lane) is needed: http://archives.postgresql.org/pgsql-hackers/2009-01/msg01746.php But maybe you have some other thoughts? ...Robert
Robert Haas escribió:
> > While I don't agree with David Fetter's premise, I think rehashing how
> > we handle VIEWs would be a good step towards updatable views. Right
> > now, the implementation of that is stalled precisely because of the rule
> > system.
>
> This is the last I remember hearing of it, which seems to suggest that
> only a week's worth of work (maybe a bit more for those of us who are
> not Tom Lane) is needed:
Right, that's exactly what I meant. Note that a week's worth of Tom
work in that area is probably measured in months for anybody else ("a
bit more" in your words), and this fits my definition of "rehashing view
handling".
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Sun, Oct 4, 2009 at 10:01 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
> Robert Haas escribió:
>
>> > While I don't agree with David Fetter's premise, I think rehashing how
>> > we handle VIEWs would be a good step towards updatable views. Right
>> > now, the implementation of that is stalled precisely because of the rule
>> > system.
>>
>> This is the last I remember hearing of it, which seems to suggest that
>> only a week's worth of work (maybe a bit more for those of us who are
>> not Tom Lane) is needed:
>
> Right, that's exactly what I meant. Note that a week's worth of Tom
> work in that area is probably measured in months for anybody else ("a
> bit more" in your words),
:-)
> and this fits my definition of "rehashing view
> handling".
The trick is to get rid of the self-join, I suppose, but it's unclear
to me whether some change to the existing view handling would make
that easier. Do you have an idea?
...Robert
On Sun, 2009-10-04 at 18:24 -0700, Dan Colish wrote: > I am not sure where that view implemenation is, but I doubt its > stalled because of the rule system. It is. > You can definitely create updatable views using rules. Sure you can, but they won't work in various significant corner cases. Search the archives for "updatable views" for details.
On Sun, 2009-10-04 at 20:54 -0400, Alvaro Herrera wrote: > While I don't agree with David Fetter's premise, I think rehashing how > we handle VIEWs would be a good step towards updatable views. Right > now, the implementation of that is stalled precisely because of the rule > system. The way forward with updatable views is triggers on views. I was going to write something about that in the future. I haven't worked out all the details. But the select part of views will still need to be done with rules.
--On 5. Oktober 2009 09:51:29 +0300 Peter Eisentraut <peter_e@gmx.net> wrote: > The way forward with updatable views is triggers on views. I was going > to write something about that in the future. I haven't worked out all > the details. In the mentioned discussion there was already the notion of "substitution rules". The notion of this pretty much applies to something like "instead of statement triggers". AFAIR, the discussion came up with a proposal for some CURRENT OF-Syntax in rules, which creates some magic rule effectively avoiding the self join and substitute the original query with the WHERE-condition of the view appended. -- Thanks Bernd
--On 4. Oktober 2009 21:37:45 -0400 Robert Haas <robertmhaas@gmail.com> wrote: > This is the last I remember hearing of it, which seems to suggest that > only a week's worth of work (maybe a bit more for those of us who are > not Tom Lane) is needed: > > http://archives.postgresql.org/pgsql-hackers/2009-01/msg01746.php > > But maybe you have some other thoughts? The reason i didn't spent any time on this, is because i'm not sure that following the "Rules" path is the way to go. As Peter mentioned, an alternative (and pretty much the same way like other databases do), is to figure out how triggers on views can handle this. -- Thanks Bernd
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > At the moment, user-accessible RULEs have, as far as I know, > just two sane uses: > > * Writing to VIEWs > * Routing writes to partitions Maybe you need a larger clientele list, because I still run up against RULEs in production environments that don't fit into the categories above. Here's one I came across just a couple weeks ago. Names changed for privacy: CREATE RULE update_other_table AS ON INSERT TO myschema.mytable DO ALSO INSERT INTO myschema.othertable (col1,col2,col3) VALUES (NEW.col1, NEW.col2, NEW.col3); Could this be done with a trigger? Yes, but on the plus rules side: * It's faster * It's easier to write * It's immediately viewable as to what is going on with a \d mytable * Dropping it won't leave an unused function around * We can still do ALTER TABLE DISABLE TRIGGER ALL I can give more examples, if you like, but removing a major feature of Postgres with no real justificatgion seems a bit hasty, to say the least. > They're mostly a foot-gun. Lots of things in Postgres could be considered potential foot guns. Frankly, I don't think rules are even near the top of such a list. Can you give examples of rule foot guns? - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200910050758 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAkrJ5wUACgkQvJuQZxSWSsjS7ACeMl8YfE38aVjnhZX3/gp8Ffgq tZsAoLQPaPxS5ky4SZ8yXMdKNTWN1ZVX =RmyV -----END PGP SIGNATURE-----
Greg Sabino Mullane wrote: > Could this be done with a trigger? Yes, but on the plus rules side: > > * It's faster > * It's easier to write > * It's immediately viewable as to what is going on with a \d mytable > * Dropping it won't leave an unused function around > * We can still do ALTER TABLE DISABLE TRIGGER ALL > > I can give more examples, if you like, but removing a major feature of > Postgres with no real justificatgion seems a bit hasty, to say the least. Agreed, here is another rules example that logs table changes to a log table: http://www.postgresql.org/files/documentation/books/aw_pgsql/node124.html -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
>>>>> "Greg" == "Greg Sabino Mullane" <greg@turnstep.com> writes: >> They're mostly a foot-gun. Greg> Lots of things in Postgres could be considered potential footGreg> guns. Frankly, I don't think rules are even nearthe top ofGreg> such a list. Can you give examples of rule foot guns? There are so many it's hard to know where to start. Here are a couple of the more common ones: 1) any reference in an insert rule to NEW.col where col has a volatile default, or the expression in the insert statementwas volatile, or the expression's value is changed by the insert, will do the wrong thing: create table t (a integer); create table t_log (a integer); create rule t_ins AS ON insert TO t do also insert into t_log values (NEW.a); insert into t values (floor(random()*1000)::integer); select * from t;a ----33 (1 row) select * from t_log; a -----392 (1 row) (think "nextval" or "uuid_generate_*" for more realistic examples) 2) any rule with multiple actions, each action is affected by the results of the previous ones. A classic example of thisis in the use of OLD in delete or update rules; OLD _does not return a row_ if a previous action in the rule deletedthe row or updated it so that it no longer matches. -- Andrew (irc:RhodiumToad)
On Mon, Oct 05, 2009 at 02:53:56PM +0100, Andrew Gierth wrote: > Here are a couple of the more common ones: > > 1) any reference in an insert rule to NEW.col where col has a volatile > default, or the expression in the insert statement was volatile, or > the expression's value is changed by the insert, will do the wrong > thing: ISTM it may be possible to use the new WITH construct here. So the rule evaluation for the following > create table t (a integer); > create table t_log (a integer); > create rule t_ins AS ON insert TO t do also insert into t_log values (NEW.a); > insert into t values (floor(random()*1000)::integer); becomes something like: WITH NEW AS ( insert into t values (floor(random()*1000)::integer); RETURNING * ) insert into t_log values (NEW.a); Would this not have the required semantics? Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines.
Martijn van Oosterhout <kleptog@svana.org> writes:
> ISTM it may be possible to use the new WITH construct here. So the rule
> evaluation for the following
>> create table t (a integer);
>> create table t_log (a integer);
>> create rule t_ins AS ON insert TO t do also insert into t_log values (NEW.a);
>> insert into t values (floor(random()*1000)::integer);
> becomes something like:
> WITH NEW AS (
> insert into t values (floor(random()*1000)::integer);
> RETURNING *
> )
> insert into t_log values (NEW.a);
> Would this not have the required semantics?
Interesting idea, but it's not clear how to make it work with multiple
DO ALSO rules, nor with conditional DO INSTEAD rules.
regards, tom lane
On Mon, Oct 05, 2009 at 10:32:53AM -0400, Tom Lane wrote: > Martijn van Oosterhout <kleptog@svana.org> writes: > > WITH NEW AS ( > > insert into t values (floor(random()*1000)::integer); > > RETURNING * > > ) > > insert into t_log values (NEW.a); > > > Would this not have the required semantics? > > Interesting idea, but it's not clear how to make it work with multiple > DO ALSO rules, nor with conditional DO INSTEAD rules. Well, my (possibly naive) view is: - Multiple DO ALSO rules seem easy. There is a patch in the works which makes INSERT/UPDATE/DELETE into proper node typesso they can actually appear in the WITH clause above. With a minor extension you could create a MultipleStatement nodetype which merely runs each substatement, like Append, but for plans. - Conditional DO INSTEAD rules are brain benders. Logically, I think they split the plan in two, one with the condition,one with the negative of the condition. So *maybe* they could also be handled by such a MultipleStatement nodebut then... I get visions of people writing a SELECT rule with a conditional DELETE statement with RETURNING *. Then, SELECTing the table would return everything but conditionally DELETE some rows. Something like: WITH OLD AS (SELECT * FROM foo) MULTISTATEMENT( SELECT * FROM OLD WHERE condition; DELETE FROM OLD WHERE NOT condition RETURNING *; ) As for actual implementation it seems doable, but I may be being impossibly naive. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines.
On Mon, Oct 05, 2009 at 09:50:18AM +0300, Peter Eisentraut wrote: > On Sun, 2009-10-04 at 18:24 -0700, Dan Colish wrote: > > I am not sure where that view implemenation is, but I doubt its > > stalled because of the rule system. > > It is. > > > You can definitely create updatable views using rules. > > Sure you can, but they won't work in various significant corner cases. > > Search the archives for "updatable views" for details. > I don't even want updatable views! I'm looking through those archives and its vague what killed them, but bad rules are definitely part of it. However, that doesn't mean you ditch the rule system because it didn't work for this particular situation. Maybe you could highlight some messages that point to the precise corner cases that make rules so bad? I would expect these corner cases would have nothing to do with updatable views, since they are such a bad idea to have automatically implemented. -- --Dan
Dan Colish wrote: > On Mon, Oct 05, 2009 at 09:50:18AM +0300, Peter Eisentraut wrote: > > On Sun, 2009-10-04 at 18:24 -0700, Dan Colish wrote: > > > You can definitely create updatable views using rules. > > > > Sure you can, but they won't work in various significant corner cases. > > > > Search the archives for "updatable views" for details. > > I don't even want updatable views! Why would you argue that point? They are specified in the SQL standard somewhere. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Mon, Oct 05, 2009 at 11:28:13AM -0400, Alvaro Herrera wrote: > Dan Colish wrote: > > On Mon, Oct 05, 2009 at 09:50:18AM +0300, Peter Eisentraut wrote: > > > On Sun, 2009-10-04 at 18:24 -0700, Dan Colish wrote: > > > > > You can definitely create updatable views using rules. > > > > > > Sure you can, but they won't work in various significant corner cases. > > > > > > Search the archives for "updatable views" for details. > > > > I don't even want updatable views! > > Why would you argue that point? They are specified in the SQL standard > somewhere. > I do not really think updatable views are needed. Maybe when the standard was written things are different; I guess you're talking about 2003. Just because something is in a standard, doesnt mean it has to be implemented. As long as you don't implement something outside of the standard, I do not have an issue. -- --Dan
On Mon, Oct 05, 2009 at 11:28:13AM -0400, Alvaro Herrera wrote: > Dan Colish wrote: > > On Mon, Oct 05, 2009 at 09:50:18AM +0300, Peter Eisentraut wrote: > > > On Sun, 2009-10-04 at 18:24 -0700, Dan Colish wrote: > > > > > You can definitely create updatable views using rules. > > > > > > Sure you can, but they won't work in various significant corner cases. > > > > > > Search the archives for "updatable views" for details. > > > > I don't even want updatable views! > > Why would you argue that point? They are specified in the SQL > standard somewhere. Feature T111, described in sections 15.9, 15.12 and 15.15 of SQL:2008, in particular. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
2009/10/5 Dan Colish <dan@unencrypted.org>: > On Mon, Oct 05, 2009 at 11:28:13AM -0400, Alvaro Herrera wrote: >> Dan Colish wrote: >> > On Mon, Oct 05, 2009 at 09:50:18AM +0300, Peter Eisentraut wrote: >> > > On Sun, 2009-10-04 at 18:24 -0700, Dan Colish wrote: >> >> > > > You can definitely create updatable views using rules. >> > > >> > > Sure you can, but they won't work in various significant corner cases. >> > > >> > > Search the archives for "updatable views" for details. >> > >> > I don't even want updatable views! >> >> Why would you argue that point? They are specified in the SQL standard >> somewhere. >> > > I do not really think updatable views are needed. Maybe when the standard was > written things are different; I guess you're talking about 2003. Just because > something is in a standard, doesnt mean it has to be implemented. As long as you > don't implement something outside of the standard, I do not have an issue. Updatable views are important for porting enterprise applications. I thing, so it has a sense. Regards Pavel Stehule > > -- > --Dan > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
>Stephen Frost <sfrost@snowman.net> wrote: > Do we have a patch which implements the necessary mechanics to > replace RULEs, even for the specific situations you list? Until > then, I don't think there's much to discuss. I thought that until we had discussion and consensus it was premature to start working on a patch.... -Kevin
* Kevin Grittner (Kevin.Grittner@wicourts.gov) wrote:
> >Stephen Frost <sfrost@snowman.net> wrote:
> > Do we have a patch which implements the necessary mechanics to
> > replace RULEs, even for the specific situations you list? Until
> > then, I don't think there's much to discuss.
>
> I thought that until we had discussion and consensus it was premature
> to start working on a patch....
In general that's true, but in this case we're talking about removing a
pretty major feature and replacing it with something else. We havn't
got the 'something else' hammered out yet (or so it sounds to me..) and
I have doubts that we'd be able to really make a call on removing RULEs
until we know and have the specifics of what's replacing it.
That might be possible to do without a patch, but it requires a great
deal more documentation, planning, and information in general before a
decision could be made. Specifically, what people will actually do to
implement the things that RULEs used to provide.
Thanks,
Stephen
Andrew, > 1) any reference in an insert rule to NEW.col where col has a volatile > default, or the expression in the insert statement was volatile, or > the expression's value is changed by the insert, will do the wrong > thing: Is this different from triggers? > 2) any rule with multiple actions, each action is affected by the results of > the previous ones. A classic example of this is in the use of OLD in > delete or update rules; OLD _does not return a row_ if a previous action > in the rule deleted the row or updated it so that it no longer matches. I know this is not any different from triggers which cascade. David's basic proposal, as I understand, is to remove RULEs and replace them with triggers on views. However, there are *lots* of ways to screw yourself up with triggers as well. For example see my previously reported bug about referential integrity and self-triggers. Triggers also have potential security issues which rules lack. So while rules are hard to use and easy to mess up, so are triggers. So while an (arguable) problem is being pointed out, no real solution is being proposed. With some irony, this discussion came about starting with the writable CTE patch ... which is a truly massive foot-gun for someone who doesn't know how to write CTEs. Huge opportunities there for a new DBA to either lock up the server or overwrite half their database. Does that mean we shouldn't do them? No. I happen to like having RULEs in my arsenal of tricks for getting the database to do Nifty Stuff. I've always considered them advanced database programming, and not for beginners. But that describes a lot of PostgreSQL functionality: security definer functions, run-time DDL generation, SQL/MED, untrusted languages, user-defined types and operators. But it's these advanced features which are what makes PostgreSQL interesting as a database. --Josh Berkus
>>>>> "Josh" == Josh Berkus <josh@agliodbs.com> writes: >> 1) any reference in an insert rule to NEW.col where col has a volatile>> default, or the expression in the insert statementwas volatile, or>> the expression's value is changed by the insert, will do the wrong>> thing: Josh> Is this different from triggers? Absolutely. In an AFTER trigger, the trigger's NEW variable is guaranteed to be exactly the inserted values. So doing a log table with triggers is reliable, whereas doing it with rules is not. >> 2) any rule with multiple actions, each action is affected by the>> results of the previous ones. A classic example ofthis is in the>> use of OLD in delete or update rules; OLD _does not return a row_>> if a previous action in the rule deletedthe row or updated it so>> that it no longer matches. Josh> I know this is not any different from triggers which cascade. Of course it is different. A trigger's value of OLD is always the actual content of the previous row version, it doesn't magically disappear the way that rule OLD does. Josh> David's basic proposal, as I understand, is to remove RULEs andJosh> replace them with triggers on views. However,there are *lots*Josh> of ways to screw yourself up with triggers as well. There is simply no comparison here. Triggers are simple procedural logic which any novice can use effectively with little chance of falling into any major pitfalls; rules are a bizarre macro-language which even experts have a hard time using correctly. Josh> For example see my previously reported bug about referentialJosh> integrity and self-triggers. link? Josh> Triggers also have potential security issues which rules lack. Example? Josh> I happen to like having RULEs in my arsenal of tricks forJosh> getting the database to do Nifty Stuff. I've alwaysconsideredJosh> them advanced database programming, and not for beginners. The difference is that rules aren't for advanced users either (as you've just demonstrated by not understanding the differences in behaviour between rules and triggers). -- Andrew (irc:RhodiumToad)
On Mon, Oct 5, 2009 at 10:17 AM, Josh Berkus <josh@agliodbs.com> wrote: > So while rules are hard to use and easy to mess up, so are triggers. So > while an (arguable) problem is being pointed out, no real solution is > being proposed. If you want to implement updatable views I still stand by my (much) earlier design suggestion. They should be implemented just like SELECT on views is currently. The rule is a simple substitution and doesn't try to analyze and decompose the query and figure out how to rewrite it into a complete different query. Most of the work is done, not in the rule, but in the regular SQL parser and statement analyzer where it has a lot more information available to it. So for example this view CREATE VIEW foo AS SELECT a AS aa, b+1 AS bb FROM tab expands this sql: SELECT bb FROM foo into this: SELECT bb FROM (SELECT a AS aa, b+1 AS bb FROM tab) AS foo and it should expand this sql: UPDATE foo SET a=1 WHERE bb=1 into this: UPDATE (SELECT a AS aa, b+1 AS bb FROM tab) AS foo SET a=1 WHERE bb=1 This means extending our regular UPDATE syntax to allow arbitrary inline views in place of the update target. That's harder than the hacks we've been playing with so far to try to reverse engineer the right way to write the update statement for a given view but it would be much much more robust. The statement analyzer handling the update statement has a much better idea of what columns it needs to write to, which tables they depend on, and so on. The problems people run into with rules always come from trying to put too much cleverness into the rule. When you put conditions on the rule based on your partition key or put intelligence in the rule to handle your updatable view logic it embeds dependencies on subtle assumptions about the eventual query which will come along. We've never run into any problems with regular rules used for regular views because all they do is substitute the view in the right place in the query. The select machinery takes care of figuring out how it relates to the rest of the query. As long as the updatable views do the same thing then rules will be exactly the right tool for the job. -- greg
sfrost@snowman.net (Stephen Frost) writes: > * David Fetter (david@fetter.org) wrote: >> On Sun, Oct 04, 2009 at 04:07:40PM -0400, Robert Haas wrote: >> > > The radical proposal was the RULE system. It's been tested now, >> > > and it's pretty much failed. >> > >> > You still haven't explained what actual benefit we'd get out of >> > doing this. >> >> Removing land mines is a benefit. > > Removing useful functionality without replacing it is definitely worse. Well, I think we can start here with the premise that there is disagreement on this... Position #1: Rules are "land mines"; in effect, an "anti-feature." Position #2: Rules represent "useful functionality." I'd tend more towards #1, myself, and with that as a premise, replacement isn't, per se, necessary. The one and only rule I have in the sizable app I'm working on is there because of the absence of updatable views. If we could put triggers on views, then I wouldn't need the rule, and that seems like a reasonable "use case" to have drawn into the modest proposal... -- select 'cbbrowne' || '@' || 'gmail.com'; http://linuxdatabases.info/info/emacs.html "I really only meant to point out how nice InterOp was for someone who doesn't have the weight of the Pentagon behind him. I really don't imagine that the Air Force will ever be able to operate like a small, competitive enterprise like GM or IBM." -- Kent England