From: | Tod McQuillin <devin(at)spamcop(dot)net> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Bug with rules in 7.0.3? |
Date: | 2001-02-03 17:09:41 |
Message-ID: | Pine.GSO.4.31.0102031104350.5691-100000@sysadmin |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-02-03 18:18:37 | Re: Bug with rules in 7.0.3? |
Previous Message | Florian Steffen | 2001-02-03 15:21:41 | Timestamp accuracy |