Re: Dropped index on table preventing rule creation

On Sep 10, 2011, at 11:26 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Thom Brown <thom(at)linux(dot)com> writes:
>> I don't use rules, but in a bit of experimentation on Git master, I
>> discovered the following behaviour:
>
>> CREATE TABLE test1 (id serial primary key, things text);
>> CREATE TABLE test2 (id serial primary key, things text);
>> ALTER TABLE test1 DROP CONSTRAINT test1_pkey;
>> ALTER TABLE test2 DROP CONSTRAINT test2_pkey;
>> CREATE RULE "_RETURN" AS ON SELECT TO test1 DO INSTEAD select * from test2;
>
>> This produces the error message: could not convert table "test1" to a
>> view because it has indexes
>
> IIRC, this is because the check is just checking relhasindex. You
> should be able to recover and create the rule if you VACUUM the table.
>
> We could no doubt add more code to make that more transparent, but I
> don't see the point. The entire exercise of converting a table to a
> view is only meant to support loading of pg_dump output from versions
> that are probably ten years obsolete at this point. We don't even
> document that you can do the above, do we?
>
> (IOW, rather than "fix" this I'd prefer to rip out the code altogether.
> But maybe we should wait a couple more years for that.)

IIRC, it's not dead code. I think you can still generate such a dump if you use CREATE OR REPLACE VIEW to manufacture a pair of mutually recursive views.

Now we should probably disallow that, but we currently don't.

...Robert