Bug with rules in 7.0.3?

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

Responses

Browse pgsql-sql by date

  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