Thread: BUG #7866: even after T's child-tables are deleted it cannot have select-rule

The following bug has been logged on the website:

Bug reference:      7866
Logged by:          Tim Romano
Email address:      tim.romano@yahoo.com
PostgreSQL version: 9.2.2
Operating system:   Windows 7 x64
Description:        =


After a table has been inherited, a select-rule cannot be created on it
*even if its child tables are deleted and a vacuum is performed afterwards*.
 =


Moreover, the select-rule documentation is vague and incomplete. Instead of
clear and concise definition, with a few simple examples illuminating a
variety of use cases, there is a long, unnecessarily complex, and not very
illuminating example.


    create table RLS3
    (tenant varchar(25) not null default current_user);
    --Query returned successfully with no result in 10 ms.

    create table customer
     (id int primary key not null,
     custname varchar(25) not null
     ) INHERITS (RLS3);
     --NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index =

     -- "customer_pkey" for table "customer"
    =

    CREATE RULE "_RETURN" AS ON SELECT TO RLS3 =

    do instead select * from RLS3 where tenant=3Dcurrent_user;
    --ERROR:  could not convert table "rls3" to a view because it has child
tables

    drop table customer;
    Query returned successfully with no result in 11 ms.
   =

     CREATE RULE "_RETURN" AS ON SELECT TO RLS3 =

     do instead select * from RLS3 where tenant=3Dcurrent_user;
    --ERROR:  could not convert table "rls3" to a view because it has child
tables
 =

     VACUUM;
     --Query returned successfully with no result in 91 ms.

    CREATE RULE "_RETURN" AS ON SELECT TO RLS3 =

    do instead select * from RLS3 where tenant=3Dcurrent_user;

    --ERROR:  could not convert table "rls3" to a view because it has child
tables

tim.romano@yahoo.com writes:
> After a table has been inherited, a select-rule cannot be created on it
> *even if its child tables are deleted and a vacuum is performed afterwards*.

This isn't a bug, it's an intentional implementation restriction.
I quote from the source code:

         * Are we converting a relation to a view?
         *
         * If so, check that the relation is empty because the storage for the
         * relation is going to be deleted.  Also insist that the rel not have
         * any triggers, indexes, or child tables.  (Note: these tests are too
         * strict, because they will reject relations that once had such but
         * don't anymore.  But we don't really care, because this whole
         * business of converting relations to views is just a kluge to allow
         * loading ancient pg_dump files.)

There is not any expectation that creating a select rule would ever
happen to anything except a freshly created table.  We'd probably not
even support the CREATE RULE syntax for this, except that pg_dump
sometimes needs to do it like that to get around circular dependencies
involving a view's rowtype.

            regards, tom lane