Sunday, June 29, 2008

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

"Alexey Bashtanov" <bashtanov@imap.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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

No comments: