Received: from femail42.sdc1.sfba.home.com (femail42.sdc1.sfba.home.com [24.254.60.36]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f8N79pe04102 for ; Sun, 23 Sep 2001 03:09:55 -0400 (EDT) (envelope-from kway@overtone.org) Received: from bean.overtone.org ([24.249.254.100]) by femail42.sdc1.sfba.home.com (InterMail vM.4.01.03.20 201-229-121-120-20010223) with ESMTP id <20010923070946.TQEQ10152.femail42.sdc1.sfba.home.com@bean.overtone.org> for ; Sun, 23 Sep 2001 00:09:46 -0700 Received: by bean.overtone.org (Postfix, from userid 1001) id 26D7B5B4BF; Sun, 23 Sep 2001 07:09:46 +0000 (GMT) Date: Sun, 23 Sep 2001 07:09:46 +0000 From: Kevin Way To: pgsql-sql@postgresql.org Subject: confounding, incorrect constraint error Message-ID: <20010923070945.A39397@bean.overtone.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.2.5i X-Archive-Number: 200109/193 X-Sequence-Number: 4875 I've hit some really evil nastiness that is either a Postgres 7.1.3 bug, or signs of early-onset senility for me. I was having trouble with my database dying while inserting some values, and running some PL/pgSQL. The schema is as listed below, and I'm getting psql:fuck.sql:175: ERROR: ExecReplace: rejected due to CHECK constraint users_logged_in while inserting values into the uservote table. If I had a few columns to the users table, postgres crashes instead of giving this (nonsensical) error. I'd greatly appreciate any insight, even if it involves a 2x4. Below is a significantly simplified version of my schema, which exhibits the above problem. DROP RULE uservote_update_item_mod; DROP RULE uservote_delete_item_dec; DROP RULE uservote_insert_item_inc; DROP RULE itemvote_update_item_mod; DROP RULE itemvote_delete_item_dec; DROP RULE itemvote_insert_item_inc; DROP FUNCTION mod_node_vote_count(INT4, INT2, INT2); DROP TABLE uservote; DROP TABLE itemvote; DROP TABLE item; DROP TABLE users; DROP TABLE node; DROP SEQUENCE node_id_seq; CREATE SEQUENCE node_id_seq; CREATE TABLE node ( node_id INT4 UNIQUE NOT NULL DEFAULT nextval('node_id_seq'), name TEXT NOT NULL, nays INT4 NOT NULL DEFAULT 0 CHECK ( nays >= 0 ), yays INT4 NOT NULL DEFAULT 0, CHECK ( yays >= 0 ), rating INT2 NOT NULL DEFAULT 50 CHECK ( rating >= 0 AND rating <= 100 ), PRIMARY KEY (node_id) ); CREATE TABLE users ( node_id INT4 UNIQUE NOT NULL, email TEXT NOT NULL, realname TEXT NOT NULL, pass_hash VARCHAR(32) NOT NULL, logged_in INT2 NOT NULL DEFAULT 0 CHECK (logged_in = 0 OR logged_in = 1) ) INHERITS (node); CREATE TABLE item ( node_id INT4 UNIQUE NOT NULL, creator_id INT4 NOT NULL REFERENCES users (node_id) ON DELETE CASCADE ON UPDATE CASCADE, reason TEXT NOT NULL ) INHERITS (node); CREATE TABLE itemvote ( vote_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, target_id INT4 NOT NULL REFERENCES item (node_id) ON DELETE CASCADE ON UPDATE CASCADE, user_id INT4 NOT NULL REFERENCES users (node_id) ON DELETE CASCADE ON UPDATE CASCADE, nays INT2 NOT NULL CHECK (nays = 0 OR nays = 1), PRIMARY KEY (user_id, target_id) ); CREATE TABLE uservote ( vote_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, target_id INT4 NOT NULL REFERENCES users (node_id) ON DELETE CASCADE ON UPDATE CASCADE, user_id INT4 NOT NULL REFERENCES users (node_id) ON DELETE CASCADE ON UPDATE CASCADE, nays INT2 NOT NULL CHECK (nays = 0 OR nays = 1), PRIMARY KEY (user_id, target_id) ); -- modifies an items nays/yays count totals as appropriate -- first arg: item number -- second arg: 1 or 0, nays or yays. -- third arg: 1 or 0, add a vote, or remove a vote CREATE FUNCTION mod_node_vote_count (INT4, INT2, INT2) RETURNS INT2 AS ' DECLARE node_num ALIAS for $1; nay_status ALIAS for $2; add ALIAS for $3; nay_tot INT4 NOT NULL DEFAULT 0; yay_tot INT4 NOT NULL DEFAULT 0; BEGIN IF add = 1 THEN IF nay_status = 1 THEN UPDATE node SET nays = nays + 1 WHERE node_id = node_num; ELSE UPDATE node SET yays = yays + 1 WHERE node_id = node_num; END IF; ELSE IF nay_status = 1 THEN UPDATE node SET nays = nays - 1 WHERE node_id = node_num; ELSE UPDATE node SET yays = yays - 1 WHERE node_id = node_num; END IF; END IF; SELECT nays INTO nay_tot FROM node WHERE node_id = node_num; SELECT yays INTO yay_tot FROM node WHERE node_id = node_num; IF nay_tot + yay_tot != 0 THEN UPDATE node SET rating = CEIL( (yay_tot * 100)/(yay_tot + nay_tot) ) WHERE node_id = node_num; ELSE UPDATE node SET rating = 50 WHERE node_id = node_num; END IF; RETURN 1; END; ' LANGUAGE 'plpgsql'; ------------------------------------------------------------------------ -- vote totalling rules -- vote insertion CREATE RULE itemvote_insert_item_inc AS ON INSERT TO itemvote DO SELECT mod_node_vote_count(NEW.target_id, NEW.nays, 1); CREATE RULE uservote_insert_item_inc AS ON INSERT TO uservote DO SELECT mod_node_vote_count(NEW.target_id, NEW.nays, 1); -- vote deletion CREATE RULE itemvote_delete_item_dec AS ON DELETE TO itemvote DO SELECT mod_node_vote_count(OLD.target_id, OLD.nays, 0); CREATE RULE uservote_delete_item_dec AS ON DELETE TO uservote DO SELECT mod_node_vote_count(OLD.target_id, OLD.nays, 0); -- vote updates CREATE RULE itemvote_update_item_mod AS ON UPDATE TO itemvote WHERE OLD.nays != NEW.nays DO (SELECT mod_node_vote_count(OLD.target_id, OLD.nays, 1); SELECT mod_node_vote_count(NEW.target_id, NEW.nays, 0);); CREATE RULE uservote_update_item_mod AS ON UPDATE TO uservote WHERE OLD.nays != NEW.nays DO (SELECT mod_node_vote_count(OLD.target_id, OLD.nays, 1); SELECT mod_node_vote_count(NEW.target_id, NEW.nays, 0);); -- users INSERT INTO users (name, pass_hash, realname, email) VALUES ('mosch', 'dafe001b7733b0f3236aa95e00f8ed88', 'Kevin', 'monica@whitehouse.gov'); INSERT INTO users (name, pass_hash, realname, email) VALUES ('Wakko', 'c6ef90fcf92bf703c3cc79a679c192a3', 'Alex', 'wakko@bitey.net'); -- items INSERT INTO item (name, creator_id, reason) VALUES ('slashdot.org', 2, 'Because it\'s a pile of turd.'); INSERT INTO item (name, creator_id, reason) VALUES ('Yahoo!', 2, 'Because it\'s ugly.'); INSERT INTO item (name, creator_id, reason) VALUES ('memepool', 1, 'Because it\'s phat phat phat phat phat.'); INSERT INTO item (name, creator_id, reason) VALUES ('blow!!??!!', 1, 'this record nays nays nays'); -- item votes INSERT INTO itemvote (target_id, user_id, nays) VALUES (3, 1, 1); INSERT INTO itemvote (target_id, user_id, nays) VALUES (4, 1, 0); INSERT INTO itemvote (target_id, user_id, nays) VALUES (5, 2, 1); -- user votes INSERT INTO uservote (target_id, user_id, nays) VALUES (2, 1, 0); INSERT INTO uservote (target_id, user_id, nays) VALUES (1, 2, 1);