PL/pgSQL related crash - Mailing list pgsql-bugs
From | Kevin Way |
---|---|
Subject | PL/pgSQL related crash |
Date | |
Msg-id | 20010923220920.A43813@bean.overtone.org Whole thread Raw |
Responses |
Crash caused by CHECK on child
|
List | pgsql-bugs |
If PostgreSQL failed to compile on your computer or you found a bug that is likely to be specific to one platform then please fill out this form and e-mail it to pgsql-ports@postgresql.org. To report any other bug, fill out the form below and e-mail it to pgsql-bugs@postgresql.org. If you not only found the problem but solved it and generated a patch then e-mail it to pgsql-patches@postgresql.org instead. Please use the command "diff -c" to generate the patch. You may also enter a bug report at http://www.postgresql.org/ instead of e-mail-ing this form. ============================================================================ POSTGRESQL BUG REPORT TEMPLATE ============================================================================ Your name : Kevin Way Your email address : kevin.way@overtone.org System Configuration --------------------- Architecture (example: Intel Pentium) : Intel Pentium III Operating System (example: Linux 2.0.26 ELF) : FreeBSD 4.4 PostgreSQL version (example: PostgreSQL-7.1.3): PostgreSQL-7.1.3 Compiler used (example: gcc 2.95.2) : gcc 2.95.3 Please enter a FULL description of your problem: ------------------------------------------------ I get the error: psql:fuck.sql:176: pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. while executing PL/pgSQL, called by a rule which gets called when I touch the itemvote table from the below example. Please describe a way to repeat the problem. Please try to provide a concise reproducible example, if at all possible: ---------------------------------------------------------------------- The below will error on the last two lines (right after 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, lastlog TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 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 XXX FIXME BugBug Postgres crashes here! INSERT INTO uservote (target_id, user_id, nays) VALUES (2, 1, 0); INSERT INTO uservote (target_id, user_id, nays) VALUES (1, 2, 1); If you know how this problem might be fixed, list the solution below: ---------------------------------------------------------------------
pgsql-bugs by date: