Thread: BUG #5042: Update numeric within a rule

BUG #5042: Update numeric within a rule

From

"Ilian Georgiev"

Date:

06 September 2009, 20:55:09

The following bug has been logged online:

Bug reference:      5042
Logged by:          Ilian Georgiev
Email address:      georgiev.ilian@gmail.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

Re: BUG #5042: Update numeric within a rule

From

Robert Haas

Date:

16 September 2009, 01:11:42

On Sun, Sep 6, 2009 at 4:48 PM, Ilian Georgiev <georgiev.ilian@gmail.com> w=
rote:
>
> The following bug has been logged online:
>
> Bug reference: =A0 =A0 =A05042
> Logged by: =A0 =A0 =A0 =A0 =A0Ilian Georgiev
> Email address: =A0 =A0 =A0georgiev.ilian@gmail.com
> PostgreSQL version: 8.1.10
> Operating system: =A0 Windows XP
> Description: =A0 =A0 =A0 =A0Update 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 (
> =A0video_sid SERIAL PRIMARY KEY,
> =A0category_sid int NOT NULL REFERENCES category ON UPDATE RESTRICT ON DE=
LETE
> RESTRICT,
> =A0url varchar(32) NOT NULL,
> =A0user_sid int NOT NULL REFERENCES "user" ON UPDATE RESTRICT ON DELETE
> RESTRICT,
> =A0 =A0 =A0 =A0image_sid int REFERENCES image,
> =A0 =A0 =A0 =A0creation_datetime timestamp NOT NULL DEFAULT NOW(),
> =A0 =A0 =A0 =A0view_count int NOT NULL DEFAULT 0,
> =A0 =A0 =A0 =A0comment_count int NOT NULL DEFAULT 0,
> =A0 =A0 =A0 =A0rating numeric(4,2) NOT NULL DEFAULT 0,
> =A0 =A0 =A0 =A0rating_percent int NOT NULL DEFAULT 0,
> =A0 =A0 =A0 =A0votes int NOT NULL DEFAULT 0,
> =A0 =A0 =A0 =A0is_published boolean NOT NULL DEFAULT false,
> =A0 =A0 =A0 =A0title varchar(128) NOT NULL,
> =A0 =A0 =A0 =A0description 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()
> =A0RETURNS "trigger" AS
> $BODY$
> =A0 =A0 =A0 =A0BEGIN
>
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0IF (NEW.rating!=3D0) THEN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0NEW.rating_percent :=3D ((=
NEW.rating / 5 ) * 100)::int;
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0END IF;
>
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0RETURN NEW;
> =A0 =A0 =A0 =A0END;
> $BODY$
> =A0LANGUAGE 'plpgsql' VOLATILE;
>
> GRANT EXECUTE ON FUNCTION update_rating_percent() TO web;
>
> CREATE TRIGGER update_rating_percent_trg
> =A0BEFORE UPDATE
> =A0ON video
> =A0FOR EACH ROW
> =A0EXECUTE PROCEDURE update_rating_percent();
>
> and table with votes :
>
> CREATE TABLE video_vote (
> =A0video_sid int NOT NULL REFERENCES video ON UPDATE CASCADE ON DELETE
> CASCADE,
> =A0 =A0 =A0 =A0ip_address inet NOT NULL,
> =A0 =A0 =A0 =A0rate int NOT NULL CHECK (rate > 0 AND rate < 6),
> =A0 =A0 =A0 =A0creation_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
> =A0 =A0 =A0 =A0video_vote_insert_rule
> AS ON INSERT TO
> =A0 =A0 =A0 =A0video_vote
> DO ALSO
> =A0 =A0 =A0 =A0UPDATE
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0video
> =A0 =A0 =A0 =A0SET
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0votes =3D votes + 1,
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0rating =3D (( SELECT
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0SUM(=
rate)::numeric
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 FROM
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0vide=
o_vote
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 WHERE
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0vide=
o_sid =3D NEW.video_sid
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0) / (votes + 1) )::numeric
> =A0 =A0 =A0 =A0WHERE
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0video_sid =3D 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
> =A0 =A0 =A0 =A0video_vote_insert_rule
> AS ON INSERT TO
> =A0 =A0 =A0 =A0video_vote
> DO ALSO
> =A0 =A0 =A0 =A0UPDATE
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0video
> =A0 =A0 =A0 =A0SET
>
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0rating =3D 2.95
> =A0 =A0 =A0 =A0WHERE
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0video_sid =3D 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=3D# 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=3D# 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=3D# create rule bar_rule as on insert to bar do update foo set
rating =3D 2.95;
CREATE RULE
rhaas=3D# insert into foo values (default, '3.67');
INSERT 0 1
rhaas=3D# insert into bar values (default);
INSERT 0 1
rhaas=3D# select * from foo;
 id | rating
----+--------
  1 |   2.95
(1 row)

> When I do :
>
> update
> =A0video
> set
> =A0rating =3D 2.95
> where
> =A0video_sid =3D 1;
>
> Then the result is expected =3D 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