Rules: A Modest Proposal

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

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

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

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

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

* 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 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