pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tod McQuillin <devin@spamcop.net>
To: pgsql-sql@postgresql.org
Subject: Bug with rules in 7.0.3?
Date: Sat, 3 Feb 2001 11:09:41 -0600 (CST)
Message-ID: <Pine.GSO.4.31.0102031104350.5691-100000@sysadmin> (raw)


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






view thread (4+ messages)  latest in thread

Message-ID: <Pine.GSO.4.31.0102031104350.5691-100000@sysadmin>
Permalink:  ../Pine.GSO.4.31.0102031104350.5691-100000@sysadmin/
Also on:    postgresql.org/message-id/Pine.GSO.4.31.0102031104350.5691-100000@sysadmin

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: devin@spamcop.net
  Subject: Re: Bug with rules in 7.0.3?
  In-Reply-To: <Pine.GSO.4.31.0102031104350.5691-100000@sysadmin>
  References: <Pine.GSO.4.31.0102031104350.5691-100000@sysadmin>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox