From: | Stephan Sachse <sachse(at)nugmbh(dot)de> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | affected rows from INSERT INTO view with rules and conditions |
Date: | 2004-09-08 09:06:52 |
Message-ID: | 20040908110652.671c5ddc@nusphere4342 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================
Your name : Stephan Sachse
Your email address : sachse (at) nugmbh (dot) de
System Configuration
---------------------
Architecture (example: Intel Pentium) : AMD Athlon(tm) Processor
Operating System (example: Linux 2.4.18) : Linux 2.4.25
PostgreSQL version (example: PostgreSQL-7.4.2): PostgreSQL-7.3.4
Compiler used (example: gcc 2.95.2) : gcc 3.2
Please enter a FULL description of your problem:
------------------------------------------------
I have 3 rules an a View to INSERT into the VIEW. 1 rule without condition and
2 other rule with a condition. All INSERT querys works fine. My only problem
ist the number of affected rows for querys. some on it is always zero some it
is 1.
i try this also with v7.4.2 with absolutly the same result
Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
-- START
-- first way is for affected rows is only 1 with pid = 2
CREATE TABLE rule_test
(
id integer NOT NULL DEFAULT nextval('rule_test_id'::text),
pid integer,
f_1 varchar,
f_2 varchar
) WITH OIDS;
CREATE SEQUENCE rule_test_id;
CREATE VIEW v_rule_test AS
SELECT id AS myid, pid AS mypid, f_1 AS myf_1, f_2 AS myf_2
FROM rule_test;
CREATE RULE v_rule_test_ins AS ON INSERT TO v_rule_test
DO INSTEAD
NOTHING;
CREATE RULE v_rule_test_ins_p1 AS ON INSERT TO v_rule_test WHERE new.mypid=1
DO INSTEAD
INSERT INTO rule_test (pid, f_1, f_2) VALUES (1, NEW.myf_1, NEW.myf_2);
CREATE RULE v_rule_test_ins_p2 AS ON INSERT TO v_rule_test WHERE new.mypid=2
DO INSTEAD
INSERT INTO rule_test (pid, f_1, f_2) VALUES (1, NEW.myf_2, NEW.myf_1);
INSERT INTO v_rule_test (mypid, myf_1, myf_2) VALUES (1, 'field 1', 'field 2');
INSERT INTO v_rule_test (mypid, myf_1, myf_2) VALUES (2, 'field 1', 'field 2');
INSERT INTO v_rule_test (mypid, myf_1, myf_2) VALUES (11, 'field 1', 'field 2');
-- output truncate
-- INSERT 0 0
-- INSERT 34588 1
-- INSERT 0 0
-- ENDE
-- START
-- second way is for affected rows always zero
-- all same as above except the Rules are not INSTEAD
CREATE TABLE rule_test
(
id integer NOT NULL DEFAULT nextval('rule_test_id'::text),
pid integer,
f_1 varchar,
f_2 varchar
) WITH OIDS;
CREATE SEQUENCE rule_test_id;
CREATE VIEW v_rule_test AS
SELECT id AS myid, pid AS mypid, f_1 AS myf_1, f_2 AS myf_2
FROM rule_test;
CREATE RULE v_rule_test_ins AS ON INSERT TO v_rule_test
DO INSTEAD
NOTHING;
CREATE RULE v_rule_test_ins_p1 AS ON INSERT TO v_rule_test WHERE new.mypid=1
DO
INSERT INTO rule_test (pid, f_1, f_2) VALUES (1, NEW.myf_1, NEW.myf_2);
CREATE RULE v_rule_test_ins_p2 AS ON INSERT TO v_rule_test WHERE new.mypid=2
DO
INSERT INTO rule_test (pid, f_1, f_2) VALUES (1, NEW.myf_2, NEW.myf_1);
INSERT INTO v_rule_test (mypid, myf_1, myf_2) VALUES (1, 'field 1', 'field 2');
INSERT INTO v_rule_test (mypid, myf_1, myf_2) VALUES (2, 'field 1', 'field 2');
INSERT INTO v_rule_test (mypid, myf_1, myf_2) VALUES (11, 'field 1', 'field 2');
-- output truncate
-- INSERT 0 0
-- INSERT 0 0
-- INSERT 0 0
-- ENDE
If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------
From | Date | Subject | |
---|---|---|---|
Next Message | PostgreSQL Bugs List | 2004-09-08 09:47:34 | BUG #1245: Postgres won't start |
Previous Message | Marcio Balieiro | 2004-09-07 23:56:02 | PostgreSQL 8.0 - very good |