What about improving the rules system we have, was Re: Rules going away - Mailing list pgsql-general

On Wed, Sep 28, 2011 at 7:34 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> I think the true state of affairs is this: rules have a lot of
> surprising behaviors, and if we could think of something that works more
> straightforwardly, we'd love to replace them.  But I think we'd have to
> have the "something" in place before we consider deprecating rules.
> At the moment we don't even have a glimmer of a design, so David's
> statement is many years premature.
>
>
I guess my question is what it would take to eliminate some of the
very surprising behaviors.  It seems to me that the cases of
surprising behaviors are actually pretty limited (moreso than table
inheritance for example).  The only surprises I can think of come with
DO ALSO rules which involve expressions which should not be
re-executed (nextval being a good example).  DO INSTEAD rules work
extremely well, IMO, and I can't think of any cases where there
surprises which are particularly easy to get bitten by there.  It
seems to me that a lot of problems with DO ALSO rules are issues which
happen when lexical ambiguity hits implementation (do you mean DO ALSO
nextval() again or take the value from the previous nextval() and feed
it into this rule?).

Instead of considering deprecating and replacing rules with something
else, I guess I am wondering if the issues re fundamental or issues of
implementation.  It seems to me we might get more mileage out of
pondering a Rules 2.0 approach, discussing the specifications, etc.
that we would in looking for an alternative.

I figure if we start this discussion then folks may have a basis for
considering alternatives down the line.  If we never discuss it
however, then folks who want something more predictable and
straightforward have nothing to consider.

So I guess I'd start by asking the community a couple of questions:
1)  Are there surprising behaviors in DO INSTEAD rules?
2)  Should we assume that there are cases dependent on existing behaviors?

If it were up to me I would change the rules spec in a couple of
clearly defined ways and then look at how to tweak what we have to
implement those changes.  In particular I would:

1)  Redefine NEW so that it is post-initial-expression evaluation and
therefore remains constant at the input of the rule's query plan.
2)  Add a new IN tuple which is pre-initial-expression evaluation and
therefore does not stay constant as a guarantee.

So for example, suppose I have the following tables which are used to
stage data coming into an accounting system.

CREATE TABLE my_transactions1 (id int not null unique default
nextval('my_transactions1_id_seq'),
post_on date not null,
credit_account int not null references credit_accounts(id),
source_id text not null unique,
total_amount numeric);

CREATE TABLE my_transactions2 (id int not null unique default
nextval('my_transactions1_id_seq'),
post_on date not null,
credit_account int not null references credit_accounts(id),
source_id text not null unique,
total_amount numeric);

If I:
CREATE RULE insert_deduction AS ON INSERT TO my_transactions1
DO ALSO INSERT INTO my_transactions2 (id, post_on, credit_account,
source_id, total_amount)
VALUES (IN.id, get_deduction_account(NEW.credit_account),
get_deduction_source(NEW.source), NEW.total_amount * -1);

Then I would expect nextval() to be executed twice, while NEW would
behave as it does in triggers.

I don't know how feasible it is to implement such a thing, and
certainly it would break backwards compatibility for at least some
users.  But I don't think it would be any worse than outright
replacing the rules system.

Best Wishes,
Chris Travers