[SQL] table inheritance and foreign key troubles
I'm having a little trouble with some inherited tables and a foreign key. Here's a simplified case, to show the trouble. CREATE TABLE node ( node_id SERIAL NOT NULL, nameTEXT NOT NULL, PRIMARY KEY (node_id) ); -- works just fine CREATE TABLE users ( email TEXT NOT NULL ) INHERITS (node); -- so far so good CREATE TABLE item ( reason TEXT NOT NULL, author_id INT NOT NULL REFERENCES users (node_id) ) INHERITS (node); ERROR: UNIQUE constraint matching given keys for referenced table "users" not found Does this operation just require differing syntax, because the referenced field is inherited from another table, or is this not possible? Kevin Way ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] trigger trouble -- procedure not found
I'm having trouble creating a trigger. First i'm creating a function, add_to_search in PL/pgSQL. \df verifies the existance of this function: smallint | add_to_search | text, text, text, integer but when I call: CREATE TRIGGER item_insert_search_add AFTER INSERT ON item FOR EACH ROW EXECUTE PROCEDURE add_to_search (name, description, reason, node_id); I get: ERROR: CreateTrigger: function add_to_search() does not exist What am I missing here? It seems to me that I'm missing something very simple, but I can't figure out what it is for the life of me. Kevin Way ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] trigger trouble -- procedure not found
Thank you. Later checking showed that these requirements were listed in the first sentence of the relevant page. Everything works like a champ now. I've made a small donation to the EFF and to the Red Cross as a minor thanks for your prompt help. Kevin Way ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Bug?: Update on ancestor for a row of a child
> And you think that Oracle is entirely free of bugs? ;-) Yes, but they'd be exciting technology-oriented e-business enabled bugs! > Still, I understand your frustration. Thanks... It's just frustrating that the bug is on something so basic, which makes it both hard to code around and hard for me to delve into the postgres source and fix. I spent a few hours tracing source before finally conceding the point that it takes more than a few hours to understand postgres internals well enough to fix a major bug. For development purposes I've just removed all the CHECK constraints from my child tables, and I'm hoping some genius will solve the problem by the time I'm looking to deploy. -Kevin Way ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] aggregate functions, COUNT
* Tom Lane <[EMAIL PROTECTED]> [02-10-01 18:02]: > Stephan Szabo <[EMAIL PROTECTED]> writes: > > I think you could use EXISTS for that, > > select EXISTS (); should give a true/false on whether the > > query returned any rows. I'm not sure if it stops after one row > > or not, but if it doesn't you can add a limit 1 to the query. > > Yes it does stop after one row; and furthermore, the planner knows to > generate a fast-start plan for it. (Or at least it's supposed to > ... hmm, this seems to be broken in current sources ...) Anyway, > there's no need for LIMIT 1 inside an EXISTS, because the planner > assumes that automatically. Thank you muchly, I did some profiling and SELECT EXISTS is indeed exactly what I wanted. -Kevin Way -- Kevin Way <[EMAIL PROTECTED]> http://www.overtone.org/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Server crash caused by CHECK on child
> -- I don't think that I only found a minor bug compared to > -- the other you wrote in your last letter: the backend crash > -- is caused by the same CHECK constraint in the child table. Oooh, my bad. I should run your scripts before assuming I know how they fail. > -- However, for you without time to analyzing Kevin's huge > -- scheme, here is the very simplified, crash-causing script. Thank you so much for finding this simplified method of crashing Postgres. Hopefully somebody can find a fix now. > -- I am hunting it, but I have to learn all what this query-executing > -- about, so probably it takes uncomparable longer for me than for > -- a developer. That's my problem as well, though your example is vastly easier to trace than mine. -Kevin Way ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] confounding, incorrect constraint error
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'),
nameTEXT 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,
realnameTEXT 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,
naysINT2 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,
naysINT2 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
Re: [SQL] confounding, incorrect constraint error
> > 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 msg06097/pgp0.pgp Description: PGP signature ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Bug?: Update on ancestor for a row of a child
> The problem is: when updating a row in an ancestor table, > which is really belongs to a child, there's something wrong > with the CHECK system. Well, I believe you found one minor problem. The bigger one is still lurking in the shadows though. To duplicate it, take my previous schema, and add lastlog TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, to the users table, between pass_hash and logged_in. After doing so, you'll find that postgres actually crashes when you try to insert a vote into the uservote table. That's the one that has me looking at the costs involved with migrating to Oracle. -Kevin ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] aggregate functions, COUNT
I'm currently using a SELECT count(*) when all I really want to know is if 1 or more records exist. Is there a standard way to just find out if a record exists? If not, is there a way to avoid iterating over all the records by writing an aggregate function? Given what I've read of how they work, I don't see how to make the function return before parsing all the results anyway, am I wrong here? Thanks for any help, or 2x4s, Kevin Way -- Kevin Way <[EMAIL PROTECTED]> http://www.overtone.org/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
