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

---- Tom Lane skrev ----

> 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

Ok, thank you.
I think you are right about putting this logic on the view instead. I had my reasons for going the rule path, but as you say there is reasons for not do that too.

Thanks a lot for very fast reponse!

Best Regards
Nicklas Avén