============================================================================
POSTGRESQL BUG REPORT
============================================================================
Your name : Oliver Meyer
Your email address : [EMAIL PROTECTED]
System Configuration
---------------------
Architecture : x86
Operating System : Linux 2.2.14 (ELF)
PostgreSQL version : PostgreSQL-6.5.1 (binary
distribution)
Compiler used (example: gcc 2.8.0) : n/a
>Please enter a FULL description of your problem:
------------------------------------------------
Given a RULE that, on DELETE to one table, triggers an action to another table,
the action specified in the rule is performed for all records in
the target table and not just for the one deleted from the first table.
>Please describe a way to repeat the problem. Please try to provide a
>concise reproducible example, if at all possible:
----------------------------------------------------------------------
The following example consists of two tables; records that are deleted
from the first table shall be moved to the second table using a rule on DELETE.
This will not work, as with every DELETE to the first table, PostgreSQL
will insert all records of this table into the second table, no matter if they were
affected by the DELETE or not.
CREATE TABLE records (index INT4, description TEXT);
CREATE TABLE old_records (index INT4, description TEXT);
CREATE RULE move_record_to_old AS ON DELETE TO records
INSERT INTO old_records VALUES (new.index, new.description);
(Note: Other attributes than "new.*" are not recognized here by PostgreSQL)
INSERT INTO records
SELECT 1, 'record 1' UNION
SELECT 2, 'old record' UNION
SELECT 3, 'third record';
SELECT * FROM records;
index|description
-----+--------------
1|record 1
2|old record
3|third record
(3 rows)
SELECT * FROM old_records;
index|description
-----+-----------
(0 rows)
Everything is okay so far; now we try to move a record to the table of
old records by deleting it from the records table:
DELETE FROM records WHERE index = 2;
Now the expected result of
SELECT * FROM old_records
would be:
index|description
-----+--------------
2|old record
(1 row)
but actually it is:
index|description
-----+-----------
1|record 1
2|old record
3|third record
(3 rows)
Which is obviously not what one would expect.
