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

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 foot
Greg> guns. Frankly, I don't think rules are even near the top of
Greg> 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 statement was 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 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.

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

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 types so they can
actually appear in the WITH clause above. With a minor extension you
could create a MultipleStatement node type 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 node but 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/

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

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 statement was 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 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.

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 and
Josh> 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 referential
Josh> 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 for
Josh> getting the database to do Nifty Stuff. I've always considered
Josh> 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