Re: BUG #5042: Update numeric within a rule - Mailing list pgsql-bugs
From | Robert Haas |
---|---|
Subject | Re: BUG #5042: Update numeric within a rule |
Date | |
Msg-id | 603c8f070909151811l2563de31jba18bd814faeb1f4@mail.gmail.com Whole thread Raw |
In response to | BUG #5042: Update numeric within a rule ("Ilian Georgiev" <georgiev.ilian@gmail.com>) |
List | pgsql-bugs |
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
pgsql-bugs by date: