confounding, incorrect constraint error
От | Kevin Way |
---|---|
Тема | confounding, incorrect constraint error |
Дата | |
Msg-id | 20010923070945.A39397@bean.overtone.org обсуждение исходный текст |
Список | pgsql-sql |
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 TEXTNOT NULL, nays INT4 NOT NULL DEFAULT 0 CHECK ( nays >= 0 ), yays INT4 NOT NULL DEFAULT 0, CHECK ( yays >= 0 ), rating INT2 NOT NULL DEFAULT50 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 REFERENCESusers (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 UPDATECASCADE, 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 UPDATECASCADE, 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_statusALIAS 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 INTOnay_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; ENDIF; 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);
В списке pgsql-sql по дате отправления: