Re: BUG #4271: dropped columns conflict with returning rules - Mailing list pgsql-bugs

Added to TODO:

    Enable creation of RETURNING rules on inherited tables that have dropped
    columns

        http://archives.postgresql.org/pgsql-bugs/2008-06/msg00183.php

---------------------------------------------------------------------------

Tom Lane wrote:
> "Alexey Bashtanov" <bashtanov@imap.cc> writes:
> > I have created a partitioned table cache with partitions cache_id_g_4184088
> > and cache_id_le_4184088 those inherit cache.
> > I provided insert by the following rule:
> > CREATE RULE cache_partic AS ON INSERT TO cache DO INSTEAD INSERT INTO
> > cache_id_g_4184088 VALUES (new.*) RETURNING cache_id_g_4184088.*;
>
> > after I ran
> > ALTER TABLE cache add column foo
> > ALTER TABLE cache add column bar
> > ALTER TABLE cache drop column bar
> > ALTER TABLE cache add column quackquack
>
> > this rule started to work incorrectly: it did not store foo and quackquack
> > values but used nulls instead.
>
> This is expected behavior because the * expressions are expanded when
> the rule is defined:
>
> regression=# create table foo(f1 int, f2 int, f3 int);
> CREATE TABLE
> regression=# create table foo1() inherits (foo);
> CREATE TABLE
> regression=# create rule redirect as on insert to foo do instead
> insert into foo1 values(new.*) returning foo1.*;
> CREATE RULE
> regression=# \d foo
>       Table "public.foo"
>  Column |  Type   | Modifiers
> --------+---------+-----------
>  f1     | integer |
>  f2     | integer |
>  f3     | integer |
> Rules:
>     redirect AS
>     ON INSERT TO foo DO INSTEAD  INSERT INTO foo1 (f1, f2, f3)
>   VALUES (new.f1, new.f2, new.f3)
>   RETURNING foo1.f1, foo1.f2, foo1.f3
>
> regression=# alter table foo add column f4 int;
> ALTER TABLE
> regression=# \d foo
>       Table "public.foo"
>  Column |  Type   | Modifiers
> --------+---------+-----------
>  f1     | integer |
>  f2     | integer |
>  f3     | integer |
>  f4     | integer |
> Rules:
>     redirect AS
>     ON INSERT TO foo DO INSTEAD  INSERT INTO foo1 (f1, f2, f3)
>   VALUES (new.f1, new.f2, new.f3)
>   RETURNING foo1.f1, foo1.f2, foo1.f3
>
> Of course you can redefine the rule after adding a column:
>
> regression=# create or replace rule redirect as on insert to foo do instead
> insert into foo1 values(new.*) returning foo1.*;
> CREATE RULE
> regression=# \d foo
>       Table "public.foo"
>  Column |  Type   | Modifiers
> --------+---------+-----------
>  f1     | integer |
>  f2     | integer |
>  f3     | integer |
>  f4     | integer |
> Rules:
>     redirect AS
>     ON INSERT TO foo DO INSTEAD  INSERT INTO foo1 (f1, f2, f3, f4)
>   VALUES (new.f1, new.f2, new.f3, new.f4)
>   RETURNING foo1.f1, foo1.f2, foo1.f3, foo1.f4
>
>
> > When I tried to ReCREATE this rule, POSTGRESQL said 'ERROR:  cannot convert
> > relation containing dropped columns to view'
>
> What did you do *exactly*?  I tried
>
> regression=# alter table foo add column f5 int;
> ALTER TABLE
> regression=# alter table foo drop column f5;
> ALTER TABLE
> regression=# create or replace rule redirect as on insert to foo do instead
> insert into foo1 values(new.*) returning foo1.*;
> ERROR:  RETURNING list has too few entries
>
> which does seem like a bug but it's not what you are describing.
>
>             regards, tom lane
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +