Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




---- Tom Lane skrev ----

> Nicklas =?ISO-8859-1?Q?Av=E9n?= <nicklas.aven@xxxxxxxxxxxxx> 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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux