Re: Deprecating RULES

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.

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

Comments?

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

Simon Riggs <simon(at)2ndQuadrant(dot)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(at)sss(dot)pgh(dot)pa(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)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(at)2ndquadrant(dot)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(at)agliodbs(dot)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(at)2ndquadrant(dot)com> wrote:
> On 11 October 2012 23:28, Josh Berkus <josh(at)agliodbs(dot)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(at)agliodbs(dot)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(at)2ndquadrant(dot)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]. 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].

[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(at)commandprompt(dot)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(at)commandprompt(dot)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(at)turnstep(dot)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(at)postgresql(dot)org [mailto:pgsql-hackers-
> owner(at)postgresql(dot)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(at)postgresql(dot)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(at)commandprompt(dot)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(at)agliodbs(dot)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(at)dunslane(dot)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

On 12 October 2012 00:45, Peter Geoghegan <peter(at)2ndquadrant(dot)com> wrote:
> On 11 October 2012 20:28, Simon Riggs <simon(at)2ndquadrant(dot)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?

Specifically with regard to security, they allow transparent
modification of DML in ways that are not visible to people performing
audits on SQL standard conforming databases. The principle of removing
unused features applies here.

>> 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]. 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].
>
> [1] http://db.cs.berkeley.edu/papers/ERL-M89-82.pdf
>
> [2] http://www.xtuple.org/ApiDevelopment

AFAICS all RULEs can be re-expressed as Triggers or Views.

Perhaps the right way to do this is to supply a package that allows
appropriate Triggers to be generated from Rule definitions, allowing
us to cope with the few uses out there in the wild. That is more work
and frankly, I don't object to people who use rules, I just object to
new people being told they are useful when they aren't.

As regards cost/benefit analysis, this is a low importance feature,
but then that is why I proposed a low effort fix that is flexible to
the needs of users affected.

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

On 10/12/2012 05:47 AM, David Johnston wrote:
>> -----Original Message-----
>> From: pgsql-hackers-owner(at)postgresql(dot)org [mailto:pgsql-hackers-
>> owner(at)postgresql(dot)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(at)postgresql(dot)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(at)commandprompt(dot)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.
I have missed SELECT rules on real tables a few times, but I do not
remember that there was much outcry when they were disabled.

On the other hand, I do not miss them much, as my experience with
rules in general is that they are brittle - even if
you can get them to work for your current use, there is no guarantee
that they keep working after changes to queries or table structure.

So restricting the SELECT rules to what can be defined in CREATE VIEW
seems a good compromise.

You can (in theory) train yourself in proper use of foot-guns, but you
can never be absolutely sure that you have reached the appropriate
level for using them in real-life situations where a broken foot is not
desirable.

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

On 10/12/2012 08:47 AM, Simon Riggs wrote:
> On 12 October 2012 01:52, Andrew Dunstan <andrew(at)dunslane(dot)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.
>
Nah! You already surprised Andrew by proposing to remove rules ;)

--------------------
Hannu Krosing

On Thu, Oct 11, 2012 at 11:55 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> As regards cost/benefit analysis, this is a low importance feature,
> but then that is why I proposed a low effort fix that is flexible to
> the needs of users affected.

Is there any feature that is more loathed and more narrowly used than
rules? What about hash indexes? It is suspicious if we cannot
identify even one feature that is more pain than gain to target for
removal.

In any case, I think it's important to keep an open mind to planning
deprecations, and I say that as someone who is directly and hugely
negatively impacted by deprecated features -- however, it is important
to do them slowly. I think the choice of rules was a pretty credible
one to bring up for consideration.

The most sensible conservative deprecation plan I can think of sense
is to only remove the feature when no release under project support
claims to support -- without deprecation warning -- that feature. So,
all in all, a four year deprecation window. I think this makes sense
for features that are not in urgent need of deprecation but chip away
at time spent serving defects or making them work with more desirable
features. Because of this long pipeline in ideal cases, there is some
benefit to starting in advance before everyone gets fed up and wants
it removed Real Soon.

--
fdr

On Friday, October 12, 2012 01:45:56 AM Peter Geoghegan wrote:
> On 11 October 2012 20:28, Simon Riggs <simon(at)2ndquadrant(dot)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]. 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].
> [2] http://www.xtuple.org/ApiDevelopment
And *drumroll*, they are broken. Every file in
https://postbooks.svn.sourceforge.net/svnroot/postbooks/xtupleserver/trunk/dbscripts/api/views/
seems to have multiple evaluation dangers in the rules.

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

On Thu, Oct 11, 2012 at 05:20:14PM -0700, Daniel Farina wrote:
> On Thu, Oct 11, 2012 at 5:07 PM, Joshua D. Drake <jd(at)commandprompt(dot)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.

As a reminder, standard_conforming_strings and escape_string_warning
were added in PG 8.1 (2005-11-08), and we defaulted
standard_conforming_strings to 'on' in PG 9.1 (2011-09-12). The reason
it took six years to change the default to treat backslashes literally
is because the change could produce incorrect results. In the RULE
case, it will just cause an error when the rule is created, and
hopefully someone will see that. However, there will be no error when
someone issues a query and the rule is _missing_ because it threw an
error on rule creation.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

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

Having an upgrade fail for mysterious reasons with a cryptic error
message the user doesn't understand isn't user-hostile? Wow, you must
have a very understanding group of users.

Lemme try to make it clear to you exactly how user-hostile you're being:

1. User downloads 9.2 today.
2. User builds a new application.
3. User finds the doc page on RULEs, decides they're a nifty concept.
4. New application includes some RULEs.
5. 9.3 comes out.
6. User schedules a downtime for upgrading.
7. In the middle of the upgrade, at 2am, they get a cryptic warning, and
dump/restore fails.
8. User has to rollback the upgrade.
9. User googles a bunch, eventually finds information on the trigger.
10. User realizes that a bunch of their code, written not 6 months
before, needs to be refactored now.
11. User switches to MongoDB in disgust.

I realize you weren't around when we removed row OIDs, but I was *still*
getting flack from that in 2008. And we lost entire OSS projects to
other databases because of removing row OIDs. And those were marked
deprecated for 3 years before we removed them.

> That is exactly what I proposed.

No, it's not. You proposed inserting a SURPRISE! break-your-application
trigger in 9.3 ... 10 months from now. With zero warning to our
general user base.

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

On 10/12/2012 06:59 PM, Josh Berkus wrote:
>> I don't think you're listening, none of those things are problems and
>> so not user hostile.
> Having an upgrade fail for mysterious reasons with a cryptic error
> message the user doesn't understand isn't user-hostile? Wow, you must
> have a very understanding group of users.
>
> Lemme try to make it clear to you exactly how user-hostile you're being:
>
> 1. User downloads 9.2 today.
> 2. User builds a new application.
> 3. User finds the doc page on RULEs, decides they're a nifty concept.
> 4. New application includes some RULEs.
> 5. 9.3 comes out.
> 6. User schedules a downtime for upgrading.
> 7. In the middle of the upgrade, at 2am, they get a cryptic warning, and
> dump/restore fails.
> 8. User has to rollback the upgrade.
> 9. User googles a bunch, eventually finds information on the trigger.
> 10. User realizes that a bunch of their code, written not 6 months
> before, needs to be refactored now.
> 11. User switches to MongoDB in disgust.
Perhaps more likely p11. is "User starts drinking and gets a divorce.
His dog dies as a result."

More seriously, if it was something that is easier to do in MongoDB,
the user _should_ switch. And MongoDB does not have RULEs.

I can't think of anything using RULES that would make PostgreSQL
behave like MongoDB.

It can be done using json/htree/xml + pl/jsv8 (or any other pl), but not
RULES.

> I realize you weren't around when we removed row OIDs, but I was *still*
> getting flack from that in 2008. And we lost entire OSS projects to
> other databases because of removing row OIDs.
I'm sure we also lost "entire projects" to other databases _because_

_of_ having row OIDs.

> And those were marked deprecated for 3 years before we removed them.
>
>> That is exactly what I proposed.
> No, it's not. You proposed inserting a SURPRISE! break-your-application
> trigger in 9.3 ... 10 months from now. With zero warning to our
> general user base.
Daniel hinted at a better approach - use a trigger which rewrites all
rules to send a nagging notice at every use of the rule in addition
to what they do originally.

On Fri, Oct 12, 2012 at 7:55 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> AFAICS all RULEs can be re-expressed as Triggers or Views.

This is a bizarre discussion. Firstly this isn't even close to true.
The whole source of people's discontentment is that triggers are *not*
equivalent to rules. If they were then they wouldn't be so upset.

Secondly the only reason views work is because they're implemented
using rules. If you want to do anything similar but different from
views you would need to use rules as well. I'm still waiting on
updateable views for example.

It sounds like what people are really looking for is to move the
section of the manual describing rules to an "internals" section of
the manual and add a note saying "do not try to use rules to implement
triggers. they are not triggers" that explains how they're different
and what they're useful for.

In general user manuals, especially ones written like Unix man pages,
tend to describe what things do without explaining why that might be
useful. That's leaves users faced with a decision between trying
similar-sounding features like rules and triggers and they might pick
the wrong one. The Postgres manual is better than most in this respect
but this is one area where it might pay to be extra clear.

--
greg

Josh Berkus wrote:
>> I don't think you're listening, none of those things are problems and
>> so not user hostile.
>
> Having an upgrade fail for mysterious reasons with a cryptic error
> message the user doesn't understand isn't user-hostile? Wow, you must
> have a very understanding group of users.
>
> Lemme try to make it clear to you exactly how user-hostile you're being:
>
> 1. User downloads 9.2 today.
> 2. User builds a new application.
> 3. User finds the doc page on RULEs, decides they're a nifty concept.
> 4. New application includes some RULEs.
<snip>

I have a proposal.

Assuming we decide to do away with RULEs, change the *documentation* for RULEs
right away in all supported maintenance branches (including 9.2), saying that
RULEs will be deprecated, but don't change any code / add any warnings until 9.3.

Then, no later than the next bug/security fix minor release, 9.2.2/etc, the
documentation for RULEs all says that, yes we have RULEs, but you shouldn't use
them on any new projects as they are going away, and you should migrate any
existing uses, and uses will warn starting in 9.3.0. This documentation change
can also be highlighted in a bullet point in the 9.2.2/etc release
announcements. If necessary, also make reference in the docs to some tool or
procedure to help find any uses of RULEs and help with the migration.

Since this isn't a code change, it should be very conservative and be safe to
include in maintenance branches, and it will alert users right where they're
most likely to look, the RULEs documentation, without any undue delay.

How does that sound?

-- Darren Duncan

On 10/12/12 11:57 AM, Darren Duncan wrote:
>
> Assuming we decide to do away with RULEs, change the *documentation* for
> RULEs right away in all supported maintenance branches (including 9.2),
> saying that RULEs will be deprecated, but don't change any code / add
> any warnings until 9.3.

I'd say back to the 8.3 docs, actually, but yes, that's the right idea.

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

On Thu, Oct 11, 2012 at 8:52 PM, Andrew Dunstan <andrew(at)dunslane(dot)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
> 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.

Yeah. Actually, I think even that is far too soon. Frankly, the fact
that several people here seem to think rules are still something they
see regularly in the field makes me wonder if we should be
entertaining this proposition at all ... but if we are, what I think
we should do first is add a warning to the documentation that says
"don't use rules". And then after, say, two releases, we could have
the CREATE RULE command throw a warning. And then after, say, two
more releases, we could have it fail with an error saying, dude, not
supported any more. That means we would start to warn no earlier than
9.5 and actually shut it off no earlier than 9.7.

The case of standard_conforming_strings has already been discussed as
a parallel. I think the case of => should also be mentioned. The SQL
standards committee has standardized this as a way of calling
functions with named arguments, but we have long allowed it as an
operator - and in fact hstore has long shipped an operator with that
name. We began warning about the use of that operator name in 9.0,
and we removed the version that ships with hstore in 9.2. I can't
imagine we'll actually implement the SQL standard behavior any sooner
than 9.4.

The thing we've got to keep in mind here is that many people upgrade
more than one release at a time. We regularly have customers who will
upgrade from, say, 8.2 or 8.3 all the way up to 9.1 or 9.2. Now, we
can't completely cater to people who are on that kind of very long
time scale or we'll never get anywhere, but cutting out a feature that
isn't even deprecated today in less than a year is going to the
opposite end of the spectrum. We know that rules are a bad fit for
almost everything, *but we can't assume that our users all know that
when it isn't even documented*.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 12 October 2012 20:01, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> We know that rules are a bad fit for
> almost everything, *but we can't assume that our users all know that
> when it isn't even documented*.

I agree that we should document that rules are something that should
almost certainly be avoided. Up until recently, the advice surrounding
commit_delay in the docs said something practically indistinguishable
from "you don't want to use this". Why not do the same for rules?

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

On Fri, Oct 12, 2012 at 3:10 PM, Peter Geoghegan <peter(at)2ndquadrant(dot)com> wrote:
> On 12 October 2012 20:01, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> We know that rules are a bad fit for
>> almost everything, *but we can't assume that our users all know that
>> when it isn't even documented*.
>
> I agree that we should document that rules are something that should
> almost certainly be avoided. Up until recently, the advice surrounding
> commit_delay in the docs said something practically indistinguishable
> from "you don't want to use this". Why not do the same for rules?

I completely agree.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 10/12/2012 08:48 PM, Greg Stark wrote:
> On Fri, Oct 12, 2012 at 7:55 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> AFAICS all RULEs can be re-expressed as Triggers or Views.
> This is a bizarre discussion. Firstly this isn't even close to true.
> The whole source of people's discontentment is that triggers are *not*
> equivalent to rules. If they were then they wouldn't be so upset.
>
> Secondly the only reason views work is because they're implemented
> using rules.
Nobody is discussing deprecating VIEWs.

And SELECT rules that are the basis of VIEWs are deprecated
from being an independent user-visible feature for quite some
time already
> If you want to do anything similar but different from
> views you would need to use rules as well. I'm still waiting on
> updateable views for example.
You CAN do these using triggers, that is the main reason we
have INSTEAD triggers.
> It sounds like what people are really looking for is to move the
> section of the manual describing rules to an "internals" section of
> the manual and add a note saying "do not try to use rules to implement
> triggers. they are not triggers" that explains how they're different
> and what they're useful for.
Moving them to internals _and_ adding a note to not use them
directly for any user code seems like a good plan.

And replacing the original RULES page with suggestion to look
under internals.
> In general user manuals, especially ones written like Unix man pages,
> tend to describe what things do without explaining why that might be
> useful. That's leaves users faced with a decision between trying
> similar-sounding features like rules and triggers and they might pick
> the wrong one. The Postgres manual is better than most in this respect
> but this is one area where it might pay to be extra clear.
>
-------------------
Hannu Krosing

On 12 October 2012 17:59, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>
>> I don't think you're listening, none of those things are problems and
>> so not user hostile.
>
> Having an upgrade fail for mysterious reasons with a cryptic error
> message the user doesn't understand isn't user-hostile? Wow, you must
> have a very understanding group of users.

I think its sad we can't even attempt a technical conversation without
you making snide ad hominem attacks that aren't even close to being
true on a personal level, nor accurate in a technical sense.

Your argument applies to any and all deprecations, not just the one
proposed. And it still applies to the deprecation schedule you
outlined, since upgrades aren't always across consecutive releases.
I'm the wrong person to lecture about upgrade incompatibility problems
since I've spoken out against them for years.

This style of debate makes it impossible to get new ideas across and
holds us all back.

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

Simon,

> I think its sad we can't even attempt a technical conversation
> without
> you making snide ad hominem attacks that aren't even close to being
> true on a personal level, nor accurate in a technical sense.

I would prefer it if you actually addressed my substantive arguments, which, so far, you haven't.

--Josh Berkus

On 12 October 2012 19:48, Greg Stark <stark(at)mit(dot)edu> wrote:
> On Fri, Oct 12, 2012 at 7:55 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> AFAICS all RULEs can be re-expressed as Triggers or Views.
>
> This is a bizarre discussion. Firstly this isn't even close to true.
> The whole source of people's discontentment is that triggers are *not*
> equivalent to rules. If they were then they wouldn't be so upset.

This may be a confusion on the point of equivalence; clearly the
features work differently.

I'm not aware of any rule that can't be rewritten as a trigger or a
view. Please can anyone show me some examples of those?

Assuming examples exist, do we think that is wide enough to be
considered a useful feature, given the other downsides of rules such
as not abiding by COPY - which causes data corruption for those who
thought rules would always be obeyed.

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

On 13 October 2012 21:15, Joshua Berkus <josh(at)agliodbs(dot)com> wrote:
> Simon,
>
>> I think its sad we can't even attempt a technical conversation
>> without
>> you making snide ad hominem attacks that aren't even close to being
>> true on a personal level, nor accurate in a technical sense.
>
> I would prefer it if you actually addressed my substantive arguments, which, so far, you haven't.

I would prefer it if you stuck to your substantive arguments, after
reading and understanding others points, which so far, you haven't. So
lets now try and stick to technical points.

Your substantive argument, as I understand it, is that deprecating
something can cause user annoyance at upgrade. I agree with that
point, as I would expect everybody to do so.

You also mention that 3 years wasn't long enough for some people, but
I am unsure as to your point there. It might be that we should take
longer than 3 years to deprecate things, or that the same pain will be
felt however long we leave it. I think the latter, since the standard
conforming strings change didn't go much better even though we took
ages to do it.

In many people's opinion, RULEs are a strangeness that are seldom used
in production and long since should have been removed. Peter shows a
paper that details things wrong with them from 15 years ago.
Deprecating rules is a much, much smaller change than any of the
recent deprecations. Everything else we say needs to have that
context.

You also mention that we must make noise for at least 18 months before
making any change, to avoid race conditions where new users adopt
RULEs and are then surprised when we deprecate them. My answer to that
is that rules are pretty useless and any reasonable developer will
discover that before putting anything live. If they do put it live,
they might not have noticed rules are actually broken, so deprecating
rules in this way will actually avoid bugs and data corruption for
those people. For me, most developers either 1) use ORMs, none of
which use RULEs, 2) speak to people in PostgreSQL community/consulting
companies - almost all of whom will advise to avoid RULEs or 3) have
read books that advise against their use or 4) read that rules are not
SQL standard and so wisely avoid unnecessarily non-standard coding. As
a result, I think the number of people likely to adopt rules in the
near future is approximately zero and the number affected by this
change will be very low and unlikely to cause embarrassment for us.

IMHO the risk of losing people to other databases seems higher from
providing broken features than it does from removing broken features,
which seems like useful and proactive management. Since I believe
there is something to be lost from maintaining the status quo, and
little to be gained from delay, I proposed a way of speeding up the
process that allowed a back out plan.

Daniel has made the point that we must enforce deprecation without any
option of reversion. Given neither of us likes to be hostile to users,
I presume we both disagree with him on that? One thing I would like to
avoid is providing another GUC for compatibility, since the
combinatorial explosion of GUC settings introduces considerable bug
risks.

Having said that, I've got no particular reason to hurry other than my
own personal embarrassment at explaining that, yes, some of our
features are broken. But I would like to see actions begin, however
long the timescale.

If we wish to make some noise, I think docs changes are not enough.
Darren's suggestion that doc additions that explain that advice has
been backpatched is useful, but we should also make Announcements of
impending deprecations as well if we truly wish to make noise. And if
we do that, as Daniel points out, sorting out hash indexes at the same
time also makes sense.

Where rules do exist it seems possible to write simple code to
transform them into triggers or views. I'll write some docs to
explain.

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

Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> On 12 October 2012 19:48, Greg Stark <stark(at)mit(dot)edu> wrote:
>> On Fri, Oct 12, 2012 at 7:55 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>>> AFAICS all RULEs can be re-expressed as Triggers or Views.

>> This is a bizarre discussion. Firstly this isn't even close to true.
>> The whole source of people's discontentment is that triggers are *not*
>> equivalent to rules. If they were then they wouldn't be so upset.

> I'm not aware of any rule that can't be rewritten as a trigger or a
> view. Please can anyone show me some examples of those?

Sorry, you're thinking you can put the burden of proof on other people,
but this doesn't work like that. If you want to deprecate rules on the
grounds that triggers are an adequate substitute, it's up to you to
prove that claim, not for other people to disprove it.

Personally, I don't think I believe it, on both practical and
theoretical grounds:

* Triggers have been available for a very long time, and have been
documented as the better choice for quite a while, but people still try
to use rules. Whether it's for (admittedly mostly illusory) ease of use
or some other reason, there's some attraction there that we need to
match, not just decree that people can't have it anymore.

* Triggers necessarily operate on a row-at-a-time basis. In theory,
for at least some bulk operations, a rule could greatly outperform
a trigger. It's difficult to walk away from that - unless somebody
can prove that the advantage doesn't ever accrue in practice. But
the fact that a rule won't work in some cases doesn't mean it won't
work for any practical use-case.

* AFTER triggers fall over on sufficiently large updates, since we still
lack any method for spilling the trigger event queue to disk when it
gets too big. It's fairly difficult to tell people they have to use
triggers as long as that limitation exists and rules don't have it.
(I wonder if this could be alleviated with some sort of "immediate
after" trigger mode that fires after the event is frozen, but without
queueing...)

regards, tom lane

On Oct 14, 2012, at 12:35 PM, Tom Lane wrote:

> Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
>> On 12 October 2012 19:48, Greg Stark <stark(at)mit(dot)edu> wrote:
>>> On Fri, Oct 12, 2012 at 7:55 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>>>> AFAICS all RULEs can be re-expressed as Triggers or Views.
>
>>> This is a bizarre discussion. Firstly this isn't even close to true.
>>> The whole source of people's discontentment is that triggers are *not*
>>> equivalent to rules. If they were then they wouldn't be so upset.
>
>> I'm not aware of any rule that can't be rewritten as a trigger or a
>> view. Please can anyone show me some examples of those?
>
> Sorry, you're thinking you can put the burden of proof on other people,
> but this doesn't work like that. If you want to deprecate rules on the
> grounds that triggers are an adequate substitute, it's up to you to
> prove that claim, not for other people to disprove it.

It seems there are two somewhat separate issues for discussion, one is the question of what to do about rules, the second is deprecation policy in general. Having worked for major software vendors, this are is always a headache. Consider the Microsoft, one of the more powerful software vendors of the PC era, is still trying to get people to upgrade to IE6, but is facing the obstacle of businesses refraining because internally written applications. The discussions around rules and hash indexes going on concurrently on this list share features which would benefit from having a general policy discussion abstracted from the attachments or dislikes of particular features.

I would suggest that a thread be spawned off to consider deprecation policy, including substantive reasons for deprecation, the burden of proof on those proposing deprecation, means of communicating to users. This will cause some thrash up front, but will go a long way to triaging deprecation discussions, and having a work flow in place for when such decisions are made.

On 14 October 2012 17:35, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
>> On 12 October 2012 19:48, Greg Stark <stark(at)mit(dot)edu> wrote:
>>> On Fri, Oct 12, 2012 at 7:55 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>>>> AFAICS all RULEs can be re-expressed as Triggers or Views.
>
>>> This is a bizarre discussion. Firstly this isn't even close to true.
>>> The whole source of people's discontentment is that triggers are *not*
>>> equivalent to rules. If they were then they wouldn't be so upset.
>
>> I'm not aware of any rule that can't be rewritten as a trigger or a
>> view. Please can anyone show me some examples of those?
>
> Sorry, you're thinking you can put the burden of proof on other people,
> but this doesn't work like that. If you want to deprecate rules on the
> grounds that triggers are an adequate substitute, it's up to you to
> prove that claim, not for other people to disprove it.

It's fair comment that if one thing can replace another, the burden of
proof is on the claimant.

But I'm not just saying rules should be deprecated because they are
superfluous. Rules are broken in various ways that can cause data
corruption, security issues and user unhappiness. Those downsides
outweigh the few possible advantages.

We have no theoretical model that describes how they should behave and
it would seem nobody cares. The reality is that rules aren't going to
be fixed and I think we should admit that and put rules to rest.
Stonebraker has moved on, and so should we, from his bad ideas.

> Personally, I don't think I believe it, on both practical and
> theoretical grounds:
>
> * Triggers have been available for a very long time, and have been
> documented as the better choice for quite a while, but people still try
> to use rules. Whether it's for (admittedly mostly illusory) ease of use
> or some other reason, there's some attraction there that we need to
> match, not just decree that people can't have it anymore.

I agree there is a huge initial attraction, just an equally huge
disappointment later. I used to quite like them myself, once.

> * Triggers necessarily operate on a row-at-a-time basis. In theory,
> for at least some bulk operations, a rule could greatly outperform
> a trigger. It's difficult to walk away from that - unless somebody
> can prove that the advantage doesn't ever accrue in practice. But
> the fact that a rule won't work in some cases doesn't mean it won't
> work for any practical use-case.

The absence of COPY support makes any use case of rules moot for bulk
data cases, IMHO.

It's possible rules are good for something and almost impossible for
me to prove that's not true.

> * AFTER triggers fall over on sufficiently large updates, since we still
> lack any method for spilling the trigger event queue to disk when it
> gets too big. It's fairly difficult to tell people they have to use
> triggers as long as that limitation exists and rules don't have it.
> (I wonder if this could be alleviated with some sort of "immediate
> after" trigger mode that fires after the event is frozen, but without
> queueing...)

I think we need that mode for RI anyway, to allow us to optimize cases
that repeatedly perform the same action, such as fk locks against a
small reference table. Not sure if that is instead of or as well as
scroll to disk.

I don't think rules solve those problems well enough to be a genuine
substitute. I think people just turn RI off and run checks offline.

Why say this all now? No reason, apart from never seems to be the
wrong answer. Apart from docs, I'll desist.

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

On Sun, Oct 14, 2012 at 9:30 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On 12 October 2012 19:48, Greg Stark <stark(at)mit(dot)edu> wrote:
>> On Fri, Oct 12, 2012 at 7:55 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>>> AFAICS all RULEs can be re-expressed as Triggers or Views.
>>
>> This is a bizarre discussion. Firstly this isn't even close to true.
>> The whole source of people's discontentment is that triggers are *not*
>> equivalent to rules. If they were then they wouldn't be so upset.
>
> This may be a confusion on the point of equivalence; clearly the
> features work differently.
>
> I'm not aware of any rule that can't be rewritten as a trigger or a
> view. Please can anyone show me some examples of those?

Huh? The one thing we currently use rules for, implementing views,
couldn't be done in triggers. In general if your source table is empty
then there's *nothing* you could cause to happen with triggers because
no triggers will fire.

The analogy to this discussion would be something like "users get
confused by macros in C and usually what they're trying to do can be
better done with functions. now that we have functions we should
deprecate macros" All of the preconditions in that sentence are true
but it doesn't follow because macros exist for a reason.

In fact it's not a very good analogy because the situation is
*precisely* the same -- rules *are* macros and manipulate the raw sql
before it's run and the reason they can't be replaced by triggers is
because, like functions, triggers happen after the code is compiled
and run.

--
greg

> -----Original Message-----
> From: pgsql-hackers-owner(at)postgresql(dot)org [mailto:pgsql-hackers-
> owner(at)postgresql(dot)org] On Behalf Of Simon Riggs
> Sent: Sunday, October 14, 2012 5:30 PM
> To: Tom Lane
> Cc: Greg Stark; Peter Geoghegan; PostgreSQL-development
> Subject: Re: [HACKERS] Deprecating RULES
>
> On 14 October 2012 17:35, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> >> On 12 October 2012 19:48, Greg Stark <stark(at)mit(dot)edu> wrote:
> >>> On Fri, Oct 12, 2012 at 7:55 AM, Simon Riggs <simon(at)2ndquadrant(dot)com>
> wrote:
> >>>> AFAICS all RULEs can be re-expressed as Triggers or Views.
> >
> >>> This is a bizarre discussion. Firstly this isn't even close to true.
> >>> The whole source of people's discontentment is that triggers are
> >>> *not* equivalent to rules. If they were then they wouldn't be so
upset.
> >
> >> I'm not aware of any rule that can't be rewritten as a trigger or a
> >> view. Please can anyone show me some examples of those?
> >
> > Sorry, you're thinking you can put the burden of proof on other
> > people, but this doesn't work like that. If you want to deprecate
> > rules on the grounds that triggers are an adequate substitute, it's up
> > to you to prove that claim, not for other people to disprove it.
>
> It's fair comment that if one thing can replace another, the burden of
proof is
> on the claimant.
>
> But I'm not just saying rules should be deprecated because they are
> superfluous. Rules are broken in various ways that can cause data
corruption,
> security issues and user unhappiness. Those downsides outweigh the few
> possible advantages.

If we rely on people that dislike RULEs to provide examples of where RULEs
necessary then through simple ignorance we are likely to deduce that RULEs
are of no practical use. People who use rules because there are no viable
alternatives need to elaborate on the how and why RULEs are used so that the
ignorant can become educated and maybe even provide current or future
alternatives to using RULEs.

It does seem that considerable documentation work needs to be done
regardless of whether a final decision to deprecate is made. Furthermore,
the results of that documentation process will provide valuable insight
toward that decision. This thread should probably end and a new one
discussing the design and content of said documentation should probably be
started. As I do not have a concrete idea of how that process would look
with respect to this community I'll leave the initial posting to someone
with more intimate knowledge of the community dynamics.

David J.

Simon,

* Simon Riggs (simon(at)2ndQuadrant(dot)com) wrote:
> You also mention that 3 years wasn't long enough for some people, but
> I am unsure as to your point there. It might be that we should take
> longer than 3 years to deprecate things, or that the same pain will be
> felt however long we leave it. I think the latter, since the standard
> conforming strings change didn't go much better even though we took
> ages to do it.

RULEs, being what they are, deserve at least 3 years, imo.

> In many people's opinion, RULEs are a strangeness that are seldom used
> in production and long since should have been removed. Peter shows a
> paper that details things wrong with them from 15 years ago.

Indeed. Unfortunately, our documentation doesn't reflect that (yet).

> Deprecating rules is a much, much smaller change than any of the
> recent deprecations. Everything else we say needs to have that
> context.

It's smaller. I don't agree that it's "much, much smaller".

> My answer to that
> is that rules are pretty useless and any reasonable developer will
> discover that before putting anything live.

To be honest, I don't believe we would be having this discussion were
your statement above accurate. RULEs are used quite a bit 'in the
wild', as it were, particularly to address our lack of proper
partitioning.

> If they do put it live,
> they might not have noticed rules are actually broken, so deprecating
> rules in this way will actually avoid bugs and data corruption for
> those people.

Your proposal was to explicitly break RULEs for them on an upgrade,
wasn't it..? Or did you propose something else? Regardless of *how*
that breakage happens, I do not believe our users would appreciate RULEs
breaking without sufficient notice for them to do something about it. I
understand your suggestion that they could simply remove the breakage,
but I do not agree that it is sufficient for them. Either they will do
it as a matter of course during the upgrade and promptly forget about
it, or they'll decide that they need to fix it in a very tight timeframe
leading up to their upgrade (after they discover it in testing)- neither
is good.

> For me, most developers either 1) use ORMs, none of
> which use RULEs, 2) speak to people in PostgreSQL community/consulting
> companies - almost all of whom will advise to avoid RULEs or 3) have
> read books that advise against their use or 4) read that rules are not
> SQL standard and so wisely avoid unnecessarily non-standard coding. As
> a result, I think the number of people likely to adopt rules in the
> near future is approximately zero and the number affected by this
> change will be very low and unlikely to cause embarrassment for us.

I completely disagree with this while our documentation talks about it,
describes it as a user feature, and even encourages use of RULEs for
partitions. Indeed, changing the documentation would be the correct
first step to deprecating RULEs.

> IMHO the risk of losing people to other databases seems higher from
> providing broken features than it does from removing broken features,
> which seems like useful and proactive management. Since I believe
> there is something to be lost from maintaining the status quo, and
> little to be gained from delay, I proposed a way of speeding up the
> process that allowed a back out plan.

Personally, I don't believe your plan is sufficient with regard to
giving users time to move off of RULEs. I don't disagree that we need
to get rid of them as a user-visible/encouraged feature.

> Daniel has made the point that we must enforce deprecation without any
> option of reversion. Given neither of us likes to be hostile to users,
> I presume we both disagree with him on that? One thing I would like to
> avoid is providing another GUC for compatibility, since the
> combinatorial explosion of GUC settings introduces considerable bug
> risks.

I agree that we can't simply disable them in the next release. My
suggestion would be along the lines of: updating our documentation,
issuing a warning when they're used in our next major release, make them
only something a superuser can create, eventually make them unable for
anyone to create.

> Having said that, I've got no particular reason to hurry other than my
> own personal embarrassment at explaining that, yes, some of our
> features are broken. But I would like to see actions begin, however
> long the timescale.

Let's, please, start with a communication plan that is initiatied by
updating our documentation and making an announcement to -announce
regarding the planned deprecation of RULEs.

> If we wish to make some noise, I think docs changes are not enough.
> Darren's suggestion that doc additions that explain that advice has
> been backpatched is useful, but we should also make Announcements of
> impending deprecations as well if we truly wish to make noise. And if
> we do that, as Daniel points out, sorting out hash indexes at the same
> time also makes sense.

Wrote the above before reading this, so- agreed.

> Where rules do exist it seems possible to write simple code to
> transform them into triggers or views. I'll write some docs to
> explain.

That would be fantastic and would be an execellent resource to refer to
in the announcment and the updated documentation... :)

Thanks!

Stephen

On 15 October 2012 00:30, Greg Stark <stark(at)mit(dot)edu> wrote:
> On Sun, Oct 14, 2012 at 9:30 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> On 12 October 2012 19:48, Greg Stark <stark(at)mit(dot)edu> wrote:
>>> On Fri, Oct 12, 2012 at 7:55 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>>>> AFAICS all RULEs can be re-expressed as Triggers or Views.
>>>
>>> This is a bizarre discussion. Firstly this isn't even close to true.
>>> The whole source of people's discontentment is that triggers are *not*
>>> equivalent to rules. If they were then they wouldn't be so upset.
>>
>> This may be a confusion on the point of equivalence; clearly the
>> features work differently.
>>
>> I'm not aware of any rule that can't be rewritten as a trigger or a
>> view. Please can anyone show me some examples of those?
>
> Huh? The one thing we currently use rules for, implementing views,
> couldn't be done in triggers.

Yes, obviously....

> In general if your source table is empty
> then there's *nothing* you could cause to happen with triggers because
> no triggers will fire.

Before statement triggers will fire.

Please can anyone show me the SQL for a rule that cannot be written as
a view or a trigger? I do not believe such a thing exists and I will
provide free beer to the first person that can prove me wrong.

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

On Mon, Oct 15, 2012 at 8:00 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> Please can anyone show me the SQL for a rule that cannot be written as
> a view or a trigger? I do not believe such a thing exists and I will
> provide free beer to the first person that can prove me wrong.

Being written as a view doesn't help you because views use rules. I
repeat, the very fact that we need rules to implement views prove
rules are necessary for some purposes.

--
greg

On 15 October 2012 11:41, Greg Stark <stark(at)mit(dot)edu> wrote:
> On Mon, Oct 15, 2012 at 8:00 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> Please can anyone show me the SQL for a rule that cannot be written as
>> a view or a trigger? I do not believe such a thing exists and I will
>> provide free beer to the first person that can prove me wrong.
>
> Being written as a view doesn't help you because views use rules. I
> repeat, the very fact that we need rules to implement views prove
> rules are necessary for some purposes.

Well, the usual way that this proposal is phrased is that user-defined
rules should be deprecated. Granted, that wasn't the case on this
occasion, but it has been on many other occasions.

It's not as if we there isn't a clear separation between what we all
agree are "good rules" (that is, ON SELECT DO
INSTEAD SELECT rules, which views are technically very simple wrappers
of) and "bad rules" (that is, everything else).

Humorous aside: I saw this comment within view.c, that dates from the
Postgres95 days at the latest (but is probably older still):

* This update consists of adding two new entries IN THE BEGINNING
* of the range table (otherwise the rule system will die a slow,
* horrible and painful death, and we do not want that now, do we?)

I'm not sure that the authors' remarks about not wanting that should
be taken at face value...

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

On 15 October 2012 11:41, Greg Stark <stark(at)mit(dot)edu> wrote:
> On Mon, Oct 15, 2012 at 8:00 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> Please can anyone show me the SQL for a rule that cannot be written as
>> a view or a trigger? I do not believe such a thing exists and I will
>> provide free beer to the first person that can prove me wrong.
>
> Being written as a view doesn't help you because views use rules. I
> repeat, the very fact that we need rules to implement views prove
> rules are necessary for some purposes.

No, it just means there is some aspect of similar underlying infrastructure.

Denial of free beer looks like proof to me...

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

On Monday, October 15, 2012 03:07:21 PM Simon Riggs wrote:
> On 15 October 2012 11:41, Greg Stark <stark(at)mit(dot)edu> wrote:
> > On Mon, Oct 15, 2012 at 8:00 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> >> Please can anyone show me the SQL for a rule that cannot be written as
> >> a view or a trigger? I do not believe such a thing exists and I will
> >> provide free beer to the first person that can prove me wrong.
> >
> > Being written as a view doesn't help you because views use rules. I
> > repeat, the very fact that we need rules to implement views prove
> > rules are necessary for some purposes.
>
> No, it just means there is some aspect of similar underlying
> infrastructure.
>
> Denial of free beer looks like proof to me...

Well, didn't Tom already mention AFTER ... FOR EACH ROW triggers being
problematic because of the in-memory queue?

Greetings,

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

On 15 October 2012 00:30, Greg Stark <stark(at)mit(dot)edu> wrote:
> In fact it's not a very good analogy because the situation is
> *precisely* the same -- rules *are* macros and manipulate the raw sql
> before it's run and the reason they can't be replaced by triggers is
> because, like functions, triggers happen after the code is compiled
> and run.

I quite like this analogy, because it nicely illustrates the problems
with rules.

C, and the C preprocessor, are essential the same now as they were in
the early 1970s. I think that *an emphasis* on a preprocessing stage
of translation is a fairly discredited idea (though there are some
sensible uses, particularly where alternatives are not available). C99
introduced inline functions, probably in no small part because it is
quite obvious that they are often superior to macros. Consider the two
most successful programming languages that were obviously influenced
by C: Java and C++. The first doesn't have a preprocessor, and the
second strongly encourages using numerous alternatives to macros where
possible, which is almost always. Maybe you don't like this analogy,
because you consider C to be a systems programming language, and as
such think it is only right and proper that programmers should be
given enough rope to hang themselves. Perhaps you're right, but the
same surely cannot be said for SQL. The original appeal of SQL was
that it was supposedly possible for non-programmers to write it.

Clearly deprecating rules implies some loss of functionality - there
is no exact, drop-in equivalent to something that magically rewrites
SQL that isn't equally baroque and problematic. If that's the bar,
then detractors of rules should stop wasting their breath, because the
bar has been set impossibly high.

On a *practical* level triggers are complete replacements for
user-defined rules. All that it takes to be able to *always* say that
one language feature is not equivalent to another, and on that basis
the other should not be deprecated, is a sufficient degree of pedantry
(not that I'm implying that you or anyone else was being pedantic, or
that concerns raised should not be heeded).

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

On 10/15/2012 09:07 AM, Simon Riggs wrote:
> On 15 October 2012 11:41, Greg Stark <stark(at)mit(dot)edu> wrote:
>> On Mon, Oct 15, 2012 at 8:00 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>>> Please can anyone show me the SQL for a rule that cannot be written as
>>> a view or a trigger? I do not believe such a thing exists and I will
>>> provide free beer to the first person that can prove me wrong.
>> Being written as a view doesn't help you because views use rules. I
>> repeat, the very fact that we need rules to implement views prove
>> rules are necessary for some purposes.
> No, it just means there is some aspect of similar underlying infrastructure.
>
> Denial of free beer looks like proof to me...
>

*sigh*

First, as Tom said, the onus of proof is on you. You can't transfer it
away with this offer of free beer.

Second, he's actually told you one advantage rules can have over
triggers, but you've pretty much chosen to ignore it:

> Triggers necessarily operate on a row-at-a-time basis. In theory,
> for at least some bulk operations, a rule could greatly outperform
> a trigger. It's difficult to walk away from that - unless somebody
> can prove that the advantage doesn't ever accrue in practice.

I have seen rules used instead of triggers for precisely this reason.
Yes, the fact that COPY bypasses rules is something you need to
remember, but that makes it a limitation of the feature, not an absolute
reason not to use it. (I rarely if ever use them myself - can't recall
the last time I did, but there is plenty of legacy use out there.)

cheers

andrew

On 15 October 2012 14:43, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>
> On 10/15/2012 09:07 AM, Simon Riggs wrote:
>>
>> On 15 October 2012 11:41, Greg Stark <stark(at)mit(dot)edu> wrote:
>>>
>>> On Mon, Oct 15, 2012 at 8:00 AM, Simon Riggs <simon(at)2ndquadrant(dot)com>
>>> wrote:
>>>>
>>>> Please can anyone show me the SQL for a rule that cannot be written as
>>>> a view or a trigger? I do not believe such a thing exists and I will
>>>> provide free beer to the first person that can prove me wrong.
>>>
>>> Being written as a view doesn't help you because views use rules. I
>>> repeat, the very fact that we need rules to implement views prove
>>> rules are necessary for some purposes.
>>
>> No, it just means there is some aspect of similar underlying
>> infrastructure.
>>
>> Denial of free beer looks like proof to me...
>
> *sigh*
>
> First, as Tom said, the onus of proof is on you. You can't transfer it away
> with this offer of free beer.

I'm aware that evidence of abstinence is not the same as absence of
evidence - I was joking.

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

On 10/15/2012 12:41 PM, Greg Stark wrote:
> On Mon, Oct 15, 2012 at 8:00 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> Please can anyone show me the SQL for a rule that cannot be written as
>> a view or a trigger? I do not believe such a thing exists and I will
>> provide free beer to the first person that can prove me wrong.
> Being written as a view doesn't help you because views use rules. I
> repeat, the very fact that we need rules to implement views prove
> rules are necessary for some purposes.
>
We really don't *need* rules to implement views as proved by other
databases which do have views but don't have rules.

Rules were used for implementing views because they were already
there and that by constraining them to standard VIEW syntax we
could nicely cut back the footgunnyness of SELECT rules (by
disallowing the "bad" usage like replacing select by insert or doing
multiple selects instead of one). Some of that is still available when
you place a VIEW over a function, but then it is at least more explicit.

-----
Hannu

Simon, Peter, etc.:

Perhaps we should take a different tack on this discussion: what feature
development is the continued presense of RULES currently blocking? If
the rest of us had some idea why you considered this deprecation urgent,
it would help!

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

On Mon, Oct 15, 2012 at 12:30:56AM +0100, Greg Stark wrote:
> On Sun, Oct 14, 2012 at 9:30 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> > On 12 October 2012 19:48, Greg Stark <stark(at)mit(dot)edu> wrote:
> >> On Fri, Oct 12, 2012 at 7:55 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> >>> AFAICS all RULEs can be re-expressed as Triggers or Views.
> >>
> >> This is a bizarre discussion. Firstly this isn't even close to true.
> >> The whole source of people's discontentment is that triggers are *not*
> >> equivalent to rules. If they were then they wouldn't be so upset.
> >
> > This may be a confusion on the point of equivalence; clearly the
> > features work differently.
> >
> > I'm not aware of any rule that can't be rewritten as a trigger or a
> > view. Please can anyone show me some examples of those?
>
> Huh? The one thing we currently use rules for, implementing views,
> couldn't be done in triggers. In general if your source table is empty
> then there's *nothing* you could cause to happen with triggers because
> no triggers will fire.
>
> The analogy to this discussion would be something like "users get
> confused by macros in C and usually what they're trying to do can be
> better done with functions. now that we have functions we should
> deprecate macros" All of the preconditions in that sentence are true
> but it doesn't follow because macros exist for a reason.

Well, on a related note, I have heard that Java didn't implement macros
because it confuses context-sensitive editors. Seems like a
wrong-headed reason to remove a feature.

This is not related to my opinion on rules, but I thought it was
interesting.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On 15 October 2012 18:43, Josh Berkus <josh(at)agliodbs(dot)com> wrote:

> Perhaps we should take a different tack on this discussion: what feature
> development is the continued presense of RULES currently blocking? If
> the rest of us had some idea why you considered this deprecation urgent,
> it would help!

From me, this isn't urgent at all, as I've said. But it is one source
of disrepute for us that I would have liked to see blocked
quicker/better. But we've agreed a way forwards with doc changes and
that is enough for now.

Personally, I think rules block MERGE, but it has already been agreed
that we would ignore rules for that case. But having said that, MERGE
is not being worked on currently, so its hardly a hot topic and
nothing I was thinking about when I brought up the topic.

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

On Mon, Oct 15, 2012 at 02:14:34PM +0100, Peter Geoghegan wrote:
> On 15 October 2012 00:30, Greg Stark <stark(at)mit(dot)edu> wrote:
> > In fact it's not a very good analogy because the situation is
> > *precisely* the same -- rules *are* macros and manipulate the raw sql
> > before it's run and the reason they can't be replaced by triggers is
> > because, like functions, triggers happen after the code is compiled
> > and run.
>
> I quite like this analogy, because it nicely illustrates the problems
> with rules.
>
> C, and the C preprocessor, are essential the same now as they were in
> the early 1970s. I think that *an emphasis* on a preprocessing stage
> of translation is a fairly discredited idea (though there are some
> sensible uses, particularly where alternatives are not available). C99
> introduced inline functions, probably in no small part because it is
> quite obvious that they are often superior to macros. Consider the two
> most successful programming languages that were obviously influenced
> by C: Java and C++. The first doesn't have a preprocessor, and the
> second strongly encourages using numerous alternatives to macros where
> possible, which is almost always. Maybe you don't like this analogy,
> because you consider C to be a systems programming language, and as
> such think it is only right and proper that programmers should be
> given enough rope to hang themselves. Perhaps you're right, but the
> same surely cannot be said for SQL. The original appeal of SQL was
> that it was supposedly possible for non-programmers to write it.

Ah, so Peter confered the Java angle, and I think he does present the
pitfalls of C macros, and that does translate to the pitfalls of rules.

I have trouble seeing how we could implement Postgres as efficiently
without C macros, but maybe that is the point --- efficiency is not
critical in SQL --- Java and C++ give other options that are "good
enough" and less error-prone.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On 10/15/2012 03:23 PM, Bruce Momjian wrote:
> I have trouble seeing how we could implement Postgres as efficiently
> without C macros, but maybe that is the point --- efficiency is not
> critical in SQL --- Java and C++ give other options that are "good
> enough" and less error-prone.
>
>

Er, C++ uses the preprocessor. In fact, C++ was originally created as a
set of preprocessor macros, IIRC.

cheers

andrew

On Mon, Oct 15, 2012 at 03:51:58PM -0400, Andrew Dunstan wrote:
>
> On 10/15/2012 03:23 PM, Bruce Momjian wrote:
> >I have trouble seeing how we could implement Postgres as efficiently
> >without C macros, but maybe that is the point --- efficiency is not
> >critical in SQL --- Java and C++ give other options that are "good
> >enough" and less error-prone.
> >
> >
>
> Er, C++ uses the preprocessor. In fact, C++ was originally created
> as a set of preprocessor macros, IIRC.

I assumed from the emails that macros were discouraged in C++; I don't
know myself personally. I certainly would miss macro abilities in C.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Peter Geoghegan <peter(at)2ndquadrant(dot)com> writes:
> Clearly deprecating rules implies some loss of functionality - there
> is no exact, drop-in equivalent to something that magically rewrites
> SQL that isn't equally baroque and problematic. If that's the bar,
> then detractors of rules should stop wasting their breath, because the
> bar has been set impossibly high.

I believe an advice system is a good contender here, as already
proposed here:

http://archives.postgresql.org/pgsql-hackers/2012-10/msg00610.php

See defadvice in Emacs Lisp and The Standard Method Combination of the
Common Lisp Object System as sources of inspiration here.

http://www.gnu.org/software/emacs/manual/html_node/elisp/Advising-Functions.html
http://www.gigamonkeys.com/book/object-reorientation-generic-functions.html

It basically would be rules without the multiple evaluation risks, yet
still the multiple evaluation feature when you need it, and with
explicit control over it.

Then if you insist on comparing to a macro facility, as we're talking
about dynamic code rewriting, maybe we need to compare RULEs to the lisp
style macro facility, which is nothing like a pre-processor facility (in
lisp, that's the reader, I think).

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

On 12 October 2012 10:08, Daniel Farina <daniel(at)heroku(dot)com> wrote:
> On Thu, Oct 11, 2012 at 11:55 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> As regards cost/benefit analysis, this is a low importance feature,
>> but then that is why I proposed a low effort fix that is flexible to
>> the needs of users affected.
>
> Is there any feature that is more loathed and more narrowly used than
> rules?

I doubt it.

Would you or someone else be able to come up with some words of
caution for us to put in the manual that would be helpful to
developers?

There isn't even a list of caveats for rules.

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

On 10/17/2012 02:48 AM, Simon Riggs wrote:

> Would you or someone else be able to come up with some words of
> caution for us to put in the manual that would be helpful to
> developers?
>
> There isn't even a list of caveats for rules.

I think we need the inverse. Some documentation on why to use rules and
this basically boils down to the problem. Can anyone tell me a reason to
use explicit rules over a trigger and function combination?

And that is the crux of the issue. If we can't identify a reason the
feature currently exists and we have a suitable and better replacement,
the feature should be deprecated and removed.

My suggestion for docs is:

Note: Do not use, use Triggers with Functions instead <link>

Sincerely,

Joshua D. Drake

--
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 17 October 2012 18:02, Joshua D. Drake <jd(at)commandprompt(dot)com> wrote:

> Note: Do not use, use Triggers with Functions instead <link>

Agreed, something simple is required. I suggest expanding that just a little...

"Rules are a non-SQL Standard feature and where possible we recommend
that you write your applications using triggers, views and functions
instead. Although not likely to be fully deprecated soon, the use of
rules is now actively discouraged."

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

On 10/17/2012 11:31 AM, Dimitri Fontaine wrote:
> Peter Geoghegan <peter(at)2ndquadrant(dot)com> writes:
>> Clearly deprecating rules implies some loss of functionality - there
>> is no exact, drop-in equivalent to something that magically rewrites
>> SQL that isn't equally baroque and problematic.
Maybe we can upgrade STATEMENT triggers to level where they
cover all practical uses of rules.

Currently we can create FOR EACH STATEMENT triggers on
INSERT OR UPDATE OR DELETE but they are pretty useless for
anything else than recording that such an even took place as
both OLD and NEW are empty for these.

Perhaps we can make them much more useful by exposing
more of the original statement to the called function.

>> If that's the bar,
>> then detractors of rules should stop wasting their breath, because the
>> bar has been set impossibly high.
> I believe an advice system is a good contender here, as already
> proposed here:
>
> http://archives.postgresql.org/pgsql-hackers/2012-10/msg00610.php
>
> See defadvice in Emacs Lisp and The Standard Method Combination of the
> Common Lisp Object System as sources of inspiration here.
>
> http://www.gnu.org/software/emacs/manual/html_node/elisp/Advising-Functions.html
> http://www.gigamonkeys.com/book/object-reorientation-generic-functions.html
>
> It basically would be rules without the multiple evaluation risks, yet
> still the multiple evaluation feature when you need it, and with
> explicit control over it.
>
> Then if you insist on comparing to a macro facility, as we're talking
> about dynamic code rewriting, maybe we need to compare RULEs to the lisp
> style macro facility, which is nothing like a pre-processor facility
Is it something closer to decorators in some languages - that is functions
that wrap other functions in some extra functionality ?

> (in
> lisp, that's the reader, I think).
>
> Regards,

All,

For the record, I like RULEs and would prefer if someone fixed the
issues with them instead of deprecating them. However, I also
acknowledge that that is unlikely to happen.

> Would you or someone else be able to come up with some words of
> caution for us to put in the manual that would be helpful to
> developers?

We could start with:

=================

Warning: RULEs are tricky to use correctly and difficult to understand,
even for users with a lot of PostgreSQL experience. For most purposes,
you want a trigger<link> and not a RULE. It is also likely that the
RULEs feature will be deprecated in some future release of PostgreSQL
when all RULE functionality can be replaced by other mechanisms.

The PostgreSQL Project recommends that you use Triggers<link> instead of
RULEs unless you have a specific reason to use RULEs.

==================

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

I dislike both of the explanations above which don't actually explain
why people shouldn't use rules (Josh does say they're tricky which is
a start). Just telling people we hate parts of the system doesn't
really come off well and leaves them wondering why.

I would suggest something like

Warning: RULES are tricky to use correctly. They rewrite the original
query into a new query before it is run and it is very hard to
correctly anticipate and rewrite every possible input query into the
desired result. There are also unexpected interactions with other
components when RULES do something unexpected such as rewrite a single
query to return two result sets.

For most applications it's much simpler and more predictable to use
TRIGGERs. ROW level triggers are evaluated for each row the original
query is about to process (or has just finished processing) and this
makes them much easier to follow. Statement level TRIGGERs can be used
for audit logs and similar operations which need to run once per
statement.

On 10/17/2012 01:02 PM, Joshua D. Drake wrote:
>
> On 10/17/2012 02:48 AM, Simon Riggs wrote:
>
>> Would you or someone else be able to come up with some words of
>> caution for us to put in the manual that would be helpful to
>> developers?
>>
>> There isn't even a list of caveats for rules.
>
> I think we need the inverse. Some documentation on why to use rules
> and this basically boils down to the problem. Can anyone tell me a
> reason to use explicit rules over a trigger and function combination?
>
>

I don't know how many times I have to say this: people are not
listening. Tom has already given a case for it upthread:

>> Triggers necessarily operate on a row-at-a-time basis. In theory,
>> for at least some bulk operations, a rule could greatly outperform
>> a trigger. It's difficult to walk away from that - unless somebody
>> can prove that the advantage doesn't ever accrue in practice.
>

People can keep ignoring that if they like, but some of us won't. This
mantra of "there is no reason at all to use rules" is like climate
change denial - no matter how many times you say it that won't make it true.

cheers

andrew

On 10/12/12, Josh Berkus <josh(at)agliodbs(dot)com> wrote:

> I realize you weren't around when we removed row OIDs, but I was *still*
> getting flack from that in 2008. And we lost entire OSS projects to
> other databases because of removing row OIDs. And those were marked
> deprecated for 3 years before we removed them.

FWIW, the documentation for 9.2 still mentions OIDs and the
'default_with_oids' parameter, in what release was it announced they
would be removed and in what release were they removed?
--
Mike Nolan

On 17 October 2012 18:50, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> I don't know how many times I have to say this: people are not listening.
> Tom has already given a case for it upthread:
>
>
>>> Triggers necessarily operate on a row-at-a-time basis. In theory,
>>> for at least some bulk operations, a rule could greatly outperform
>>> a trigger. It's difficult to walk away from that - unless somebody
>>> can prove that the advantage doesn't ever accrue in practice.

Fair point. I'm just not sure that that is a good enough reason to not
deprecate rules. I mean, if experienced hackers cannot figure out if
that's actually a useful facet of rules, what hope is there for anyone
else?

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

On 17 October 2012 18:46, Greg Stark <stark(at)mit(dot)edu> wrote:
> I would suggest something like
>
> Warning: RULES are tricky to use correctly. They rewrite the original
> query into a new query before it is run and it is very hard to
> correctly anticipate and rewrite every possible input query into the
> desired result. There are also unexpected interactions with other
> components when RULES do something unexpected such as rewrite a single
> query to return two result sets.

+1 to that sort of wording.

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

Greg,

> Warning: RULES are tricky to use correctly. They rewrite the original
> query into a new query before it is run and it is very hard to
> correctly anticipate and rewrite every possible input query into the
> desired result. There are also unexpected interactions with other
> components when RULES do something unexpected such as rewrite a single
> query to return two result sets.
>
> For most applications it's much simpler and more predictable to use
> TRIGGERs. ROW level triggers are evaluated for each row the original
> query is about to process (or has just finished processing) and this
> makes them much easier to follow. Statement level TRIGGERs can be used
> for audit logs and similar operations which need to run once per
> statement.

This is excellent. However, if we are actually considerting deprecating
them, we should add the sentence "RULEs may be deprecated in a future
release of PostgreSQL."

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

Greg Stark <stark(at)mit(dot)edu> writes:
> I dislike both of the explanations above which don't actually explain
> why people shouldn't use rules (Josh does say they're tricky which is
> a start). Just telling people we hate parts of the system doesn't
> really come off well and leaves them wondering why.

Agreed. I think that by far the most common problem people hit with
rules has to do with unexpected multiple evaluations of volatile
functions (eg nextval). If we're going to put in some warning text
I think it would be smart to explain that and maybe even show an
example. It'd be easy enough to generate an example involving,
say, a rule that's meant to log rows that are inserted.

regards, tom lane

On 10/17/2012 10:46 AM, Greg Stark wrote:
>
> I dislike both of the explanations above which don't actually explain
> why people shouldn't use rules (Josh does say they're tricky which is
> a start). Just telling people we hate parts of the system doesn't
> really come off well and leaves them wondering why.
>
> I would suggest something like
>
> Warning: RULES are tricky to use correctly. They rewrite the original
> query into a new query before it is run and it is very hard to
> correctly anticipate and rewrite every possible input query into the
> desired result. There are also unexpected interactions with other
> components when RULES do something unexpected such as rewrite a single
> query to return two result sets.
>
> For most applications it's much simpler and more predictable to use
> TRIGGERs. ROW level triggers are evaluated for each row the original
> query is about to process (or has just finished processing) and this
> makes them much easier to follow. Statement level TRIGGERs can be used
> for audit logs and similar operations which need to run once per
> statement.
>

I am not sure where to stick it but we should also include the fact that
rules are almost always slower that a trigger/function comparative.

Sincerely,

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

> I am not sure where to stick it but we should also include the fact that
> rules are almost always slower that a trigger/function comparative.

That wouldn't be accurate, actually.

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

On 10/17/12 2:31 AM, Dimitri Fontaine wrote:
> Then if you insist on comparing to a macro facility, as we're talking
> about dynamic code rewriting, maybe we need to compare RULEs to the lisp
> style macro facility, which is nothing like a pre-processor facility (in
> lisp, that's the reader, I think).

Except the vast majority of the audience likely have no idea what the
'lisp macro system' is like or what this comparison implies.. Even
though I dabbled in a little LISP almost 40 years ago, I don't remember
anything about LISP macros, just Assembler and C macros :)

Any such comparison should be with something that is common knowledge,
not something even more obscure than the primary subject matter.

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

On 17 October 2012 18:46, Greg Stark <stark(at)mit(dot)edu> wrote:

> I would suggest something like
>
> Warning: RULES are tricky to use correctly. They rewrite the original
> query into a new query before it is run and it is very hard to
> correctly anticipate and rewrite every possible input query into the
> desired result. There are also unexpected interactions with other
> components when RULES do something unexpected such as rewrite a single
> query to return two result sets.
>
> For most applications it's much simpler and more predictable to use
> TRIGGERs. ROW level triggers are evaluated for each row the original
> query is about to process (or has just finished processing) and this
> makes them much easier to follow. Statement level TRIGGERs can be used
> for audit logs and similar operations which need to run once per
> statement.

I like this very much. Thank you.

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

On 10/17/12 10:46 AM, Greg Stark wrote:
> Warning: RULES are tricky to use correctly. They rewrite the original
> query into a new query before it is run and it is very hard to
> correctly anticipate and rewrite every possible input query into the
> desired result. There are also unexpected interactions with other
> components when RULES do something unexpected such as rewrite a single
> query to return two result sets.
>
> For most applications it's much simpler and more predictable to use
> TRIGGERs. ROW level triggers are evaluated for each row the original
> query is about to process (or has just finished processing) and this
> makes them much easier to follow. Statement level TRIGGERs can be used
> for audit logs and similar operations which need to run once per
> statement.

as a relative novice, I concur, this is clear, concise, and to the point.

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

On Wed, Oct 17, 2012 at 10:50 AM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>>> Triggers necessarily operate on a row-at-a-time basis. In theory,
>>> for at least some bulk operations, a rule could greatly outperform
>>> a trigger. It's difficult to walk away from that - unless somebody
>>> can prove that the advantage doesn't ever accrue in practice.
>>
> People can keep ignoring that if they like, but some of us won't. This
> mantra of "there is no reason at all to use rules" is like climate change
> denial - no matter how many times you say it that won't make it true.

I think there is an assumed presumption on behalf of those those
vigorously opposing the deprecation of rules that everyone understands
what the use cases for rules are and their respective commonality. So
far, the discussion has been pretty unenlightening to me, and I find
the notion that those in favor of deprecation are just skirting well
known questions ill justified. Just because an "in theory..." case
works better is not in and of itself enough to warrant a vigorous
defense -- perhaps I missed the email where people said "yes, I see
that all the time when rules are involved and wouldn't want to go
without it".

You and Josh seem to be strong proponents of rules for reasons other
than "I just don't want to break applications". That's not too many
to ask both of you: can you itemize your use cases and how important
you feel they are?

I'll cost-size it for you: for me, as of my current understanding, if
but one more defect can be removed per year by dropping all
maintenance of RULES in exchange, I'd take that trade, as I understand
things right now.

--
fdr

On 10/17/2012 11:32 AM, Josh Berkus wrote:
>
>
>> I am not sure where to stick it but we should also include the fact that
>> rules are almost always slower that a trigger/function comparative.
>
> That wouldn't be accurate, actually.

Let me add: when used with partitioning. I should have been more explicit.

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 10/17/2012 03:06 PM, Daniel Farina wrote:
> On Wed, Oct 17, 2012 at 10:50 AM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>>>> Triggers necessarily operate on a row-at-a-time basis. In theory,
>>>> for at least some bulk operations, a rule could greatly outperform
>>>> a trigger. It's difficult to walk away from that - unless somebody
>>>> can prove that the advantage doesn't ever accrue in practice.
>> People can keep ignoring that if they like, but some of us won't. This
>> mantra of "there is no reason at all to use rules" is like climate change
>> denial - no matter how many times you say it that won't make it true.
> I think there is an assumed presumption on behalf of those those
> vigorously opposing the deprecation of rules that everyone understands
> what the use cases for rules are and their respective commonality. So
> far, the discussion has been pretty unenlightening to me, and I find
> the notion that those in favor of deprecation are just skirting well
> known questions ill justified. Just because an "in theory..." case
> works better is not in and of itself enough to warrant a vigorous
> defense -- perhaps I missed the email where people said "yes, I see
> that all the time when rules are involved and wouldn't want to go
> without it".
>
> You and Josh seem to be strong proponents of rules for reasons other
> than "I just don't want to break applications". That's not too many
> to ask both of you: can you itemize your use cases and how important
> you feel they are?
>
> I'll cost-size it for you: for me, as of my current understanding, if
> but one more defect can be removed per year by dropping all
> maintenance of RULES in exchange, I'd take that trade, as I understand
> things right now.
>

I'll give you one case, although I still think Tom is right - the onus
of proof is on those proposing to remove a feature, not the other way
around.

Some years ago I was partitioning a large data store. By far the fastest
way to do this, by about an order of magnitude, turned out to be using a
partitioning rule. In testing it was way faster than using a trigger,
even one written in C, or pulling out the individual partitions one by
one. And I don't thing writing triggers in C is an acceptable
replacement for rules anyway.

One I had the data partitioned I dropped the rule and put a trigger in
place.

Now I'd be fairly miffed if we just removed that capability. I
personally feel that the bar for removing features should be pretty darn
high.

cheers

andrew

On Wed, Oct 17, 2012 at 12:43 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>
> On 10/17/2012 03:06 PM, Daniel Farina wrote:
>>
>> On Wed, Oct 17, 2012 at 10:50 AM, Andrew Dunstan <andrew(at)dunslane(dot)net>
>> wrote:
>>>>>
>>>>> Triggers necessarily operate on a row-at-a-time basis. In theory,
>>>>> for at least some bulk operations, a rule could greatly outperform
>>>>> a trigger. It's difficult to walk away from that - unless somebody
>>>>> can prove that the advantage doesn't ever accrue in practice.
>>>
>>> People can keep ignoring that if they like, but some of us won't. This
>>> mantra of "there is no reason at all to use rules" is like climate change
>>> denial - no matter how many times you say it that won't make it true.
>>
>> I think there is an assumed presumption on behalf of those those
>> vigorously opposing the deprecation of rules that everyone understands
>> what the use cases for rules are and their respective commonality. So
>> far, the discussion has been pretty unenlightening to me, and I find
>> the notion that those in favor of deprecation are just skirting well
>> known questions ill justified. Just because an "in theory..." case
>> works better is not in and of itself enough to warrant a vigorous
>> defense -- perhaps I missed the email where people said "yes, I see
>> that all the time when rules are involved and wouldn't want to go
>> without it".
>>
>> You and Josh seem to be strong proponents of rules for reasons other
>> than "I just don't want to break applications". That's not too many
>> to ask both of you: can you itemize your use cases and how important
>> you feel they are?
>>
>> I'll cost-size it for you: for me, as of my current understanding, if
>> but one more defect can be removed per year by dropping all
>> maintenance of RULES in exchange, I'd take that trade, as I understand
>> things right now.
>>
>
>
> I'll give you one case, although I still think Tom is right - the onus of
> proof is on those proposing to remove a feature, not the other way around.

I'll have to register my disagreement then, in the special case where
a feature becomes so obscure that many people don't have a wide-spread
intuition at what it's good at or used for. Tom also said "build the
replacement," and without itemization of use cases, I don't even know
what that would look like -- perhaps such knowledge is assumed, but I
think it's assumed wrongly, so perhaps there just needs to be some
education. At best you could define what to build somewhat
tautologically from the mechanism used by RULES, and that's not a very
good way to go about it, methinks.

Thank you for humoring me and fleshing out your case anyway.

> Some years ago I was partitioning a large data store. By far the fastest way
> to do this, by about an order of magnitude, turned out to be using a
> partitioning rule. In testing it was way faster than using a trigger, even
> one written in C, or pulling out the individual partitions one by one. And I
> don't thing writing triggers in C is an acceptable replacement for rules
> anyway.
>
> One I had the data partitioned I dropped the rule and put a trigger in
> place.

That's a good one. So, would a more legitimate partitioning becoming
a feature be enough to assuage user-visible rules support? Or are
there other cases?

> Now I'd be fairly miffed if we just removed that capability. I personally
> feel that the bar for removing features should be pretty darn high.

The bar for quality is also high. Like I said: to my needs, one less
bug outweighs the advantages of rules, especially if that advantage is
carried over multiple years. I still lose quite a bit in the
deprecation regardless: if even 0.1% of the customer base uses rules,
a sudden deprecation will cause us a lot of pain. However, a slow
deprecation is a lot more manageable and, if it pays off in one more
bug solved a year or a better positioned feature maintained with
equivalent effort it will have been worth it.

That's another thing that has not come up for discussion: those who
maintain rules -- are they happy to do it? What is the investment of
time like? I have been presuming a cost of maintenance, but I have
never heard someone who actually maintains rules regularly or
implements features that become more complex because of it try to size
the benefit one way or another.

--
fdr

> You and Josh seem to be strong proponents of rules for reasons other
> than "I just don't want to break applications". That's not too many
> to ask both of you: can you itemize your use cases and how important
> you feel they are?

Well, my main issue is actually that I don't want to break people's
applications. I seldom use RULEs myself.

The one area where I do is handling bulk loading for partitioning or
other table redirection. For this case, rules are much, much, much (as
in 100X) faster than row-at-a-time triggers.

Could statement triggers and MERGE be improved to replace this? Pretty
much certainly. Have they been? No.

Also, I'll point out that surveying -hackers for feature usage is a
pretty skewed group. A bunch of the people on this list (maybe most of
them) don't develop applications -- some never have. If we think
surveying usage affects our plans for deprecation, then we ought to
survey a less select group of users.

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

On 10/17/12 12:57 PM, Daniel Farina wrote:
> I'll have to register my disagreement then, in the special case where
> a feature becomes so obscure that many people don't have a wide-spread
> intuition at what it's good at or used for. Tom also said "build the
> replacement," and without itemization of use cases, I don't even know
> what that would look like -- perhaps such knowledge is assumed, but I
> think it's assumed wrongly, so perhaps there just needs to be some
> education. At best you could define what to build somewhat
> tautologically from the mechanism used by RULES, and that's not a very
> good way to go about it, methinks.

Well, there are the cases for which RULEs are actually the superior/only
mechanism (probably a fairly small set) and the cases where they are
not, but are used anyway (a much larger set). For the latter group,
those cases need to be (a) identified, and (b) migration documented.

For example, one can currently create an ON UPDATE rule to make a view
updatable. It is now also possible to create a trigger to do the same
thing, and its results would be more predictable. However, nobody has
documented how one would migrate and existing UPDATE rule to a new ON
UPDATE trigger.

Putting it as "Andrew and Josh need to enumerate these cases, or forever
be silent" is quite unfair to our users. Andrew and I hardly represent
the entire scope of PostgreSQL app developers. Enumerating the cases,
finding replacements for them, and documenting migrations needs to be a
group effort.

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

On Wed, Oct 17, 2012 at 1:12 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> On 10/17/12 12:57 PM, Daniel Farina wrote:
>> I'll have to register my disagreement then, in the special case where
>> a feature becomes so obscure that many people don't have a wide-spread
>> intuition at what it's good at or used for. Tom also said "build the
>> replacement," and without itemization of use cases, I don't even know
>> what that would look like -- perhaps such knowledge is assumed, but I
>> think it's assumed wrongly, so perhaps there just needs to be some
>> education. At best you could define what to build somewhat
>> tautologically from the mechanism used by RULES, and that's not a very
>> good way to go about it, methinks.

[use case, redacted, although worth independent consideration]

> Putting it as "Andrew and Josh need to enumerate these cases, or forever
> be silent" is quite unfair to our users. Andrew and I hardly represent
> the entire scope of PostgreSQL app developers. Enumerating the cases,
> finding replacements for them, and documenting migrations needs to be a
> group effort.

Unfortunately I myself see little evidence of the vast, vast --
several nines of vast -- majority of folks using rules, and as I said:
as a thought experiment, merely one solved bug is worth more to me
than rules from what I know at this time. If I had a wealth of user
pain to draw upon, I would have in opposition to their deprecation.
But, I don't, so I am cautiously in favor of pipelining a slow
deprecation, even though I can only be hurt by the process tactically
-- strategically, I can be helped, e.g. by solving even one defect
that lowers *my* maintenance cost. You can consider my sentiment the
result of some evidence of absence, if you will. I can probably
refine this intuition if it would change someone's mind, but given the
tone of conversation, I'd probably simply be given a no-true-scotsman
retort -- which is true, Heroku's user base is not provably
representative of all users. But what else is there to go on, besides
experiences of others, such as yours and Andrew's, or others?

Both of you have given some well-considered use cases now, but the
conversation was a quagmire a while because it seems like the thing to
do was dismiss those charitable to the idea of deprecation rather than
even tersely list out use cases that are in danger. If the project
suffered a vast number of deprecation requests I could understand the
'silence is not consent' argument, because who has the time to defend
all territory all the time? But as-is such conversations are so rare
that I think positive identification of use cases is worthwhile use of
time, if it can result in but a chance of eliminating maintenance
burden and surface area.

Features do not stay for free, especially not at the level of quality
the project demands, and my personal sanity benefits from that
quality. While nobody has given a cost of the maintenance of rules, I
would surmise it is non-zero, and consuming resources on potentially
lousy features is not a service to users either, and I do not wish
that to be ignored.

Finally, putting aside the use cases you are able to positively
identify from your personal experirence, I think it's reasonable to
put in a message of intent-to-deprecate and reverse or revise course
as more data appears. Perhaps the thinking should be: "intent to
aggressively gather data to enable deprecation" rather than "a final
deprecation decision and plan, full stop." The most direct route may
be to package such a request into error messages or warnings in the
database, because I do not think release notes or announcements are
enough.

Contrast this with the sudden change to VACUUM FULL: from no doubling
in space usage to a doubling in space usage temporarily. That's
nothing to sneeze at, and who knows, thousands of administrative
scripts happily VACUUM FULLing could have blown up terribly. But, it
was changed anyway, because the feature was pretty much deemed not
that useful to people relating their needs. How is this reasoning
consistent with that change?

--
fdr

On Wed, Oct 17, 2012 at 5:45 PM, Daniel Farina <daniel(at)heroku(dot)com> wrote:
> retort -- which is true, Heroku's user base is not provably
> representative of all users. But what else is there to go on, besides
> experiences of others, such as yours and Andrew's, or others?

Well, Heroku doesn't support Slony + Londiste (and I am not overly
indignant at your reasoning at not supporting them), so I think we can
guarantee that anything involving those trigger-based replication
systems will be absent from Heroku's user base. Which is not to
express indignance, but merely that there are a few knowable biases
about your users. And I'd expect quite a few unknow(n|able) ones.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

Daniel,

> Unfortunately I myself see little evidence of the vast, vast --
> several nines of vast -- majority of folks using rules, and as I said:
> as a thought experiment, merely one solved bug is worth more to me
> than rules from what I know at this time.

Again, the answer to this is to run an aggressively promoted survey, so
that we can have data, rather than speculation by -hackers.

> Finally, putting aside the use cases you are able to positively
> identify from your personal experirence, I think it's reasonable to
> put in a message of intent-to-deprecate and reverse or revise course
> as more data appears. Perhaps the thinking should be: "intent to
> aggressively gather data to enable deprecation" rather than "a final
> deprecation decision and plan, full stop."

Exactly.

I fact, I'll go further and say that I believe we will be deprecating
RULEs eventually. It's merely a question of how long that will take and
what we need to document, announce and implement before then.

I would tend to say "well, they're not hurting anyone, why not keep
them?" Except that we're gathering an increasing number of features
(RETURNING, FDWs, CTEs, Command triggers) which don't work well together
with RULEs. That puts us in danger of turning into MySQL ("Sorry, you
can't use Full Text Search with transactions"), which is not a direction
we want to go in.

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

On 17 October 2012 23:24, Josh Berkus <josh(at)agliodbs(dot)com> wrote:

> I fact, I'll go further and say that I believe we will be deprecating
> RULEs eventually. It's merely a question of how long that will take and
> what we need to document, announce and implement before then.
>
> I would tend to say "well, they're not hurting anyone, why not keep
> them?" Except that we're gathering an increasing number of features
> (RETURNING, FDWs, CTEs, Command triggers) which don't work well together
> with RULEs. That puts us in danger of turning into MySQL ("Sorry, you
> can't use Full Text Search with transactions"), which is not a direction
> we want to go in.

I don't really understand. We *are* already in the position you say we
don't want to go towards. It's not a danger, its a current reality.

So what do we do? I've got the doc changes now. Let's agree the rest
of the plan...

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

On Wed, Oct 17, 2012 at 3:24 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> Daniel,
>
>> Unfortunately I myself see little evidence of the vast, vast --
>> several nines of vast -- majority of folks using rules, and as I said:
>> as a thought experiment, merely one solved bug is worth more to me
>> than rules from what I know at this time.
>
> Again, the answer to this is to run an aggressively promoted survey, so
> that we can have data, rather than speculation by -hackers.

I think that's great, but I am cynical enough to believe that after
such surveys that we should be prepared to turn back if the insertion
of a deprecation warning into Postgres generates more data (e.g.
complaints). I'm quite happy with long and even uncertain process,
depending on what happens, deprecation may have to put off for a very
long time.

I don't usually like to push so insistently, but I felt inclined to
because I did not feel that, in the beginning, that those proposing
that we even talk about the idea got a very evenhanded response. Your
sentiments may vary, but I feel this is a justified one, now.

>> Finally, putting aside the use cases you are able to positively
>> identify from your personal experirence, I think it's reasonable to
>> put in a message of intent-to-deprecate and reverse or revise course
>> as more data appears. Perhaps the thinking should be: "intent to
>> aggressively gather data to enable deprecation" rather than "a final
>> deprecation decision and plan, full stop."
>
> Exactly.
>
> I fact, I'll go further and say that I believe we will be deprecating
> RULEs eventually. It's merely a question of how long that will take and
> what we need to document, announce and implement before then.
>
> I would tend to say "well, they're not hurting anyone, why not keep
> them?" Except that we're gathering an increasing number of features
> (RETURNING, FDWs, CTEs, Command triggers) which don't work well together
> with RULEs. That puts us in danger of turning into MySQL ("Sorry, you
> can't use Full Text Search with transactions"), which is not a direction
> we want to go in.

Sounds very reasonable to me. Also, contains some good reasons for
deprecation I had not thought of.

--
fdr

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> I would tend to say "well, they're not hurting anyone, why not keep
> them?" Except that we're gathering an increasing number of features
> (RETURNING, FDWs, CTEs, Command triggers) which don't work well together
> with RULEs.

Really? On what do you base that claim? The only one of those that I
might believe is command triggers, but AFAIK we only have/plan command
triggers for DDL, so there's no overlap.

I'm fairly annoyed by the entire tenor of this conversation, because
the people who are hollering the loudest seem to be people who have
never actually touched any of the rules code, but nonetheless seem
prepared to tell those of us who have what to spend our time on.

Now having said that, I would definitely like to see rules in their
current form go away eventually. But not without a substitute.
Triggers are not a complete replacement, and no amount of wishful
thinking makes them so.

Perhaps it would be more profitable to try to identify the pain points
that make people so eager to get rid of rules, and then see if we could
alleviate them. One big problem I know about offhand is the
multiple-evaluation risk, which seems at least in principle fixable.
What others are there?

regards, tom lane

On Oct 17, 2012, at 4:45 PM, Daniel Farina wrote:

> On Wed, Oct 17, 2012 at 1:12 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>> On 10/17/12 12:57 PM, Daniel Farina wrote:
>>> I'll have to register my disagreement then, in the special case where
>>> a feature becomes so obscure that many people don't have a wide-spread
>>> intuition at what it's good at or used for. Tom also said "build the
>>> replacement," and without itemization of use cases, I don't even know
>>> what that would look like -- perhaps such knowledge is assumed, but I
>>> think it's assumed wrongly, so perhaps there just needs to be some
>>> education. At best you could define what to build somewhat
>>> tautologically from the mechanism used by RULES, and that's not a very
>>> good way to go about it, methinks.
>
> [use case, redacted, although worth independent consideration]
>
>> Putting it as "Andrew and Josh need to enumerate these cases, or forever
>> be silent" is quite unfair to our users. Andrew and I hardly represent
>> the entire scope of PostgreSQL app developers. Enumerating the cases,
>> finding replacements for them, and documenting migrations needs to be a
>> group effort.
>
> Unfortunately I myself see little evidence of the vast, vast --
> several nines of vast -- majority of folks using rules, and as I said:
> as a thought experiment, merely one solved bug is worth more to me
> than rules from what I know at this time. If I had a wealth of user
> pain to draw upon, I would have in opposition to their deprecation.
> But, I don't, so I am cautiously in favor of pipelining a slow
> deprecation, even though I can only be hurt by the process tactically

I am a lurker here, and as such, understand that I have no standing. But I do write internal applications using postgresql and it seems to me that the direction forward is clear. I've just went back and read the 9.2 documentation on Rules. It appears that Rules are a current supported and best solution to many problems. So as previously stated and I think pretty much agreed the docs must be changed. I did not pick up from the docs that there were the problems mentioned in the various emails.

With that said, having read each email, there are some politics that do not make sense.

Are these the facts?

1. Rules are required in the core. For example, that is how views are implemented.
2. There are some, possibly fringe, use cases where Rules are the best solution.
3. There are many uses of Rules that are fragile, or even broken in implementation.

4. There is a desire to make Rules an internal core functionality only.
or
5. There is a desire to eliminate Rules all together.

6. There is new functionality that does not work correctly considering Rules. (e.g. Rules code is not updated.)

It would seem to me that with #1 and #2 it is foolish (to me, not understanding the politics) to consider deprecation.

The real issue is, "Should Rules be visible to users?"

As an application developer, I do not use Rules because they are non standard and my code will be used by different back ends, so personality I have no skin in this decision. But logically, I think that it is silly to consider deprecation at this time. The time to consider deprecation is when no core functionality depends on Rules. Until that time, there is nothing to be gained by deprecation and there is no reason to piss off users by deprecation of code that has to be maintained anyway.

So I would move the docs to the internal section, state that Rules are not recommended to be used in user SQL, and that Rules may be deprecated in the future, then leave things alone for a couple of years until the way forward becomes clear. If developers want to deprecate Rules, then create code that eliminates Rules from being require for core functions.

It seems to me that eventually Rules will suffer bit rot and it will be clear that it is time to remove all traces, or Rules will be maintained (albeit possibly less scope) and they will continue as core functionality based on need.

Neil

On 10/17/2012 07:25 PM, Tom Lane wrote:

>
> I'm fairly annoyed by the entire tenor of this conversation, because
> the people who are hollering the loudest seem to be people who have
> never actually touched any of the rules code, but nonetheless seem
> prepared to tell those of us who have what to spend our time on.

+1

I too have been quite annoyed.

>
> Now having said that, I would definitely like to see rules in their
> current form go away eventually. But not without a substitute.
> Triggers are not a complete replacement, and no amount of wishful
> thinking makes them so.
>
> Perhaps it would be more profitable to try to identify the pain points
> that make people so eager to get rid of rules, and then see if we could
> alleviate them. One big problem I know about offhand is the
> multiple-evaluation risk, which seems at least in principle fixable.
> What others are there?
>
>

Yeah. That's by far the best approach. It has the merit of being
positive rather than just taking something away that people do use, even
if it's only a relatively small number of users.

The biggest pain people have mentioned is that they don't work with
COPY. I am in fact about to start working on a project which will
probably alleviate that pain point. I'm not going to say much more, and
I would not have said anything right now except that there is this
sudden rush to deprecate rules, or announce a future removal of the
feature. However, I hope to have a proposal to put to the community by
about the end of November.

On that point, it's also worth noting that FDWs provide a nice
workaround, instead of doing a straight

COPY TO mytable FROM myfile

you set up the source as a foreign table using file_fdw or my
file_text_array_fdw, and then do

INSERT INTO mytable
SELECT ...
FROM my_foreign_table;

Maybe this too would be worth mentioning in the docs, maybe in the rules
section with an xref from the copy section.

So, please, hold off for little bit. I don't mind putting warnings in
the docs, but I'd really rather we waited on any announcement of a
future possible deprecation, or log warnings that using rules will cause
zombies to eat your brainz.

cheers

andrew

On 10/17/2012 04:25 PM, Tom Lane wrote:
> ...Now having said that, I would definitely like to see rules in their
> current form go away eventually. But not without a substitute.
> Triggers are not a complete replacement, and no amount of wishful
> thinking makes them so.
...
> Perhaps it would be more profitable to try to identify the pain points
> that make people so eager to get rid of rules, and then see if we
> could alleviate them.

Alternately/additionally identify the deficiencies in triggers that
drive users to prefer rules. For example, a common need is to update a
log table whenever updates are made to a main table.

Using rules to accomplish this is very easy to understand and write,
even for most beginners. (Understand properly including limitations and
dangers is another issue, of course.) It is also easy to maintain. If
you drop the table, the rule is cleaned up as well.

With triggers you need to select from a variety of available languages,
write a function in that language and write a trigger that calls that
function. Dropping the function will remove the trigger but the user
must remember to delete the function as well, if desired. Nothing
insurmountable but inconvenient compared to the use of a rule.

Per the documentation "PostgreSQL only allows the execution of a
user-defined function for the triggered action. The standard allows the
execution of a number of other SQL commands..."

There may be valid reasons why implementing that part of the SQL
standard in PostgreSQL is difficult or unwise but removing that
limitation on triggers would eliminate one annoyance that pushes users
toward rules.

Cheers,
Steve

On Thu, Oct 18, 2012 at 6:46 AM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>
> On 10/17/2012 07:25 PM, Tom Lane wrote:
>
>>
>> I'm fairly annoyed by the entire tenor of this conversation, because
>> the people who are hollering the loudest seem to be people who have
>> never actually touched any of the rules code, but nonetheless seem
>> prepared to tell those of us who have what to spend our time on.
>
>
> +1
>
> I too have been quite annoyed.

Sorry that I'm an offender. I also did not like the way the
conversation was going for some time; for me, I felt like I didn't
understand a lot of the terse rejections that materialized immediately
on behalf of users that I personally cannot identify, and I felt those
rejections weren't in a neutral language either that encouraged
clarification. I'm glad things have moved beyond that.

> The biggest pain people have mentioned is that they don't work with COPY. I
> am in fact about to start working on a project which will probably alleviate
> that pain point. I'm not going to say much more, and I would not have said
> anything right now except that there is this sudden rush to deprecate rules,
> or announce a future removal of the feature. However, I hope to have a
> proposal to put to the community by about the end of November.

I have encountered this as a papercut.

Here's another use case that in my history with RULES that didn't seem
to pan out so well: In my recollection, one way to use rules is to
retarget operations that happen against a view and move them to a
table, and as I recall to make this work as one expected one had to
have a very wordy RULE (for UPDATEs) with a litany of (fairly simple)
equality and not-null conditions to make it work as one would expect
(to not under-constrain the UPDATE). This became a maintenance
headache whenever attributes were added to the underlying relation.

It was also quite complex, as I recall, when one wanted to maintain an
interface but normalize the underlying table and split writes into two
or more places.

It has been quite some time, does that sound like a correct rendering
of a problem?

--
fdr

On Wed, Oct 17, 2012 at 7:25 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Josh Berkus <josh(at)agliodbs(dot)com> writes:
>> I would tend to say "well, they're not hurting anyone, why not keep
>> them?" Except that we're gathering an increasing number of features
>> (RETURNING, FDWs, CTEs, Command triggers) which don't work well together
>> with RULEs.
>
> Really? On what do you base that claim? The only one of those that I
> might believe is command triggers, but AFAIK we only have/plan command
> triggers for DDL, so there's no overlap.
>
> I'm fairly annoyed by the entire tenor of this conversation, because
> the people who are hollering the loudest seem to be people who have
> never actually touched any of the rules code, but nonetheless seem
> prepared to tell those of us who have what to spend our time on.
>
> Now having said that, I would definitely like to see rules in their
> current form go away eventually. But not without a substitute.
> Triggers are not a complete replacement, and no amount of wishful
> thinking makes them so.
>
> Perhaps it would be more profitable to try to identify the pain points
> that make people so eager to get rid of rules, and then see if we could
> alleviate them. One big problem I know about offhand is the
> multiple-evaluation risk, which seems at least in principle fixable.
> What others are there?

Well, it'd be nice to be able to rewrite a query referring to a table
to still refer to that same table, but you can't, because you get
infinite recursion.

If you could do that, it'd presumably be a WHOLE lot faster than
triggers. Because frankly, if triggers were infinitely fast, I don't
think we'd be having this conversation. But they're not. They're
slow; really slow, and while we may be able to improve that somewhat
by some means, they're basically always going to be slow. Being able
to rewrite queries is inside the server is useful, and rules are not a
very good solution to that problem, but right now they're the only
thing we've got.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 10/18/2012 01:11 PM, Daniel Farina wrote:

> Here's another use case that in my history with RULES that didn't seem
> to pan out so well: In my recollection, one way to use rules is to
> retarget operations that happen against a view and move them to a
> table, and as I recall to make this work as one expected one had to
> have a very wordy RULE (for UPDATEs) with a litany of (fairly simple)
> equality and not-null conditions to make it work as one would expect
> (to not under-constrain the UPDATE). This became a maintenance
> headache whenever attributes were added to the underlying relation.

Yes, but you also get a similar headache with a trigger. Unless you're
VERY careful you can get a trigger failure by adding an attribute, and
an almost guaranteed one by removing an attribute. It's true that the
language for specifying the operations is more expressive, but no matter
what mechanism you use, changing the shape of the objects can get you
into trouble.

I've never said that rules are perfect, nor that they should be used
whenever possible. What I have said is that there are known cases where
they are the best solution currently available. I still think that.

cheers

andrew

Well, it'd be nice to be able to rewrite a query referring to a table
to still refer to that same table, but you can't, because you get
infinite recursion.

If that was possible it would be quite easy to express any row/column level
security policies with it.

If you could do that, it'd presumably be a WHOLE lot faster than
triggers. Because frankly, if triggers were infinitely fast, I don't
think we'd be having this conversation. But they're not. They're
slow; really slow, and while we may be able to improve that somewhat
by some means, they're basically always going to be slow. Being able
to rewrite queries is inside the server is useful, and rules are not a
very good solution to that problem, but right now they're the only
thing we've got.

Moreover there are no triggers fired on selects.

Best regards

--

Luben Karavelov

On Thu, Oct 18, 2012 at 1:55 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>
> On 10/18/2012 01:11 PM, Daniel Farina wrote:
>
>> Here's another use case that in my history with RULES that didn't seem
>> to pan out so well: In my recollection, one way to use rules is to
>> retarget operations that happen against a view and move them to a
>> table, and as I recall to make this work as one expected one had to
>> have a very wordy RULE (for UPDATEs) with a litany of (fairly simple)
>> equality and not-null conditions to make it work as one would expect
>> (to not under-constrain the UPDATE). This became a maintenance
>> headache whenever attributes were added to the underlying relation.
>
>
>
> Yes, but you also get a similar headache with a trigger. Unless you're VERY
> careful you can get a trigger failure by adding an attribute, and an almost
> guaranteed one by removing an attribute. It's true that the language for
> specifying the operations is more expressive, but no matter what mechanism
> you use, changing the shape of the objects can get you into trouble.
>
> I've never said that rules are perfect, nor that they should be used
> whenever possible. What I have said is that there are known cases where they
> are the best solution currently available. I still think that.

I'm not going to disagree with that, I only feel it's reasonable to
ask why those who react so strongly against deprecation why they think
what they do, and receive a clinical response, because not everyone
has seen those use cases. My level of interest in deprecation is only
as far as "if those who have to deal with the RULES implementation
don't want to work on it anymore in favor of other things, I think the
pain to users of deprecation is, from my vantage point, manageable if
given some time."

I also want to be very clear that I know my vantage point is skewed,
but I feel like exposing what assessment of user activity I can to
-hackers is important, and the best I can do when it comes to
considering topics like these.

--
fdr

Daniel,

> I'm not going to disagree with that, I only feel it's reasonable to
> ask why those who react so strongly against deprecation why they think
> what they do, and receive a clinical response, because not everyone
> has seen those use cases. My level of interest in deprecation is only
> as far as "if those who have to deal with the RULES implementation
> don't want to work on it anymore in favor of other things, I think the
> pain to users of deprecation is, from my vantage point, manageable if
> given some time."

Note that you have heard from one of the people maintaining RULES, who
doesn't find them problematic to maintain (Tom). Note that the original
hackers calling for deprecation do not work on RULEs except where they
touch other features.

And I'll say again: if you want a full list of use-cases for RULEs, you
need to go further than the -hackers list. This is a small, insular
community which does not represent the majority of PostgreSQL users.
You have gone to Heroku's users, but given the nature of your user base,
they seem like the least likely group of people to use RULEs. That's
like me polling a bunch of Data Warehousing geeks and then declaring
that we don't really need SERIALIZABLE.

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

On Thu, Oct 18, 2012 at 6:10 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> Daniel,
>
>> I'm not going to disagree with that, I only feel it's reasonable to
>> ask why those who react so strongly against deprecation why they think
>> what they do, and receive a clinical response, because not everyone
>> has seen those use cases. My level of interest in deprecation is only
>> as far as "if those who have to deal with the RULES implementation
>> don't want to work on it anymore in favor of other things, I think the
>> pain to users of deprecation is, from my vantage point, manageable if
>> given some time."
>
> Note that you have heard from one of the people maintaining RULES, who
> doesn't find them problematic to maintain (Tom). Note that the original
> hackers calling for deprecation do not work on RULEs except where they
> touch other features.
>
> And I'll say again: if you want a full list of use-cases for RULEs, you
> need to go further than the -hackers list. This is a small, insular
> community which does not represent the majority of PostgreSQL users.
> You have gone to Heroku's users, but given the nature of your user base,
> they seem like the least likely group of people to use RULEs. That's
> like me polling a bunch of Data Warehousing geeks and then declaring
> that we don't really need SERIALIZABLE.

I have tried very assiduously to avoid generalizing, even though
perhaps I have failed. I made a false assumption that nobody wanted
to work with RULES that I am very sorry for.

What I do not like, and stand by, is that I did not like the form of
terse dismissal of even the idea of deprecation, with a feeling that
it is entirely unnecessary to explain that assessment in any detail
for dozens of emails. I don't think it's a healthy thing, especially
for a community where (traditionally) deprecation comes up so seldom.
I don't think this took place on the other deprecation threads so much
that spawned in this one's wake.

I can only offer the data I have. Please do not over-read in what I
have been trying to communicate, or conflate my position with those of
other individuals, if you feel there is a chance of that.

Let us please consider the matter resolved unless you feel you have
more pointers for me -- anyone can send them to me individually,
perhaps. I am not soldiering for the deprecation of RULES, but rather
the community's approach to but a suggestion of certain kinds of
change. Perhaps this is but a one-off, because discussion in the other
threads has been seemingly healthier.

--
fdr

On 10/18/2012 09:10 PM, Josh Berkus wrote:
> Daniel,
>
>> I'm not going to disagree with that, I only feel it's reasonable to
>> ask why those who react so strongly against deprecation why they think
>> what they do, and receive a clinical response, because not everyone
>> has seen those use cases. My level of interest in deprecation is only
>> as far as "if those who have to deal with the RULES implementation
>> don't want to work on it anymore in favor of other things, I think the
>> pain to users of deprecation is, from my vantage point, manageable if
>> given some time."
> Note that you have heard from one of the people maintaining RULES, who
> doesn't find them problematic to maintain (Tom). Note that the original
> hackers calling for deprecation do not work on RULEs except where they
> touch other features.

Just for kicks I decided to look and see how long ago 120 commits was on
each of the backend subdirectories. Here are the results:

[andrew(at)emma backend]$ for f in * ; do test -d $f && git log
--format="$f: %ci" $f | sed -n -e 1,120d -e 'p;q' ; done
access: 2012-02-21 14:14:16 -0500
bootstrap: 2004-10-10 23:37:45 +0000
catalog: 2011-06-16 12:11:20 -0400
commands: 2011-11-23 00:03:22 -0500
executor: 2010-02-20 21:24:02 +0000
libpq: 2009-08-29 19:26:52 +0000
main: 1998-04-06 00:32:26 +0000
nodes: 2010-01-01 23:03:10 +0000
optimizer: 2011-04-08 19:19:17 -0400
parser: 2011-03-08 16:43:56 -0500
po: 2003-10-04 22:50:20 +0000
port: 2006-10-13 13:59:47 +0000
postmaster: 2011-04-03 19:42:00 -0400
replication: 2011-01-10 21:53:18 +0100
rewrite: 2005-04-28 21:47:18 +0000
storage: 2011-07-08 18:44:07 +0300
tcop: 2009-12-07 05:22:23 +0000
tsearch: 2007-08-22 04:13:15 +0000
utils: 2012-04-20 23:56:57 -0300

As you can see, in the case of rewrite it takes us back 7 1/2 years. I
know this is a *very* rough measure, but it still tends to indicate to
me that the maintenance burden isn't terribly high.

cheers

andrew

On Fri, Oct 19, 2012 at 10:29 AM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> As you can see, in the case of rewrite it takes us back 7 1/2 years. I know
> this is a *very* rough measure, but it still tends to indicate to me that
> the maintenance burden isn't terribly high.

That's a pretty neat one-liner. However... in my view, the real cost
of rules is that they are hard to support as we add new features to
SQL. I believe we already decided to punt on making them work with
CTEs... and maybe one other case? I don't really remember the details
any more, but presumably this will come up again with MERGE, and
perhaps other cases...

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

> That's a pretty neat one-liner. However... in my view, the real cost
> of rules is that they are hard to support as we add new features to
> SQL. I believe we already decided to punt on making them work with
> CTEs... and maybe one other case? I don't really remember the details
> any more, but presumably this will come up again with MERGE, and
> perhaps other cases...

Unless the easiest way to implement MERGE is to extend RULEs.

Actually, I found myself wondering about RULEs and FDWs, for that
matter. There's not much synergy there now, but I can imagine RULEs
being used to do rewriting for funkier FDW setups, which would be hard
to do with TRIGGERs.

For example, imagine you have a series of CSV FDWs which relate to
segments of a postgres log. You want to query them like they were one
table. How would you use triggers to do that?

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

On 19 October 2012 22:03, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> Unless the easiest way to implement MERGE is to extend RULEs.

FWIW, I'd say that's probably about the hardest possible way to
implement MERGE, assuming that we prioritise providing robust UPSERT
support, as I strongly feel we should.

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

On Fri, Oct 19, 2012 at 5:03 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>> That's a pretty neat one-liner. However... in my view, the real cost
>> of rules is that they are hard to support as we add new features to
>> SQL. I believe we already decided to punt on making them work with
>> CTEs... and maybe one other case? I don't really remember the details
>> any more, but presumably this will come up again with MERGE, and
>> perhaps other cases...
>
> Unless the easiest way to implement MERGE is to extend RULEs.

The problems with MERGE are mostly around concurrency, as far as I can
tell. I can't see why RULEs would have anything to do with it -
except that I don't see how MERGE can sanely support rules, and even
if we find a way to make it do that, anyone already using RULEs will
need to adjust them to support MERGE. I'm not sure I have a horribly
well-thought-out position on the underlying issue here - I'm kind of
vacillating back and forth - but I do think one of the problems with
RULEs is that they are too tied to particular command names. Adding
any new commands that can select or modify data - be it MERGE, UPSERT,
or whatever - is going to cause trouble both for implementors and for
people relying on the feature.

> For example, imagine you have a series of CSV FDWs which relate to
> segments of a postgres log. You want to query them like they were one
> table. How would you use triggers to do that?

You wouldn't. You'd use a view, which of course is the only kind of
rules that are not being proposed for deprecation.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> The problems with MERGE are mostly around concurrency, as far as I can
> tell. I can't see why RULEs would have anything to do with it -
> except that I don't see how MERGE can sanely support rules, and even
> if we find a way to make it do that, anyone already using RULEs will
> need to adjust them to support MERGE. I'm not sure I have a horribly
> well-thought-out position on the underlying issue here - I'm kind of
> vacillating back and forth - but I do think one of the problems with
> RULEs is that they are too tied to particular command names. Adding
> any new commands that can select or modify data - be it MERGE, UPSERT,
> or whatever - is going to cause trouble both for implementors and for
> people relying on the feature.

And triggers (or anything else) would be better on that score because ...?

regards, tom lane

On Mon, Oct 22, 2012 at 8:57 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> The problems with MERGE are mostly around concurrency, as far as I can
>> tell. I can't see why RULEs would have anything to do with it -
>> except that I don't see how MERGE can sanely support rules, and even
>> if we find a way to make it do that, anyone already using RULEs will
>> need to adjust them to support MERGE. I'm not sure I have a horribly
>> well-thought-out position on the underlying issue here - I'm kind of
>> vacillating back and forth - but I do think one of the problems with
>> RULEs is that they are too tied to particular command names. Adding
>> any new commands that can select or modify data - be it MERGE, UPSERT,
>> or whatever - is going to cause trouble both for implementors and for
>> people relying on the feature.
>
> And triggers (or anything else) would be better on that score because ...?

Well, my thought was that a trigger - at least a row-level trigger -
can presumably be fired on the basis of whether an individual row is
being insert or updated, rather than on whether the statement is named
INSERT or UPDATE. If that's not correct, we've got some
head-scratching to do...

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Fri, Oct 19, 2012 at 2:55 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Fri, Oct 19, 2012 at 10:29 AM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>> As you can see, in the case of rewrite it takes us back 7 1/2 years. I know
>> this is a *very* rough measure, but it still tends to indicate to me that
>> the maintenance burden isn't terribly high.
>
> That's a pretty neat one-liner. However... in my view, the real cost
> of rules is that they are hard to support as we add new features to
> SQL. I believe we already decided to punt on making them work with
> CTEs... and maybe one other case? I don't really remember the details
> any more, but presumably this will come up again with MERGE, and
> perhaps other cases...

Good point on the CTE (and it's correct). I think by any reasonable
definition rules are in fact already de facto deprecated: they are not
being extended to interact with other features and the community is
advising against their use. I don't think anybody would complain
if/when a hypothetical MERGE feature was advanced without rule
interaction.

That said, I don't think there is any reasonable argument to remove
rules. Backwards compatibility should only be broken when it *must*
be broken. Any 'developer interest only' standards ('grotty code',
'inelegant', 'ill advised for new code', etc) of removal are
completely specious and thus are IMSNHO irrelevant.

merlin

> -----Original Message-----
> From: pgsql-hackers-owner(at)postgresql(dot)org [mailto:pgsql-hackers-
> owner(at)postgresql(dot)org] On Behalf Of Merlin Moncure
> Sent: Monday, October 22, 2012 6:54 PM
> To: Robert Haas
> Cc: Andrew Dunstan; Josh Berkus; Daniel Farina; pgsql-
> hackers(at)postgresql(dot)org
> Subject: Re: [HACKERS] Deprecating RULES
> >
> Good point on the CTE (and it's correct). I think by any reasonable
definition
> rules are in fact already de facto deprecated: they are not being extended
to
> interact with other features and the community is advising against their
use.
> I don't think anybody would complain if/when a hypothetical MERGE feature
> was advanced without rule interaction.
>
> That said, I don't think there is any reasonable argument to remove rules.
> Backwards compatibility should only be broken when it *must* be broken.
> Any 'developer interest only' standards ('grotty code', 'inelegant', 'ill
advised
> for new code', etc) of removal are completely specious and thus are IMSNHO
> irrelevant.
>
> merlin

While I agree with this sentiment to some degree in order for the community
to thrive new developer blood needs to be introduced periodically. Not that
this feature is particularly an issue but making the codebase easier to
learn and maintain has considerable value in its own right.

To put a different spin on things it is like CREATE RULE is a specialty
tool. Taken that way we should strictly describe the uses-cases where
CREATE RULE behavior is well-defined and problem free. If the end-user
isn't trying to use RULEs in exactly those cases then they are advised to
attempt another solution or send an e-mail to the list to get some expert
opinions on that particular use-case. Known problematic uses can also be
listed to minimize the amount of "not listed, what do y'all think" e-mails
sent to the list. In this setup there is some developer obligation to try
and not break those "well-defined" use-cases; but that exists today even if
it is not explicitly mentioned.

David J.

Darren Duncan <darren(at)darrenduncan(dot)net> writes:
> I have a proposal.

> Assuming we decide to do away with RULEs,

You lost me already.

If we had replacement functionality for everything that can be done with
rules, we could start to think about when we might begin to tell people
they can't use rules. Unfortunately, we don't have that precondition.

regards, tom lane

On 2013.08.27 7:57 PM, Tom Lane wrote:
> Darren Duncan <darren(at)darrenduncan(dot)net> writes:
>> I have a proposal.
>
>> Assuming we decide to do away with RULEs,
>
> You lost me already.
>
> If we had replacement functionality for everything that can be done with
> rules, we could start to think about when we might begin to tell people
> they can't use rules. Unfortunately, we don't have that precondition.
>
> regards, tom lane

That's a really old post/thread, and I'm not arguing for any kind of action
related to RULEs, please disregard the message. -- Darren Duncan

Darren Duncan <darren(at)darrenduncan(dot)net> writes:
> That's a really old post/thread, and I'm not arguing for any kind of action
> related to RULEs, please disregard the message. -- Darren Duncan

Oh, my fault --- for some reason my mail reader popped it up as an unread
message, and I failed to notice the date. My apologies.

regards, tom lane