Re: postgresql rule bug

Coranth Gryphon <gryphon(at)healer(dot)com> writes:
> Hi. From the contributors list, you seemed to be the most likely
> people to send a postgresql bug report.

There is a bugs list, which is the appropriate place.

> When using a RULE that triggers off an INSERT on a table whose
> primary key is a SERIAL, every time the rule accesses the new
> serial id in the body of the rule, the serial number increments.

Wouldn't surprise me. A rule is basically like a macro --- it's
expanded with the original inputs substituted in each appropriate place.
This is either a bug or a feature depending on what sort of example you
are looking at ...

In this particular case it does seem rather annoying, since there's
no obvious way to get the behavior you're after. I dunno if we
can do anything about it in the near future though. Jan, this is
your turf --- what do you think?

regards, tom lane

> Here is an example of the problem.

> CREATE TABLE T_Comments
> (
> id_note SERIAL,
> txtnote VARCHAR(80) NOT NULL,
> PRIMARY KEY (id_note)
> );

> CREATE TABLE T_CommentHistory
> (
> id_note INT4 NOT NULL, /* FK to Comments */
> dt_act ABSTIME NOT NULL,
> action CHAR(6) NOT NULL
> PRIMARY KEY (id_note, dt_act)
> );

> CREATE RULE T_CommentCreation
> AS ON INSERT TO T_Comments
> DO INSERT INTO T_CommentHistory
> VALUES (new.id_note, CURRENT_TIMESTAMP, 'create')
> ;

> INSERT INTO T_Comments (txtnote) VALUES ('This is a test')
> ;

> SELECT * FROM T_Comments;

> id_note | txtnote
> --------+--------------
> 2 | This is a test

> SELECT * FROM T_CommentHistory;

> id_note | dt_act | actions
> --------+------------------------------+-------
> 1 | Tue Apr 11 11:40:47 2000 GMT | create

> Or am I doing something wrong in the rule?

> -coranth

> PS> Thanx in advance for your time and for helping
> to create such a great piece of software.