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=current_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=current_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=current_user;
--ERROR: could not convert table "rls3" to a view because it has child
tables
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs