bug with CREATE RULE - Mailing list pgsql-bugs

========================================================================
====
                         POSTGRESQL BUG REPORT TEMPLATE
========================================================================
====


Your name        :    Chris Pizzi
Your email address    :    cpizzi@sequiam.com


System Configuration
---------------------
   Architecture (example: Intel Pentium)      :    Intel Pentium III
(Coppermine)

   Operating System (example: Linux 2.0.26 ELF)     :    Linux 2.2.20 Debian
unstable

   PostgreSQL version (example: PostgreSQL-7.3):   PostgreSQL 7.3.4

   Compiler used (example:  gcc 2.95.2)        :    GCC i386-linux-gcc (GCC)
3.3.2 20030831 (Debian prerelease)


Please enter a FULL description of your problem:
------------------------------------------------
A "select" rule may be written on a table that is referenced by the
foreign keys of other tables.  After the rule is created, the foreign
keys are correctly enforced for inserts and updates of the child table,
but not for updates and deletes of the parent.  The affected foreign
key triggers cannot be dropped; thus the child tables, new parent table
as defined by the rule, and the parent view cannot be dropped either.



Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------

template1=# select version();
                                                     version
------------------------------------------------------------------------
----------------------------------------
  PostgreSQL 7.3.4 on i386-pc-linux-gnu, compiled by GCC i386-linux-gcc
(GCC) 3.3.2 20030831 (Debian prerelease)
(1 row)

template1=# create database example;
CREATE DATABASE
template1=# \c example
You are now connected to database example.
example=# create table parent (id integer unique);
NOTICE:  CREATE TABLE / UNIQUE will create implicit index
'parent_id_key' for table 'parent'
CREATE TABLE
example=# create table parent_v (id integer unique);
NOTICE:  CREATE TABLE / UNIQUE will create implicit index
'parent_v_id_key' for table 'parent_v'
CREATE TABLE
example=# create table child (fk integer);
CREATE TABLE
example=# alter table child add constraint child_fk foreign key (fk)
references parent_v (id);
NOTICE:  ALTER TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
ALTER TABLE
example=# create rule "_RETURN" as on select to parent_v do instead
select id from parent;
CREATE RULE
example=# insert into parent values (1);
INSERT 46215 1
example=# insert into parent values (2);
INSERT 46216 1
example=# insert into parent values (3);
INSERT 46217 1
example=# insert into child values (1);
INSERT 46218 1
example=# insert into child values (2);
INSERT 46219 1
example=# insert into child values (4);
ERROR:  child_fk referential integrity violation - key referenced from
child not found in parent_v
example=# delete from parent where id = 1;
DELETE 1
example=# drop table parent cascade;
NOTICE:  Drop cascades to rule _RETURN on view parent_v
NOTICE:  Drop cascades to view parent_v
NOTICE:  Drop cascades to constraint child_fk on table child
ERROR:  DropTrigger: relation "parent_v" is not a table
example=# drop table child cascade;
ERROR:  DropTrigger: relation "parent_v" is not a table
example=# drop view parent_v;
NOTICE:  constraint child_fk on table child depends on view parent_v
ERROR:  DropTrigger: relation "parent_v" is not a table
example=# alter table child drop constraint child_fk;
ERROR:  DropTrigger: relation "parent_v" is not a table
example=#


If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------
Creating a "select" rule on a table should fail if the table is
referenced by foreign key constraints.