BUG #5042: Update numeric within a rule

The following bug has been logged online:

Bug reference: 5042
Logged by: Ilian Georgiev
Email address: georgiev(dot)ilian(at)gmail(dot)com
PostgreSQL version: 8.1.10
Operating system: Windows XP
Description: Update numeric within a rule
Details:

Hello I have a sutuation where I can do update on numeric column with a
signle update but when this update statement is in a rule it doesn't wotk
properly.
Look :

I have a table with videos :

CREATE TABLE video (
video_sid SERIAL PRIMARY KEY,
category_sid int NOT NULL REFERENCES category ON UPDATE RESTRICT ON DELETE
RESTRICT,
url varchar(32) NOT NULL,
user_sid int NOT NULL REFERENCES "user" ON UPDATE RESTRICT ON DELETE
RESTRICT,
image_sid int REFERENCES image,
creation_datetime timestamp NOT NULL DEFAULT NOW(),
view_count int NOT NULL DEFAULT 0,
comment_count int NOT NULL DEFAULT 0,
rating numeric(4,2) NOT NULL DEFAULT 0,
rating_percent int NOT NULL DEFAULT 0,
votes int NOT NULL DEFAULT 0,
is_published boolean NOT NULL DEFAULT false,
title varchar(128) NOT NULL,
description text
);

GRANT INSERT, UPDATE, SELECT ON video TO web;
GRANT SELECT, UPDATE ON video_video_sid_seq TO web;

COMMENT ON TABLE video IS 'Holds video desctiptions';

CREATE OR REPLACE FUNCTION update_rating_percent()
RETURNS "trigger" AS
$BODY$
BEGIN

IF (NEW.rating!=0) THEN
NEW.rating_percent := ((NEW.rating / 5 ) * 100)::int;
END IF;

RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

GRANT EXECUTE ON FUNCTION update_rating_percent() TO web;

CREATE TRIGGER update_rating_percent_trg
BEFORE UPDATE
ON video
FOR EACH ROW
EXECUTE PROCEDURE update_rating_percent();

and table with votes :

CREATE TABLE video_vote (
video_sid int NOT NULL REFERENCES video ON UPDATE CASCADE ON DELETE
CASCADE,
ip_address inet NOT NULL,
rate int NOT NULL CHECK (rate > 0 AND rate < 6),
creation_datetime timestamp NOT NULL DEFAULT NOW()
);

GRANT INSERT, UPDATE, DELETE, SELECT ON video_vote TO web;

COMMENT ON TABLE video_vote IS 'Votes of every user by IP address';

CREATE UNIQUE INDEX video_vote_ukey ON video_vote (video_sid, ip_address);

with a rule connected to the video table :

CREATE OR REPLACE RULE
video_vote_insert_rule
AS ON INSERT TO
video_vote
DO ALSO
UPDATE
video
SET
votes = votes + 1,
rating = (( SELECT
SUM(rate)::numeric
FROM
video_vote
WHERE
video_sid = NEW.video_sid
) / (votes + 1) )::numeric
WHERE
video_sid = NEW.video_sid;

now when I do simple update on video it gets the right value for scale.But
when I do insert on video_vote and this do update on video table I got .00
for scale.

I even changed my rule to :

CREATE OR REPLACE RULE
video_vote_insert_rule
AS ON INSERT TO
video_vote
DO ALSO
UPDATE
video
SET

rating = 2.95
WHERE
video_sid = NEW.video_sid;

The result in rating column was 2.00 .
When I do :

update
video
set
rating = 2.95
where
video_sid = 1;

Then the result is expected = 2.95