Обсуждение: rules: evaluate inputs in advance

Hi,

I was trying to create an updateable view. Suddenly I got foreign key
violations when using nextval('myseq').

As I understand, the rewriter does something similar to a simple text
replacement (I guess copying the plan tree nodes?) so that nextval gets
evaluated again for every rule that applies.

Is this desirable? Are there applications which need every rule to
reevaluate the input expressions? Or could that behaviour be changed so
that the input values for the rules get evaluated only once?

A simple example:

test=# CREATE TABLE test (id SERIAL PRIMARY KEY);
test=# CREATE VIEW view AS SELECT id FROM test;
test=# CREATE RULE rule1 AS ON INSERT TO view DO INSTEAD \
         INSERT INTO test (id) VALUES (NEW.id);
test=# CREATE RULE rule2 AS ON INSERT TO view DO ALSO \
         DELETE FROM test WHERE id = NEW.id;
test=# INSERT INTO view (id) VALUES (1);
test=# SELECT id FROM test;
 id
----
(0 rows)    -- that's what I was expecting...

test=# INSERT INTO view (id) VALUES (nextval('test_id_seq'));
test=# SELECT id FROM test;
 id
----
  1
(1 row)    -- this happens because rule2 evaluated nextval(..) again

test=# SELECT currval('test_id_seq');
 currval
---------
       2
(1 row)    -- confirming my observation.


Regards

Markus

On Fri, May 12, 2006 at 03:51:18PM +0200, Markus Schiltknecht wrote:
> Hi,
>
> I was trying to create an updateable view. Suddenly I got foreign key
> violations when using nextval('myseq').
>
> As I understand, the rewriter does something similar to a simple text
> replacement (I guess copying the plan tree nodes?) so that nextval gets
> evaluated again for every rule that applies.
>
> Is this desirable? Are there applications which need every rule to
> reevaluate the input expressions? Or could that behaviour be changed so
> that the input values for the rules get evaluated only once?

It's a known problem. It's also one of the reasons why triggers are
recommended over rules. And it's not desirable behaviour.

There have been discussions about the problems with the rule system and
how it seems to fail on simple ideas. Yet no-one has come up with an
alternative that covers the current uses...

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

On Fri, 2006-05-12 at 15:57 +0200, Martijn van Oosterhout wrote:
> It's a known problem. It's also one of the reasons why triggers are
> recommended over rules. And it's not desirable behaviour.

Well, triggers cannot be used to create writeable views, can they?

> There have been discussions about the problems

Do you have some pointers here? I did not find relevant discussions in
the archives (of gmane.org)

> Yet no-one has come up with an
> alternative that covers the current uses...

As far as I know, the rewriter simply copies plan tree nodes. I thought
about inserting an 'evaluate-only-once' node which is linked from all
rules and points to the expression to evaluate (instead of copying the
complete explession and evaluating it several times in the executor).

Regards

Markus

> Well, triggers cannot be used to create writeable views, can they?

The documentation says that triggers can be used on views but only for Inserts; not updates or
deletes.

http://www.postgresql.org/docs/8.1/interactive/rules-triggers.html

Regards,

Richard

On Fri, May 12, 2006 at 04:22:00PM +0200, Markus Schiltknecht wrote:
> > There have been discussions about the problems
>
> Do you have some pointers here? I did not find relevant discussions in
> the archives (of gmane.org)

Hmm, maybe not so much discussions as comments from developers when the
Nth problem with rules comes in a week. I remember a few but can't find
them in the archives.

In cases where rules work well, like views, they work really well. And
for simple updatable views they work too. But when you try to do
something complex you get hung up on things like nextval() being
evaluated multiple times.

I seem to remember Tom suggesting that for updatable views it'd
probably be nicer to be able to work it via an interface that looks
more like triggers. That way it would be clearer and you don't suffer
the problems related to multiple evaluations of parameters.
Unfortunatly, I havn't come up with something that could work.

> As far as I know, the rewriter simply copies plan tree nodes. I thought
> about inserting an 'evaluate-only-once' node which is linked from all
> rules and points to the expression to evaluate (instead of copying the
> complete explession and evaluating it several times in the executor).

But it can't really. In the example that started this thread, there are
two seperate rules and after rewriting the executor will be presented
two seperate queries. If you were dealing with only one row you could
change the second query to use currval() but that breaks if there are
more rows.

What you probably want is a function that is given the row and then
executes the two statements on a per row basis. This has the effect you
want but gives up the major benefit of rules, wholesale query
restructuring like views which allows the executor to find better
plans. The executor can't see inside a trigger so it can't optimise.

There are half-baked ideas, but I have no idea if they're better than
what we have now...

Have a ncie day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Hi Martijn,

On Fri, 2006-05-12 at 18:05 +0200, Martijn van Oosterhout wrote:
> But it can't really. In the example that started this thread, there are
> two seperate rules and after rewriting the executor will be presented
> two seperate queries.

Ah, thank you, that explains the difficulties with rules.

> What you probably want is a function that is given the row and then
> executes the two statements on a per row basis. This has the effect you
> want but gives up the major benefit of rules, wholesale query
> restructuring like views which allows the executor to find better
> plans. The executor can't see inside a trigger so it can't optimise.

Isn't that an argument for keeping rewrite rules instead of using
something trigger like for updatable views? Wouldn't it be feasible to
teach the executor how to handle multiple queries with some
pre-evaluated input?

Regards

Markus

On Sun, May 14, 2006 at 08:01:39AM +0200, Markus Schiltknecht wrote:
> > What you probably want is a function that is given the row and then
> > executes the two statements on a per row basis. This has the effect you
> > want but gives up the major benefit of rules, wholesale query
> > restructuring like views which allows the executor to find better
> > plans. The executor can't see inside a trigger so it can't optimise.
>
> Isn't that an argument for keeping rewrite rules instead of using
> something trigger like for updatable views? Wouldn't it be feasible to
> teach the executor how to handle multiple queries with some
> pre-evaluated input?

Well, I notice that the SQL standard defines something called WITH, so
what you want is something like:

WITH OLD AS ( SELECT blah )
DO
( UPDATE <rule1>
; DELETE <rule2> )

The standard doesn't allow you apply one WITH clause to two statements
but that is what you want. The implementation though would be, well,
difficult...

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Martijn van Oosterhout <kleptog@svana.org> writes:
> Well, I notice that the SQL standard defines something called WITH, so
> what you want is something like:

> WITH OLD AS ( SELECT blah )
> DO
> ( UPDATE <rule1>
> ; DELETE <rule2> )

I think it'd be a mistake to assume that WITH would fix Markus'
complaint.  I haven't studied the spec in detail but I think that WITH
acts largely like a macro too.  It certainly doesn't have the behavior
of "evaluate this just once", as one of the prime uses for it is in
recursive query definition.

            regards, tom lane