Re: BUG #1175: insert rule action with defaults

On Mon, Jun 21, 2004 at 10:14:53PM -0400, Tom Lane wrote:
> "PostgreSQL Bugs List" <pgsql-bugs(at)postgresql(dot)org> writes:
> > [ double evaluation of serial-column default with ]
> >
> > create rule silly_insert_r as on insert to silly do
> > insert into action (item_id,whence)
> > values (new.id,'now');
>
> The standard answer to this is "use a trigger, not a rule, to copy
> inserted or updated data to another table". A rule is basically a
> macro and as such has the usual multiple-evaluation hazards that
> all C programmers are familiar with :-(. There are times when this
> behavior is just what you want, but not when trying to copy the
> results of volatile expressions.
>
> People are often scared away from triggers because of the apparent
> notational complexity. This is too bad, because conceptually triggers
> are *much* simpler than rules. Someday I'd like to rewrite the docs
> so that triggers are discussed first and made to look like the simpler
> facility ...
>
> regards, tom lane

Tom,

Thanks for the quick response. Your answer was pretty much
what I had expected. I was drifting towards triggers as a more
controllable (and standard (?)) means to deal with this issue.

But from an aesthetic point of view (aesthetic database - now
there's a term you don't hear too often) it seems to me that what is
happening conceptually is that the default is being treated as the macro
in this case and not the rule. As soon as I make a reference to new.id in
a rule, that ought to fix the column to the default. But what is happening
is that the instructions for setting new.id being carried forward into
the rule. This is more of a conceptual quibble I have at this point.

I think the standard answer should be "do not use serial columns
in any insert rule". I can see problems in cases other than copying
inserted data to another table with rules.

thanks for the good work,

joe