---- Tom Lane skrev ---- > Nicklas =?ISO-8859-1?Q?Av=E9n?= <nicklas.a...@jordogskog.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
