Re: postgresql rule bug
Coranth Gryphon <gryphon@healer.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
Show quoted text
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.
Import Notes
Reply to msg id not found: 38F311E9.147B0187@healer.comReference msg id not found: 38F311E9.147B0187@healer.com
Tom Lane wrote:
Coranth Gryphon <gryphon@healer.com> writes:
CREATE RULE T_CommentCreation
AS ON INSERT TO T_Comments
DO INSERT INTO T_CommentHistory
VALUES (new.id_note, CURRENT_TIMESTAMP, 'create')
Untested suggestion -
Try using currval('T_Comments_id_note_seq') instead.
--
Mark Hollomon
mhh@nortelnetworks.com
ESN 451-9008 (302)454-9008
Import Notes
Reference msg id not found: 38F311E9.147B0187@healer.com
Tom Lane wrote:
Coranth Gryphon <gryphon@healer.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.
It's not a bug, it's a feature :-)
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.
Absolutely right, and I don't think we should/could fix it.
Only a trigger will do it correctly in this case.
The rule expands to a separate query, which is ran BEFORE the
one sent to the backend. First of all, there's no guarantee
that this additional query will produce the same number of
result rows (inserts) as the original one.
Consider the case that someone does an INSERT ... SELECT to
create new T_Comments. Further take into account that the
rule could have some qualification and only some of the
comments cause additional information in T_CommentHistory
(where txtnote != '').
Now at the time the INSERT for the history is executed, the
system doesn't even know HOW MANY rows the original query
will produce. How should it preallocate the serials and
remember which ones to use when running the original query?
Forget it and go for a trigger.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #