Re: ON SELECT rule on a table without columns - Mailing list pgsql-hackers

On February 8, 2019 10:05:03 AM GMT+01:00, Rushabh Lathia <rushabh.lathia@gmail.com> wrote:
>On Fri, Feb 8, 2019 at 12:48 PM Andres Freund <andres@anarazel.de>
>wrote:
>
>> Hi,
>>
>> On 2019-02-08 12:18:32 +0530, Ashutosh Sharma wrote:
>> > When "ON SELECT" rule is created on a table without columns, it
>> > successfully converts a table into the view. However, when the same
>is
>> > done using CREATE VIEW command, it fails with an error saying:
>"view
>> > must have at least one column". Here is what I'm trying to say:
>> >
>> > -- create table t1 without columns
>> > create table t1();
>> >
>> > -- create table t2 without columns
>> > create table t2();
>> >
>> > -- create ON SELECT rule on t1 - this would convert t1 from table
>to view
>> > create rule "_RETURN" as on select to t1 do instead select * from
>t2;
>> >
>> > -- now check the definition of t1
>> > \d t1
>> >
>> > postgres=# \d+ t1
>> >                             View "public.t1"
>> >  Column | Type | Collation | Nullable | Default | Storage |
>Description
>> >
>--------+------+-----------+----------+---------+---------+-------------
>> > View definition:
>> >  SELECT
>> >    FROM t2;
>> >
>> > The output of "\d+ t1" shows the definition of converted view t1
>which
>> > doesn't have any columns in the select query.
>> >
>> > Now, when i try creating another view with the same definition
>using
>> > CREATE VIEW command, it fails with the error -> ERROR:  view must
>have
>> > at least one column. See below
>> >
>> > postgres=# create view v1 as select from t2;
>> > ERROR:  view must have at least one column
>> >
>> > OR,
>> >
>> > postgres=# create view v1 as select * from t2;
>> > ERROR:  view must have at least one column
>> >
>> > Isn't that a bug in create rule command or am i missing something
>here ?
>> >
>> > If it is a bug, then, attached is the patch that fixes it.
>> >
>> > --
>> > With Regards,
>> > Ashutosh Sharma
>> > EnterpriseDB:http://www.enterprisedb.com
>>
>> > diff --git a/src/backend/rewrite/rewriteDefine.c
>> b/src/backend/rewrite/rewriteDefine.c
>> > index 3496e6f..cb51955 100644
>> > --- a/src/backend/rewrite/rewriteDefine.c
>> > +++ b/src/backend/rewrite/rewriteDefine.c
>> > @@ -473,6 +473,11 @@ DefineQueryRewrite(const char *rulename,
>> >                                                errmsg("could not
>convert
>> table \"%s\" to a view because it has row security enabled",
>> >
>>  RelationGetRelationName(event_relation))));
>> >
>> > +                     if (event_relation->rd_rel->relnatts == 0)
>> > +                             ereport(ERROR,
>> > +
>>  (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
>> > +                                              errmsg("view must
>have at
>> least one column")));
>> > +
>> >                       if (relation_has_policies(event_relation))
>> >                               ereport(ERROR,
>> >
>>  (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
>>
>> Maybe I'm missing something, but why do we want to forbid this?
>
>
>Because pg_dump - produce the output for such case as:
>
> CREATE VIEW public.foo AS
> SELECT
>   FROM public.bar;
>
>which fails to restore because we forbid this in create view:
>
>postgres@20625=#CREATE VIEW public.foo AS
>postgres-#  SELECT
>postgres-#    FROM public.bar;
>ERROR:  view must have at least one column
>postgres@20625=#

You misunderstood my point: I'm asking why we shouldn't remove that check from views, rather than adding it to create
rule.

Andres
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.