Thread: BUG #1175: insert rule action with defaults

The following bug has been logged online:

Bug reference:      1175
Logged by:          Joe Carlson

Email address:      joe@fruitfly.org

PostgreSQL version: 7.3.2

Operating system:   linux

Description:        insert rule action with defaults

Details:

I realize this was discussed 2 years ago; the issue is
how to deal with default columns in rule-based actions.

The issue I have is tracking inserts or updates to a
collection of tables using a rule which will insert a
transaction record for that operation.

This illustrates the behavior:
create table silly (id serial, item varchar(20));
create table action (item_id integer, whence datetime);

create rule silly_insert_r as on insert to silly do
insert into action (item_id,whence)
values (new.id,'now');

insert into silly (item) value ('first');

select * from silly;
 id | item
----+-------
  2 | first
(1 row)

select * from action;
 item_id |         whence
---------+------------------------
       1 | 2004-06-21 16:24:51-07
(1 row)

So, in the rule expansion, the default column for id
is getting evaluation in a call to nextval, but this
value is not being used in the insert to silly.
One (namely, I) would like to have the same id values
without the second call to nextval.

I realize there are different ways to implement this
behavior as a workaround; but these become quite
complex in my real application. It would be good to
have the rule expansion happen after default columns
were identified and set rather than being specified
in separate operations.

"PostgreSQL Bugs List" <pgsql-bugs@postgresql.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

On Mon, Jun 21, 2004 at 10:14:53PM -0400, Tom Lane wrote:
> "PostgreSQL Bugs List" <pgsql-bugs@postgresql.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