Deprecating RULES

Simon Riggs <simon@2ndQuadrant.com> writes:

Not many RULE-lovers out there, once you've tried to use them.
Allowing RULEs complicates various things and can make security more difficult.

For 9.3, I suggest we create a DDL trigger by default which prevents
RULEs and throws an ERROR that explains they are now deprecated.

This is utter nonsense. We can't deprecate them until we have a
substitute that is better. If you want to get rid of rules, build the
replacement; don't just try to be a pain in the ass to users.

regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

This is utter nonsense. We can't deprecate them until we have a
substitute that is better. If you want to get rid of rules, build the
replacement; don't just try to be a pain in the ass to users.

My understanding is that the main reason why RULEs are bad™ is that they
will not replace a SQL query, but possibly (or is it always) generate
multiple queries out of it, then run all of them one after the other.

Some programming languages offer much the same user level facility as
what we call RULEs, only without the hazardous behavior we all came to
hate, except for those few who actually understand it, who will then
only hate them for more detailed reasons.

That other facility is called an "advice" and allows users to attach
code to run each time a function/method/command is called, either
before, after or around the main call. In the around case, a syntactic
facility is given that will explicitly call the adviced
function/method/command, and you also have ways to change the arguments,
then of course tweak the result.

E.g an INSTEAD TRIGGER on a VIEW would be equivalent to an AROUND ADVICE
on that view, where the user code would not use the aforementioned
facility to still run the main command.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

On 11 October 2012 20:50, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Simon Riggs <simon@2ndQuadrant.com> writes:

Not many RULE-lovers out there, once you've tried to use them.
Allowing RULEs complicates various things and can make security more difficult.

For 9.3, I suggest we create a DDL trigger by default which prevents
RULEs and throws an ERROR that explains they are now deprecated.

This is utter nonsense. We can't deprecate them until we have a
substitute that is better.

We do, they're called views and triggers, both of which are SQL Standard.

If you want to get rid of rules, build the
replacement; don't just try to be a pain in the ass to users.

Supporting broken and non-standard features *is* a pain in the ass to
users, since they are sometimes persuaded to use them and then regret
it. Or if they do, hit later problems.

You recently rejected a partitioning related patch because it used rules...

Anyway, lets start with a discussion of what rules give us that SQL
standard features do not?

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

For 9.3, I suggest we create a DDL trigger by default which prevents
RULEs and throws an ERROR that explains they are now deprecated.

Well, even if we were considering this, the sequence would need to be:

1. Announce in 9.3 that RULES will be going away RSN.
2. In 9.4, send a warning every time someone loads/edits a user-defined
RULE.
3. In 10.0, get rid of CREATE RULE.

This is utter nonsense. We can't deprecate them until we have a
substitute that is better. If you want to get rid of rules, build the
replacement; don't just try to be a pain in the ass to users.

I was thinking that this should start with making a list of all of the
things you can currently do with RULEs and making sure that we have an
equivalent. When it actually comes time to dump RULEs entirely, we
would also need docs on how to migrate existing RULEs to the new
equivalents (e.g. rewriting a RULE as a view or a trigger).

Wiki page, anyone?

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On Thu, Oct 11, 2012 at 6:25 PM, Simon Riggs <simon@2ndquadrant.com> wrote:

Anyway, lets start with a discussion of what rules give us that SQL
standard features do not?

The somewhat broader question that this elicits is "How would we go
about deprecating a feature that seems to be troublesome?" I think
Josh Berkus describes this downthread in a reasonable way.

There are pretty cool things you can do with rules, but they don't
seem to scale very successfully to important/massive usage. I tried
them out several times, and it was pretty cool that they worked as
well as they did, but it sure didn't go to production. I'd be
saddened if rules went away because they seemed pretty cool, but it
wouldn't be "practical" disappointment.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

On 11 October 2012 23:28, Josh Berkus <josh@agliodbs.com> wrote:

For 9.3, I suggest we create a DDL trigger by default which prevents
RULEs and throws an ERROR that explains they are now deprecated.

Well, even if we were considering this, the sequence would need to be:

1. Announce in 9.3 that RULES will be going away RSN.
2. In 9.4, send a warning every time someone loads/edits a user-defined
RULE.
3. In 10.0, get rid of CREATE RULE.

With the DDL trigger, we're able to do that faster. The idea is you
can still delete it if you need compatibility, so we get the message
across without an extra release and without an annoying GUC (etc).

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Thu, Oct 11, 2012 at 3:39 PM, Simon Riggs <simon@2ndquadrant.com> wrote:

On 11 October 2012 23:28, Josh Berkus <josh@agliodbs.com> wrote:

For 9.3, I suggest we create a DDL trigger by default which prevents
RULEs and throws an ERROR that explains they are now deprecated.

Well, even if we were considering this, the sequence would need to be:

1. Announce in 9.3 that RULES will be going away RSN.
2. In 9.4, send a warning every time someone loads/edits a user-defined
RULE.
3. In 10.0, get rid of CREATE RULE.

With the DDL trigger, we're able to do that faster. The idea is you
can still delete it if you need compatibility, so we get the message
across without an extra release and without an annoying GUC (etc).

This seems sane to me. The deprecation of standard-conforming strings
was very smooth in our experience, because it seems like practically
every client has adjusted in that long deprecation period. By
contrast, the sudden bytea format change was not nearly so smooth --
it bit a lot of people and we have had to change our configuration to
override Postgres's default (which we are loathe to do) to the old
encoding as our platform-default when vending databases until we can
age out all older libpqs, which is going to take quite some time.

So, apparently, griping continuously in the logs does get the job done.

--
fdr

With the DDL trigger, we're able to do that faster. The idea is you
can still delete it if you need compatibility, so we get the message
across without an extra release and without an annoying GUC (etc).

You're seeing these things as bugs. I see them as features. And we
don't need a GUC if you can't turn the warning off.

I'm also not real keen on the idea that someone could dump a 9.2
database and be unable to load it into 9.3 because of the DDL trigger,
especially if they might not encounter it until halfway through a
restore. That seems rather user-hostile to me.

Also, how would you picture that working with pg_upgrade?

RULEs are a major feature we've had for over a decade. We've discussed
deprecating them on -hackers, but believe it or don't, most of our users
don't read -hackers. We need to warn people, loudly and repeatedly, for
at *least* a year and a half before removing RULEs. So, to expand on my
sequence of events:

1. Figure out how to 100% replace all functionality currently offered by
RULEs (this may already exist, but nobody has accounted it)
2. Announce that RULES will be going away after 9.4 (in 2015).
3. Amend the documentation pages on RULEs with a fat header saying this
is a deprecated feature and going away in 2 versions.
4. Write wiki pages describing how to migrate away from RULEs.
5. In 9.4, send a warning every time someone CREATEs/ALTERs a
user-defined RULE.
6. In 10.0, get rid of CREATE RULE.

Note that steps 1-4 would need to be complete at least a year before
RULEs actually go away ... preferably 2 years. And 100% of the
functionality required to replace RULEs needs to be available at least
one version before RULEs go away, preferably 2 versions.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On Thu, Oct 11, 2012 at 3:59 PM, Josh Berkus <josh@agliodbs.com> wrote:

With the DDL trigger, we're able to do that faster. The idea is you
can still delete it if you need compatibility, so we get the message
across without an extra release and without an annoying GUC (etc).

You're seeing these things as bugs. I see them as features. And we
don't need a GUC if you can't turn the warning off.

I'm also not real keen on the idea that someone could dump a 9.2
database and be unable to load it into 9.3 because of the DDL trigger,
especially if they might not encounter it until halfway through a
restore. That seems rather user-hostile to me.

Also, how would you picture that working with pg_upgrade?

RULEs are a major feature we've had for over a decade. We've discussed
deprecating them on -hackers, but believe it or don't, most of our users
don't read -hackers. We need to warn people, loudly and repeatedly, for
at *least* a year and a half before removing RULEs. So, to expand on my
sequence of events:

1. Figure out how to 100% replace all functionality currently offered by
RULEs (this may already exist, but nobody has accounted it)
2. Announce that RULES will be going away after 9.4 (in 2015).
3. Amend the documentation pages on RULEs with a fat header saying this
is a deprecated feature and going away in 2 versions.
4. Write wiki pages describing how to migrate away from RULEs.
5. In 9.4, send a warning every time someone CREATEs/ALTERs a
user-defined RULE.
6. In 10.0, get rid of CREATE RULE.

I think this more realistic. One other thing I'd like to state is
that one we move into hard-core deprecation mode that the DDL trigger
may not be enough to incite the action we need to smoothly deprecate.
Instead, any *use* of a rule that is not through a view (aka a
non-deprecated feature) should spam you with a nice big warning to
annoy you into taking action. This may be in a release following the
DDL-trigger-warning.

This may sound insane, but it worked for standards conforming strings,
and that seems to have gone reasonably well...at least taken in
comparison to bytea.

--
fdr

On 11 October 2012 20:28, Simon Riggs <simon@2ndquadrant.com> wrote:

Not many RULE-lovers out there, once you've tried to use them.

Allowing RULEs complicates various things and can make security more difficult.

What exactly do they make more difficult? Are you particularly
concerned with the overhead that rules impose when developing certain
types of features? If so, since there's going to be a legacy
compatibility mode for a long time, I don't know that deprecating
rules will buy you much in the next 3 - 4 releases.

For 9.3, I suggest we create a DDL trigger by default which prevents
RULEs and throws an ERROR that explains they are now deprecated.

Anybody that really cares can delete this and use them. Sometime in
future, we hard code it, barring complaints.

Well, rules have been around since the Berkeley days [1]http://db.cs.berkeley.edu/papers/ERL-M89-82.pdf. I don't
think that anyone, including Tom, is willing to argue that
user-defined rules are not a tar-pit (except perhaps ON SELECT DO
INSTEAD SELECT rules - which are exactly equivalent to views anyway).
Personally, I'd like to see them removed too. However, in order to be
able to get behind your proposal, I'd like to see a reasonably
developed cost/benefit analysis. People do use user-defined rules. For
example, the xTuple open source ERP package uses ON INSERT DO INSTEAD
rules [2]http://www.xtuple.org/ApiDevelopment.

[1]: http://db.cs.berkeley.edu/papers/ERL-M89-82.pdf

[2]: http://www.xtuple.org/ApiDevelopment

--
Peter Geoghegan http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services

On 10/11/2012 03:59 PM, Josh Berkus wrote:

I'm also not real keen on the idea that someone could dump a 9.2
database and be unable to load it into 9.3 because of the DDL trigger,
especially if they might not encounter it until halfway through a
restore. That seems rather user-hostile to me.

Also, how would you picture that working with pg_upgrade?

RULEs are a major feature we've had for over a decade.

That nobody in the right mind would use in production for YEARS. That
said there is a very real problem here. For a very, very long time the
recommended way (wrong way in fact) to do partitioning was based on
rules. Now, those in the know immediately said, "WTF" but I bet you that
a lot of people that we don't know about are using rules for partitioning.

We definitely need a warning period that this is going away. That said,
I don't know that we need a whole release cycle. If we start announcing
now (or before the new year) that in 9.3 we will not have rules, that
gives people 9-10 months to deal with the issue and that is assuming
that we are dealing with early adopters, which we aren't because early
adopters are not going to be using rules.

JD

--
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC
@cmdpromptinc - 509-416-6579

On Thu, Oct 11, 2012 at 5:07 PM, Joshua D. Drake <jd@commandprompt.com> wrote:

On 10/11/2012 03:59 PM, Josh Berkus wrote:

I'm also not real keen on the idea that someone could dump a 9.2
database and be unable to load it into 9.3 because of the DDL trigger,
especially if they might not encounter it until halfway through a
restore. That seems rather user-hostile to me.

Also, how would you picture that working with pg_upgrade?

RULEs are a major feature we've had for over a decade.

That nobody in the right mind would use in production for YEARS. That said
there is a very real problem here. For a very, very long time the
recommended way (wrong way in fact) to do partitioning was based on rules.
Now, those in the know immediately said, "WTF" but I bet you that a lot of
people that we don't know about are using rules for partitioning.

We definitely need a warning period that this is going away. That said, I
don't know that we need a whole release cycle. If we start announcing now
(or before the new year) that in 9.3 we will not have rules, that gives
people 9-10 months to deal with the issue and that is assuming that we are
dealing with early adopters, which we aren't because early adopters are not
going to be using rules.

My experience suggests that only ample annoyance for at least one full
release cycle will provide a low-impact switch. This annoyance must
not be able to be turned off.

--
fdr

On 10/11/2012 08:20 PM, Daniel Farina wrote:

On Thu, Oct 11, 2012 at 5:07 PM, Joshua D. Drake <jd@commandprompt.com> wrote:

On 10/11/2012 03:59 PM, Josh Berkus wrote:

I'm also not real keen on the idea that someone could dump a 9.2
database and be unable to load it into 9.3 because of the DDL trigger,
especially if they might not encounter it until halfway through a
restore. That seems rather user-hostile to me.

Also, how would you picture that working with pg_upgrade?

RULEs are a major feature we've had for over a decade.

That nobody in the right mind would use in production for YEARS. That said
there is a very real problem here. For a very, very long time the
recommended way (wrong way in fact) to do partitioning was based on rules.
Now, those in the know immediately said, "WTF" but I bet you that a lot of
people that we don't know about are using rules for partitioning.

We definitely need a warning period that this is going away. That said, I
don't know that we need a whole release cycle. If we start announcing now
(or before the new year) that in 9.3 we will not have rules, that gives
people 9-10 months to deal with the issue and that is assuming that we are
dealing with early adopters, which we aren't because early adopters are not
going to be using rules.

My experience suggests that only ample annoyance for at least one full
release cycle will provide a low-impact switch. This annoyance must
not be able to be turned off.

Spot on. All our experience is that just announcing things, especially
in places other than release notes and similar, is ineffective as a way
of communicating with our user base.

I'm with Tom and Josh and Daniel on this, and to be honest I'm somewhat
surprised at the willingness of some people to spring surprises on
users. I still come across uses of rules in the wild, and not just for
partitioning either. Personally I think if we start now the earliest we
should even consider removing the support is 9.4.

cheers

andrew

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

Tom and Simon wrote:

If you want to get rid of rules, build the
replacement; don't just try to be a pain in the ass to users.

Supporting broken and non-standard features *is* a pain in the ass to
users, since they are sometimes persuaded to use them and then regret
it. Or if they do, hit later problems.

Broken? That's a strong word. Tricky perhaps. Best avoided by novices, yes.
But broken? Do they not work exactly as described in the fine manual?
FWIW, I still see them a lot in the wild.

Anyway, lets start with a discussion of what rules give us
that SQL standard features do not?

Even if the answer is nothing, if we do not implement the SQL standard
feature yet (exhibit A: updateable views), it's a moot point unless
the goal is to spur development of those features just so we can
deprecate rules.

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201210112251
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAlB3hx8ACgkQvJuQZxSWSshhwQCfdtKc7R2i0kz7eDUTXtik93k3
KyEAoK0dQVZsfcAD3OlHYDVhWMjst8QZ
=xY2L
-----END PGP SIGNATURE-----

FWIW I thought the stuff in commit 092d7ded2 might be pretty useful
generally.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

-----Original Message-----
From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers-
owner@postgresql.org] On Behalf Of Andrew Dunstan
Sent: Thursday, October 11, 2012 8:52 PM
To: Daniel Farina
Cc: Joshua D. Drake; Josh Berkus; Simon Riggs;

pgsql-hackers@postgresql.org

Subject: Re: [HACKERS] Deprecating RULES

On 10/11/2012 08:20 PM, Daniel Farina wrote:

On Thu, Oct 11, 2012 at 5:07 PM, Joshua D. Drake

<jd@commandprompt.com> wrote:

On 10/11/2012 03:59 PM, Josh Berkus wrote:

I'm also not real keen on the idea that someone could dump a 9.2
database and be unable to load it into 9.3 because of the DDL
trigger, especially if they might not encounter it until halfway
through a restore. That seems rather user-hostile to me.

Also, how would you picture that working with pg_upgrade?

RULEs are a major feature we've had for over a decade.

That nobody in the right mind would use in production for YEARS. That
said there is a very real problem here. For a very, very long time
the recommended way (wrong way in fact) to do partitioning was based

on rules.

Now, those in the know immediately said, "WTF" but I bet you that a
lot of people that we don't know about are using rules for

partitioning.

We definitely need a warning period that this is going away. That
said, I don't know that we need a whole release cycle. If we start
announcing now (or before the new year) that in 9.3 we will not have
rules, that gives people 9-10 months to deal with the issue and that
is assuming that we are dealing with early adopters, which we aren't
because early adopters are not going to be using rules.

My experience suggests that only ample annoyance for at least one full
release cycle will provide a low-impact switch. This annoyance must
not be able to be turned off.

Spot on. All our experience is that just announcing things, especially in

places

other than release notes and similar, is ineffective as a way of

communicating

with our user base.

I'm with Tom and Josh and Daniel on this, and to be honest I'm somewhat
surprised at the willingness of some people to spring surprises on users.

I still

come across uses of rules in the wild, and not just for partitioning

either.

Personally I think if we start now the earliest we should even consider
removing the support is 9.4.

cheers

andrew

Deprecation means that existing code will no longer work without
refactoring. If CREATE RULE was a security hazard or unstable that may
justify such an action but simply because using it properly (or at least
safely) is difficult doesn't mean that those who have managed should be
punished for their expertise.

Late night rambling here but the "risk mitigation" that we seem to be caring
about is new users searching for and using "algorithms" that they find on
the web without understanding the intricacies of how those algorithms work.
Do we really want to build something into the database to deal with this (by
disallowing it outright) or do we do our best to provide authoritative and
useful documentation so that when users go looking for the "CREATE RULE"
command in our documentation they are provided with reasoning and
alternatives to its use?

RULEs may be difficult but maybe there are some rare use-cases where they
would be appropriate. No one here is all-knowing and just maybe someone in
the future will have an idea and decide to further improve them or at the
least recognize a situation where the current implementation is useful.

So, what actual harms are there to using CREATE RULE and are there less
invasive means, via a more nuanced restriction implementation of CREATE RULE
or simply via documentation, to mitigate those harms? Maybe there would not
be enough benefits to CREATE RULE at this point in time to consider
implementing in from scratch but given that it already exists it should be
worth some effort to keep it functioning even if only for
forward-compatibility reasons. And regardless, the whole "what do you use
instead of CREATE RULE" documentation needs to be created no matter the
eventual decision to fully remove the feature from the system.

David J.

Josh Berkus wrote:

For 9.3, I suggest we create a DDL trigger by default which prevents
RULEs and throws an ERROR that explains they are now deprecated.

Well, even if we were considering this, the sequence would need to be:

1. Announce in 9.3 that RULES will be going away RSN.
2. In 9.4, send a warning every time someone loads/edits a user-defined
RULE.
3. In 10.0, get rid of CREATE RULE.

I think we can easily move up the first 2 steps.

1. Announce right now, effectively in 9.2, that RULES are going away soon.
2. In 9.3, send the warning.

Then optionally 3. In 9.4 can be where CREATE RULE is removed, or stay with 10.0
there and we have a solid 2 years of warnings instead of one.

It seems to me that step 1 is completely outside the release cycle, as it
doesn't involve changing any code. Since 9.2 just came out, we can just do #1
as of 9.2.

The only reason I see to delay #1 is if we aren't sure we're going to go ahead
with it, and give a few months to think about it before announcing this major
thing suddenly. Waiting until 9.3 just to make an announcement is silly.

-- Darren Duncan

On 11 October 2012 23:59, Josh Berkus <josh@agliodbs.com> wrote:

With the DDL trigger, we're able to do that faster. The idea is you
can still delete it if you need compatibility, so we get the message
across without an extra release and without an annoying GUC (etc).

You're seeing these things as bugs. I see them as features. And we
don't need a GUC if you can't turn the warning off.

I'm also not real keen on the idea that someone could dump a 9.2
database and be unable to load it into 9.3 because of the DDL trigger,
especially if they might not encounter it until halfway through a
restore. That seems rather user-hostile to me.

I don't think you're listening, none of those things are problems and
so not user hostile.

I've proposed a trigger that is there by default but which is *removable*.

So you can turn it off, and yet there is no GUC.

Also, how would you picture that working with pg_upgrade?

If RULEs are in use, we automatically delete the trigger.

RULEs are a major feature we've had for over a decade. We've discussed
deprecating them on -hackers, but believe it or don't, most of our users
don't read -hackers. We need to warn people, loudly and repeatedly, for
at *least* a year and a half before removing RULEs.

That is exactly what I proposed.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 12 October 2012 01:52, Andrew Dunstan <andrew@dunslane.net> wrote:

I'm with Tom and Josh and Daniel on this, and to be honest I'm somewhat
surprised at the willingness of some people to spring surprises on users.

I've never caused nor argued in favour of hardcoded changes that catch users.

This would be a documented change and one that is alterable, should
the user wish.

So your comments don't apply.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services