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

On Fri, Feb 8, 2019 at 3:05 PM Andres Freund <andres@anarazel.de> wrote:
>
>
>
> 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.
>

Here is the second point from my previous response:

"Regarding why we can't allow select on a view without columns given
that select on a table without column is possible, I don't have any
answer :)"

I prepared the patch assuming that the current behaviour of create
view on a table without column is fine.

-- 
With Regards,
Ashutosh Sharma
EnterpriseDB:http://www.enterprisedb.com