Received: from sysadmin.chi.wdr.com (sysadmin.chi.ubswarburg.com [146.180.1.66]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f13H9hq18828 for ; Sat, 3 Feb 2001 12:09:43 -0500 (EST) (envelope-from devin@sysadmin.chi.wdr.com) Received: from devin by sysadmin.chi.wdr.com with local (Exim 3.12 #2) id 14P6By-0001fp-00 for pgsql-sql@postgresql.org; Sat, 03 Feb 2001 11:09:42 -0600 Date: Sat, 3 Feb 2001 11:09:41 -0600 (CST) From: Tod McQuillin X-X-Sender: To: Subject: Bug with rules in 7.0.3? Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200102/21 X-Sequence-Number: 1189 Hi there... I've spotted something weird in 7.0.3 with rules. By now I've realised I probably need to use a trigger to do what I have in mind, but even so, there no way I can explain the behaviour I am getting with a rule. Given this SQL script: CREATE TABLE menu ( menu_id SERIAL PRIMARY KEY, name TEXT, price integer ); INSERT INTO menu(name, price) VALUES ('Beer', 5); INSERT INTO menu(name, price) VALUES ('Vodka', 10); INSERT INTO menu(name, price) VALUES ('Scotch', 8); CREATE TABLE orders ( order_id SERIAL PRIMARY KEY, menu_id INTEGER REFERENCES menu, price INTEGER NOT NULL DEFAULT -1 ); CREATE RULE fix_order_price AS ON INSERT TO orders DO UPDATE orders SET price = M.price FROM menu M WHERE M.menu_id = new.menu_id AND new.price = -1; INSERT INTO orders (menu_id) VALUES (2); SELECT * FROM orders; Here's what happens: % createdb buggy CREATE DATABASE % psql buggy < ~/pg.bug NOTICE: CREATE TABLE will create implicit sequence 'menu_menu_id_seq' for SERIAL column 'menu.menu_id' NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'menu_pkey' for table 'menu' CREATE INSERT 259680 1 INSERT 259681 1 INSERT 259682 1 NOTICE: CREATE TABLE will create implicit sequence 'orders_order_id_seq' for SERIAL column 'orders.order_id' NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'orders_pkey' for table 'orders' NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) CREATE CREATE 259722 1 INSERT 0 3 order_id | menu_id | price ----------+---------+------- 1 | 2 | -1 2 | 2 | -1 3 | 2 | -1 (3 rows) How the heck can one insert and update generate three rows? -- Tod McQuillin