Re: BUG #4271: dropped columns conflict with returning rules

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(at)imap(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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