Skip site navigation (1) Skip section navigation (2)

PL/pgSQL related crash

From: Kevin Way <kevin(dot)way(at)overtone(dot)org>
To: pgsql-bugs(at)postgresql(dot)org
Subject: PL/pgSQL related crash
Date: 2001-09-23 22:09:21
Message-ID: 20010923220920.A43813@bean.overtone.org (view raw or flat)
Thread:
Lists: 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(at)postgresql(dot)org(dot)

To report any other bug, fill out the form below and e-mail it to
pgsql-bugs(at)postgresql(dot)org(dot)

If you not only found the problem but solved it and generated a patch
then e-mail it to pgsql-patches(at)postgresql(dot)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(dot)way(at)overtone(dot)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(at)whitehouse(dot)gov');
INSERT INTO users (name,  pass_hash, realname, email) VALUES ('Wakko', 'c6ef90fcf92bf703c3cc79a679c192a3', 'Alex', 'wakko(at)bitey(dot)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:
---------------------------------------------------------------------



Responses

pgsql-bugs by date

Next:From: pgsql-bugsDate: 2001-09-24 01:46:47
Subject: Bug #467: Can't insert a value of 0 (zero) into a Bytea type.
Previous:From: John SummerfieldDate: 2001-09-23 21:23:20
Subject: CVS still doesn't work as described:

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group