Re: tables referenced from insert...returning

"Michael Shulman" <shulman(at)mathcamp(dot)org> writes:
> On Mon, Jun 23, 2008 at 8:46 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Multiple evaluations of NEW in the text of a rule are a great way
>> to cause yourself trouble --- consider what happens if there's
>> a volatile function such as nextval() involved.

> Ouch! I didn't realize that multiple references to NEW were actually
> translated by the rule system into multiple *evaluations* of the
> supplied arguments. Are there reasons one might desire that behavior?

Well, the rule system is fundamentally a macro-expansion mechanism,
and multiple-evaluation risks come with that territory. There are
things you can do with macro expansion that can't be done any other
way, so I don't think that that decision was wrong on its face, but
certainly we've seen plenty of traps for the unwary in it.

I've occasionally wondered what a "rule system mark II" might look
like, but frankly I have no idea how to design one that has useful
functionality and fewer traps.

> I can think of simple situations in which one would *not* want such
> multiple evaluation. For example, a rule on table1 which logs all
> modifications of table1 to table1_log would be naturally written as

> CREATE RULE log AS ON INSERT TO table1 DO ALSO
> INSERT INTO table1_log (new_value,...) VALUES (NEW.value,...);

Except that NEW.* doesn't necessarily have any relationship at all to
what was actually put into table1 --- it'll just be the values that were
provided to the original "INSERT INTO view" command. Your own example
showed the difference. This sort of logging application is *far* better
served by triggers.

Perhaps the grail we're looking for is a rule-like syntax for defining
what are really triggers. Not sure though...

regards, tom lane