Hello!
I've encountered certain--opposite to documentation--DBMS behavoiur. Let's
narrow down my DB schema to few tables, which can reproduce this problem.
I have these schemas:
CREATE TABLE "users"
(
"id_user" SERIAL NOT NULL,
"first" VARCHAR(24) NOT NULL DEFAULT '(imi\352)',
"last" VARCHAR(32) NOT NULL DEFAULT '(nazwisko)',
"email" VARCHAR(24) DEFAULT NULL,
"vip" BOOLEAN NOT NULL DEFAULT 'f',
"ed" BOOLEAN NOT NULL DEFAULT 'f',
PRIMARY KEY ("id_user")
);
CREATE TABLE "permissions"
(
"id_permission" INTEGER NOT NULL,
"name" VARCHAR(32) NOT NULL UNIQUE,
PRIMARY KEY ("id_permission")
);
CREATE TABLE "given_permissions"
(
"id_permission" INTEGER NOT NULL,
"id_user" INTEGER NOT NULL,
PRIMARY KEY ("id_permission", "id_user"),
FOREIGN KEY ("id_permission")
REFERENCES "permissions"
ON DELETE CASCADE,
FOREIGN KEY ("id_user")
REFERENCES "users"
ON DELETE CASCADE
);
Besides I have this rule:
CREATE RULE "on_insert_to_users" AS
ON INSERT TO "users"
DO
(
INSERT INTO "given_permissions" VALUES (1, NEW."id_user");
INSERT INTO "given_permissions" VALUES (2, NEW."id_user");
INSERT INTO "given_permissions" VALUES (3, NEW."id_user");
);
In table "permissions" I have tuples with "id_permission" in (1, 2, 3, ...).
This rule is suppoused to give a new user default permissions. But when
issuing statement like that:
INSERT INTO "users" (first, last, email) VALUES ('John', 'Doe',
'[EMAIL PROTECTED]');
or
INSERT INTO "users" DEFAULT VALUES;
cause this error message to appear:
"Fail to add null value in not null attribute id_user"
It turned out, that dropping the "on_insert_to_users" rule
allows me again to insert default values into "users" table
(in particular "id_user"). So it seems, like action in the
rule does not see "id_user" value set to default value in NEW
tuple. But according to documentation we have:
"The action [defined in the rule] is done instead of the original
query if INSTEAD is specified; otherwise it is done after the
original query in the case of ON INSERT, or before the original
query in the case of ON UPDATE or ON DELETE".
So in this particular case, as I understand, in NEW tuple
there should already be the default "id_user" value properly
set, since the action in this case is invoked _after_ insert
statement. Well, in this case is not enteirly true. I guess,
that "id_user" value in NEW tuple is not properly updated after
insertion data into "users" table.
My question is--is this really a bug or for some reason is this
proper behaviour, but not adequately described in docs?
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
