Horton

Thread

  1. confounding, incorrect constraint error

    Kevin Way <kevin.way@overtone.org> — 2001-09-23T07:09:46Z

    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);

  2. Re: confounding, incorrect constraint error

    Kevin Way <kevin.way@overtone.org> — 2001-09-23T20:54:01Z

    > > Below is a significantly simplified version of my schema, which
> > exhibits
> > the above problem.
> 
> Unfortunately, even a simplified version of your schema would take me
> some hours to understand.  As your rule-setting is quite complex, my
> first instinct would be to hunt for circular procedural logic in your
> rules.  Try to pursue, step by step, everything that happens from the
> moment you send the insert command to uservotes.  You may find that the
> logic cascades back to the beginning.  I've done this to myself on
> occasion, causing the DB to hang on a seemingly simple request.

I'm fairly certain that there's no circular procedural logic.

The errors can be turned on/off by turning on/off the uservote_ series
of rules, which are attached to the uservote table.  These rules call
mod_node_vote_count which only touches the node table.  There are no
rules or triggers associated with the node table, so there is no circular
logic there.

Additional strangeness is that the itemvote_ series of rules works perfectly
despite the fact that the only difference between uservote_ and itemvote_
rules is the table that triggers them, they both call the same procedure on
the nodes table.

My current thinking is that something is stomping on some memory, because 
you can vary the effect of the error from being an incorrectly failed CHECK
constraint, to crashing the database, by varying the number of columns in
the tables in question.

I'm unemployed at the moment and this is a pet project, so I can't offer
much in the way of financial compensation, but I'll start the bidding at
$50 donation in your name to your choice of the EFF, the Red Cross, or the
American Cancer Society, in return for a fix.  (If none of these charities
are acceptable, surely one can be found later that is acceptable to both
parties).

Again, I greatly appreciate any help, and I apologize that my test case is
still fairly sizeable, despite being about 10% the size of the original
code.

-Kevin Way

  3. Re: confounding, incorrect constraint error

    Jan Wieck <janwieck@yahoo.com> — 2001-09-24T18:12:03Z

    Kevin Way wrote:
> I'm unemployed at the moment and this is a pet project, so I can't offer
> much in the way of financial compensation, but I'll start the bidding at
> $50 donation in your name to your choice of the EFF, the Red Cross, or the
> American Cancer Society, in return for a fix.  (If none of these charities
> are acceptable, surely one can be found later that is acceptable to both
> parties).

    Sorry,  I missed the original post of the problem. If you can
    send it to me again and change your offer into donating blood
    at the Red Cross, I would take a look at it.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

  4. CHECK problem really OK now...

    Kovacs Baldvin <kb136@hszk.bme.hu> — 2001-09-24T18:38:20Z

    Hi everybody!

I tried, and it works: the current CVS version really runs
happily the query what sent to heaven our 7.1 version of the
backend.

Kevin: your original complex schema also runs smoothly.

Thanks for our mindful developers!

Regards,
Baldvin

I think Jan wrote:
>     Sorry,  I missed the original post of the problem. If you can
>     send it to me again and change your offer into donating blood
>     at the Red Cross, I would take a look at it.

Probably the chances for a prize went... :-(( However, is there
still a shortage of blod???

Baldvin

  5. Re: CHECK problem really OK now...

    Josh Berkus <josh@agliodbs.com> — 2001-09-24T19:18:04Z

    Baldvin,

> Probably the chances for a prize went... :-(( However, is there
> still a shortage of blod???

Last I checked, the American Red Cross does not want your blood right
now -- they want it two weeks from now.  Currently blood stores are
full, but they get depleted pretty fast.  Of course, what they really
want is for you to make a commitment to donate twice a year, every year.

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

  6. Re: CHECK problem really OK now...

    Jan Wieck <janwieck@yahoo.com> — 2001-09-24T19:23:13Z

    Kovacs Baldvin wrote:
> Hi everybody!
>
> I tried, and it works: the current CVS version really runs
> happily the query what sent to heaven our 7.1 version of the
> backend.
>
> Kevin: your original complex schema also runs smoothly.
>
> Thanks for our mindful developers!
>
> Regards,
> Baldvin
>
> I think Jan wrote:
> >     Sorry,  I missed the original post of the problem. If you can
> >     send it to me again and change your offer into donating blood
> >     at the Red Cross, I would take a look at it.
>
> Probably the chances for a prize went... :-(( However, is there
> still a shortage of blod???

    Well, for the NY disaster they probably have more than enough
    - not that many injured people there - sad enough though. But
    what's wrong with using the current wave of patriotism to get
    as much as they can get?

    It help's saving life! Using the  victims  for  that  purpose
    isn't  abuse.   It  is  turning grief, anger and sadness into
    help and hope.

    Let blood become "Open Source". Give it for free  and  you'll
    get plenty of it when you need some.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

  7. [HACKERS] not on .hackers

    August Zajonc <junk-pgsql@aontic.com> — 2001-09-24T20:38:27Z

    I tend to follow the mailing list through news.postgresql.org, and it seems
like all the -hackers messages are ending up in the .general group rather
than .hackers.

Did I somehow blow my configuration or are other people experiencing the
same thing?

AZ

  8. Re: [HACKERS] not on .hackers

    Colin 't Hart <cthart@yahoo.com> — 2001-09-24T20:51:14Z

    August Zajonc:

> I tend to follow the mailing list through news.postgresql.org, and it
seems
> like all the -hackers messages are ending up in the .general group rather
> than .hackers.

I also follow the mailing list(s) through news.postgresql.org and now that
you mention it ...

It hasn't always been like this.


Cheers,

Colin

  9. Re: CHECK problem really OK now...

    Roberto Mello <rmello@cc.usu.edu> — 2001-09-25T04:49:57Z

    On Mon, Sep 24, 2001 at 03:23:13PM -0400, Jan Wieck wrote:
> 
>     It help's saving life! Using the  victims  for  that  purpose
>     isn't  abuse.   It  is  turning grief, anger and sadness into
>     help and hope.
> 
>     Let blood become "Open Source". Give it for free  and  you'll
>     get plenty of it when you need some.

	I couldn't agree more!

	-Roberto
-- 
+------------| Roberto Mello - http://www.brasileiro.net |------------+
Computer Science, Utah State University  -    http://www.usu.edu
USU Free Software & GNU/Linux Club       -    http://fslc.usu.edu
Space Dynamics Lab, Developer            -    http://www.sdl.usu.edu
OpenACS - Enterprise free web toolkit    -    http://openacs.org
TAFB -> Text Above Fullquote Below

  10. Re: [HACKERS] not on .hackers

    G. Anthony Reina <reina@nsi.edu> — 2001-09-25T18:59:20Z

    "Colin 't Hart" <cthart@yahoo.com> wrote in message news:<9oo6en$qr8$1@news.tht.net>...
> August Zajonc:
> 
> > I tend to follow the mailing list through news.postgresql.org, and it
>  seems
> > like all the -hackers messages are ending up in the .general group rather
> > than .hackers.
> 
> I also follow the mailing list(s) through news.postgresql.org and now that
> you mention it ...
> 
> It hasn't always been like this.

I'm getting the same thing. Almost nothing is going to the Hackers
list on Google groups (old Dejanews). They all seem to be sent to
other lists with the [HACKERS] id in the subject.

-Tony

  11. Re: [SQL] CHECK problem really OK now...

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-09-26T18:36:58Z

    Kovacs Baldvin <kb136@hszk.bme.hu> writes:
> I tried, and it works: the current CVS version really runs
> happily the query what sent to heaven our 7.1 version of the
> backend.

I believe this traces to a fix I made in May:

2001-05-27 16:48  tgl

	* src/: backend/executor/execJunk.c, backend/executor/execMain.c,
	include/executor/executor.h, include/nodes/execnodes.h: When using
	a junkfilter, the output tuple should NOT be stored back into the
	same tuple slot that the raw tuple came from, because that slot has
	the wrong tuple descriptor.  Store it into its own slot with the
	correct descriptor, instead.  This repairs problems with SPI
	functions seeing inappropriate tuple descriptors --- for example,
	plpgsql code failing to cope with SELECT FOR UPDATE.

I didn't realize at the time that the error would also affect updates of
child tables, but tracing through your example with 7.1 shows clearly
that the CHECK is being applied to a slot that contains a four-column
tuple and only a three-column descriptor.  Ooops.

			regards, tom lane