Re: BUG #5042: Update numeric within a rule

On Sun, Sep 6, 2009 at 4:48 PM, Ilian Georgiev <georgiev(dot)ilian(at)gmail(dot)com> wrote:
>
> 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 think what is happening here is that you are dividing two integers
(rate is an integer, therefore sum(rate) is an integer, and votes is
an integer, therefore votes + 1 is an integer), so you're getting an
integer result. You then cast that result to a numeric, but by that
point you've already thrown away the remainder. If you divide by
votes::numeric + 1 instead of votes + 1 you'll probably get a
different answer.

> 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 .

I find this just about impossible to believe. I just tried it with a
self-contained test case and it worked fine (see below). It's pretty
hard to believe that there could be a bug that makes numeric division
truncate to the nearest integer, but only when used from within a
rule. I think it's more likely that you made a mistake somewhere in
the process of carrying out this experiment.

rhaas=# create table foo (id serial primary key, rating numeric(4,2)
not null default 0);
NOTICE: CREATE TABLE will create implicit sequence "foo_id_seq" for
serial column "foo.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"foo_pkey" for table "foo"
CREATE TABLE
rhaas=# create table bar (id serial primary key);
NOTICE: CREATE TABLE will create implicit sequence "bar_id_seq" for
serial column "bar.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"bar_pkey" for table "bar"
CREATE TABLE
rhaas=# create rule bar_rule as on insert to bar do update foo set
rating = 2.95;
CREATE RULE
rhaas=# insert into foo values (default, '3.67');
INSERT 0 1
rhaas=# insert into bar values (default);
INSERT 0 1
rhaas=# select * from foo;
id | rating
----+--------
1 | 2.95
(1 row)

> When I do :
>
> update
>  video
> set
>  rating = 2.95
> where
>  video_sid = 1;
>
> Then the result is expected = 2.95

I'm not at all surprised by this result. :-)

By the by, I think that the way you are implementing this is not
MVCC-safe. You probably should store the sum of the ratings and the
count of votes in the video table, and do the division when you select
from that table. I think the way you have it there might be a
possibility of the wrong average rating being stored in the face of
concurrent inserts to video_vote.

...Robert