Re: ERROR: cannot convert relation containing dropped columns to view

Nicklas =?ISO-8859-1?Q?Av=E9n?= <nicklas(dot)aven(at)jordogskog(dot)no> writes:
> create table foo
> (
> id serial,
> deleted int
> );

> alter table foo drop column deleted;
> alter table foo add column deleted timestamp;

> CREATE or replace RULE del_post AS ON DELETE TO foo
> DO INSTEAD
> update foo set deleted = now()
> WHERE id = OLD.id
> returning *;

> returns:
> ERROR: cannot convert relation containing dropped columns to view

Hmm.

> 1) is this a bug

Well, it's an unimplemented feature anyway. The reason the error message
is like that seems to be that it was correct (that is, that was the only
possible case) when it was introduced, which was in the 2002 patch that
implemented DROP COLUMN to begin with:

+ /*
+ * 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). 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.
+ */
+ if (attr->attisdropped)
+ elog(ERROR, "cannot convert relation containing dropped columns to view");

When we made rules with RETURNING go through this logic, in 2006, we
don't seem to have revisited the message text, much less thought about
whether we needed to take "more trouble" about dealing with dropped
columns in a real table.

I'm not sure how hard it would be to support the case. Given that yours
is the first complaint in ten years, and that rules in general are pretty
out of favor, it's probably not going to be very high on the to-do list.
My own inclination would just be to provide a more on-point error message
for this case.

> 2) is there a way to "cean" the table from the deleted columns without
> recreating it?

Nope, sorry.

What I'd suggest is that you consider implementing this behavior without
using rules. Instead, what you want is something like

create view visible_foo as
select <desired columns> from foo where deleted is null;

plus INSTEAD OF triggers that redirect inserts/updates/deletes from
visible_foo to foo. This way is likely to perform better than a rule
and have less-surprising semantics in corner cases.

regards, tom lane