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', '[EMAIL PROTECTED]');
INSERT INTO users (name, pass_hash, realname, email) VALUES ('Wakko',
'c6ef90fcf92bf703c3cc79a679c192a3', 'Alex', '[EMAIL PROTECTED]');
-- 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);
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
