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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
