Re: [BUGS] BUG #4271: dropped columns conflict with returning rules

"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