On Sun, Sep 6, 2009 at 4:48 PM, Ilian Georgiev <georgiev.il...@gmail.com> wrote: > > The following bug has been logged online: > > Bug reference: 5042 > Logged by: Ilian Georgiev > Email address: georgiev.il...@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 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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs