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

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
securityenabled",
 
> >                                                               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? Given
> that we these days allows selects without columns, I see no reason to
> require this for views.  The view error check long predates allowing
> SELECT and CREATE TABLE without columns. I think it's existence is just
> an oversight.  Tom, you did relaxed the permissive cases, any opinion?
>

That's because, we don't allow creation of a view on a table without
columns. So, shouldn't we do the same when converting table to a view
that doesn't have any column in it. 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 can see that, even SELECT without any targetlist or table name in
it, works fine, see this,

postgres=# select;
--
(1 row)