BUG #4271: dropped columns conflict with returning rules

The following bug has been logged online:

Bug reference: 4271
Logged by: Alexey Bashtanov
Email address: bashtanov(at)imap(dot)cc
PostgreSQL version: 8.3.1
Operating system: linux
Description: dropped columns conflict with returning rules
Details:

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.

When I tried to ReCREATE this rule, POSTGRESQL said 'ERROR: cannot convert
relation containing dropped columns to view'

"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

Hello, Tom!

> What did you do *exactly*?

Here's the example of command sequence that lead to this error:

luh=# create table foo(a int);
CREATE TABLE
luh=# alter TABLE foo add column b int;
ALTER TABLE
luh=# alter TABLE foo drop column b;
ALTER TABLE
luh=# alter TABLE foo add column c int;
ALTER TABLE
luh=# create table foo_child() inherits (foo);
CREATE TABLE
luh=# create or replace rule myrule as on insert to foo do instead
insert into foo_child values(new.*) returning foo_child.*;
ERROR: cannot convert relation containing dropped columns to view
luh=#

> > 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:

That's OK

Thanks,
Alexey

--
http://www.fastmail.fm - The way an email service should be

"Alexey Bashtanov" <bashtanov(at)imap(dot)cc> writes:
>> What did you do *exactly*?

> Here's the example of command sequence that lead to this error:

> luh=# create table foo(a int);
> CREATE TABLE
> luh=# alter TABLE foo add column b int;
> ALTER TABLE
> luh=# alter TABLE foo drop column b;
> ALTER TABLE
> luh=# alter TABLE foo add column c int;
> ALTER TABLE
> luh=# create table foo_child() inherits (foo);
> CREATE TABLE
> luh=# create or replace rule myrule as on insert to foo do instead
> insert into foo_child values(new.*) returning foo_child.*;
> ERROR: cannot convert relation containing dropped columns to view

Ah, it looks like you get different errors depending on whether the
dropped column is the last one or not, but they're coming from the
same routine.

The short answer is that checkRuleResultList() isn't prepared to cope
with dropped columns. Per the code comment:

/*
* Disallow dropped columns in the relation. This won't happen in the
* cases we actually care about (namely creating a view via CREATE
* TABLE then CREATE RULE, or adding a RETURNING rule to a view).
* Trying to cope with it is much more trouble than it's worth,
* because we'd have to modify the rule to insert dummy NULLs at the
* right positions.
*/

This example shows that dropped columns might happen in real-world cases
after all, so I suppose we should think about improving the situation,
at least for the RETURNING case. (This comment was originally written
with only the convert-table-to-view case in mind, and I think it's still
a reasonable restriction there.)

Hmm, I wonder if "insert dummy NULLs" is really necessary, or if
renumbering the targetlist's resnos would be enough to make RETURNING
work? I forget whether we select RETURNING elements by resno or
physical position.

Anyway, I have other things to do that strike me as higher priority.
Anyone want to tackle this one?

regards, tom lane

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. +