Horton

Thread

Commits

  1. Fix CREATE VIEW to allow zero-column views.

  1. ON SELECT rule on a table without columns

    Ashutosh Sharma <ashu.coek88@gmail.com> — 2019-02-08T06:48:32Z

    Hi All,

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

  2. Re: ON SELECT rule on a table without columns

    Rushabh Lathia <rushabh.lathia@gmail.com> — 2019-02-08T07:11:23Z

    On Fri, Feb 8, 2019 at 12:18 PM Ashutosh Sharma <ashu.coek88@gmail.com>
wrote:

> Hi All,
>
> 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 ?
>

Yes, it's looks like a bug to me.

>
> If it is a bug, then, attached is the patch that fixes it.
>

I had quick glance to the patch - here are few commits:

1)

+            if (event_relation->rd_rel->relnatts == 0)

Can't use direct relnatts - as need to consider attisdropped.

2)
I think you may like to change the error message to be in-line with
the other error message in the similar code area.

May be something like:
"could not convert table \"%s\" to a view because table does not have any
column"


Regards,
Rushabh Lathia
www.EnterpriseDB.com

  3. Re: ON SELECT rule on a table without columns

    Andres Freund <andres@anarazel.de> — 2019-02-08T07:18:39Z

    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? 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?

Greetings,

Andres Freund

  4. Re: ON SELECT rule on a table without columns

    Rushabh Lathia <rushabh.lathia@gmail.com> — 2019-02-08T09:05:03Z

    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=#

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?
>
> Greetings,
>
> Andres Freund
>
>

-- 
Rushabh Lathia

  5. Re: ON SELECT rule on a table without columns

    Ashutosh Sharma <ashu.coek88@gmail.com> — 2019-02-08T09:16:04Z

    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? 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)

  6. Re: ON SELECT rule on a table without columns

    Andres Freund <andres@anarazel.de> — 2019-02-08T09:35:03Z

    
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.

  7. Re: ON SELECT rule on a table without columns

    Ashutosh Sharma <ashu.coek88@gmail.com> — 2019-02-08T10:07:00Z

    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

  8. Re: ON SELECT rule on a table without columns

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-02-08T14:25:07Z

    Andres Freund <andres@anarazel.de> writes:
> You misunderstood my point: I'm asking why we shouldn't remove that check from views, rather than adding it to create rule.

+1.  This seems pretty obviously to be something we just missed when
we changed things to allow zero-column tables.

			regards, tom lane

  9. Re: ON SELECT rule on a table without columns

    Ashutosh Sharma <ashu.coek88@gmail.com> — 2019-02-08T16:27:14Z

    On Fri, Feb 8, 2019 at 7:55 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Andres Freund <andres@anarazel.de> writes:
> > You misunderstood my point: I'm asking why we shouldn't remove that check from views, rather than adding it to create rule.
>
> +1.  This seems pretty obviously to be something we just missed when
> we changed things to allow zero-column tables.
>

Thanks Andres for bringing up that point and thanks Tom for the confirmation.

Attached is the patch that allows us to create view on a table without
columns. I've also added some test-cases for it in create_view.sql.
Please have a look and let me know your opinion.

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

  10. Re: ON SELECT rule on a table without columns

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-02-08T18:02:54Z

    Ashutosh Sharma <ashu.coek88@gmail.com> writes:
> Attached is the patch that allows us to create view on a table without
> columns. I've also added some test-cases for it in create_view.sql.
> Please have a look and let me know your opinion.

Haven't read the patch, but a question seems in order here: should
we regard this as a back-patchable bug fix?  The original example
shows that it's possible to create a zero-column view in existing
releases, which I believe would then lead to dump/reload failures.
So that seems to qualify as a bug not just a missing feature.
On the other hand, given the lack of field complaints, maybe it's
not worth the trouble to back-patch.  I don't have a strong
opinion either way.

BTW, has anyone checked on what the matview code paths will do?
Or SELECT INTO?

			regards, tom lane

  11. Re: ON SELECT rule on a table without columns

    Ashutosh Sharma <ashu.coek88@gmail.com> — 2019-02-08T18:50:51Z

    On Fri, Feb 8, 2019 at 11:32 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Ashutosh Sharma <ashu.coek88@gmail.com> writes:
> > Attached is the patch that allows us to create view on a table without
> > columns. I've also added some test-cases for it in create_view.sql.
> > Please have a look and let me know your opinion.
>
> Haven't read the patch, but a question seems in order here: should
> we regard this as a back-patchable bug fix?  The original example
> shows that it's possible to create a zero-column view in existing
> releases, which I believe would then lead to dump/reload failures.
> So that seems to qualify as a bug not just a missing feature.
> On the other hand, given the lack of field complaints, maybe it's
> not worth the trouble to back-patch.  I don't have a strong
> opinion either way.
>

In my opinion, this looks like a bug fix that needs to be back ported,
else, we might encounter dump/restore failure in some cases, like the
one described in the first email.

> BTW, has anyone checked on what the matview code paths will do?
> Or SELECT INTO?
>

I just checked on that and found that both mat view and SELECT INTO
statement works like CREATE TABLE AS command and it doesn't really
care about the target list of the source table unlike normal views
which would error out when the source table has no columns.

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

  12. Re: ON SELECT rule on a table without columns

    Ashutosh Sharma <ashu.coek88@gmail.com> — 2019-02-11T10:09:03Z

    On Sat, Feb 9, 2019 at 12:20 AM Ashutosh Sharma <ashu.coek88@gmail.com> wrote:
>
> On Fri, Feb 8, 2019 at 11:32 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >
> > Ashutosh Sharma <ashu.coek88@gmail.com> writes:
> > > Attached is the patch that allows us to create view on a table without
> > > columns. I've also added some test-cases for it in create_view.sql.
> > > Please have a look and let me know your opinion.
> >
> > Haven't read the patch, but a question seems in order here: should
> > we regard this as a back-patchable bug fix?  The original example
> > shows that it's possible to create a zero-column view in existing
> > releases, which I believe would then lead to dump/reload failures.
> > So that seems to qualify as a bug not just a missing feature.
> > On the other hand, given the lack of field complaints, maybe it's
> > not worth the trouble to back-patch.  I don't have a strong
> > opinion either way.
> >
>
> In my opinion, this looks like a bug fix that needs to be back ported,
> else, we might encounter dump/restore failure in some cases, like the
> one described in the first email.
>
> > BTW, has anyone checked on what the matview code paths will do?
> > Or SELECT INTO?
> >
>
> I just checked on that and found that both mat view and SELECT INTO
> statement works like CREATE TABLE AS command and it doesn't really
> care about the target list of the source table unlike normal views
> which would error out when the source table has no columns.
>

Added the regression test-cases for mat views and SELECT INTO
statements in the attached patch. Earlier patch just had the
test-cases for normal views along with the fix.

Andres, Tom, Please have a look into the attached patch and let me
know if I'm still missing something. Thank you.

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

  13. Re: ON SELECT rule on a table without columns

    Andres Freund <andres@anarazel.de> — 2019-02-11T10:21:56Z

    Hi,

On 2019-02-11 15:39:03 +0530, Ashutosh Sharma wrote:
> Andres, Tom, Please have a look into the attached patch and let me
> know if I'm still missing something. Thank you.

> --- a/src/test/regress/expected/create_view.out
> +++ b/src/test/regress/expected/create_view.out
> @@ -1706,9 +1706,16 @@ select pg_get_ruledef(oid, true) from pg_rewrite
>       43 AS col_b;
>  (1 row)
>  
> +-- create view on a table without columns
> +create table t0();
> +create view v0 as select * from t0;
> +select * from v0;
> +--
> +(0 rows)

I suggest also adding a view that select zero columns, where the
unerlying table has columns.

I think it'd be good to name the view in a way that's a bit more unique,
and leaving it in place. That way pg_dump can be tested with this too
(and mostly would be tested via pg_upgrade's tests).


>  -- clean up all the random objects we made above
>  \set VERBOSITY terse \\ -- suppress cascade details
>  DROP SCHEMA temp_view_test CASCADE;
>  NOTICE:  drop cascades to 27 other objects
>  DROP SCHEMA testviewschm2 CASCADE;
> -NOTICE:  drop cascades to 62 other objects
> +NOTICE:  drop cascades to 64 other objects
> diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out
> index d0121a7..f1d24e6 100644
> --- a/src/test/regress/expected/matview.out
> +++ b/src/test/regress/expected/matview.out
> @@ -589,3 +589,12 @@ SELECT * FROM mvtest2;
>  ERROR:  materialized view "mvtest2" has not been populated
>  HINT:  Use the REFRESH MATERIALIZED VIEW command.
>  ROLLBACK;
> +-- create materialized view on a table without columns
> +create table mt0();
> +create materialized view mv0 as select * from mt0;
> +select * from mv0;
> +--
> +(0 rows)

Same.


Thanks!

Greetings,

Andres Freund

  14. Re: ON SELECT rule on a table without columns

    Ashutosh Sharma <ashu.coek88@gmail.com> — 2019-02-11T12:20:42Z

    Thanks Andres for the quick review.

On Mon, Feb 11, 2019 at 3:52 PM Andres Freund <andres@anarazel.de> wrote:
>
> Hi,
>
> On 2019-02-11 15:39:03 +0530, Ashutosh Sharma wrote:
> > Andres, Tom, Please have a look into the attached patch and let me
> > know if I'm still missing something. Thank you.
>
> > --- a/src/test/regress/expected/create_view.out
> > +++ b/src/test/regress/expected/create_view.out
> > @@ -1706,9 +1706,16 @@ select pg_get_ruledef(oid, true) from pg_rewrite
> >       43 AS col_b;
> >  (1 row)
> >
> > +-- create view on a table without columns
> > +create table t0();
> > +create view v0 as select * from t0;
> > +select * from v0;
> > +--
> > +(0 rows)
>
> I suggest also adding a view that select zero columns, where the
> unerlying table has columns.
>

Done.

> I think it'd be good to name the view in a way that's a bit more unique,
> and leaving it in place. That way pg_dump can be tested with this too
> (and mostly would be tested via pg_upgrade's tests).
>

Renamed view to something like -> 'view_no_column' and
'view_zero_column' and didn't drop it so that it so that it gets
tested with pg_upgrade.

>
> >  -- clean up all the random objects we made above
> >  \set VERBOSITY terse \\ -- suppress cascade details
> >  DROP SCHEMA temp_view_test CASCADE;
> >  NOTICE:  drop cascades to 27 other objects
> >  DROP SCHEMA testviewschm2 CASCADE;
> > -NOTICE:  drop cascades to 62 other objects
> > +NOTICE:  drop cascades to 64 other objects
> > diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out
> > index d0121a7..f1d24e6 100644
> > --- a/src/test/regress/expected/matview.out
> > +++ b/src/test/regress/expected/matview.out
> > @@ -589,3 +589,12 @@ SELECT * FROM mvtest2;
> >  ERROR:  materialized view "mvtest2" has not been populated
> >  HINT:  Use the REFRESH MATERIALIZED VIEW command.
> >  ROLLBACK;
> > +-- create materialized view on a table without columns
> > +create table mt0();
> > +create materialized view mv0 as select * from mt0;
> > +select * from mv0;
> > +--
> > +(0 rows)
>
> Same.
>
>

Done.

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

  15. Re: ON SELECT rule on a table without columns

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-02-17T17:40:21Z

    Ashutosh Sharma <ashu.coek88@gmail.com> writes:
> [ allow-create-view-on-table-without-columns-v3.patch ]

Pushed.  I revised the test cases a bit --- notably, I wanted to be
sure we exercised pg_dump's createDummyViewAsClause for this, especially
after noticing that it wasn't being tested at all before :-(

			regards, tom lane

  16. Re: ON SELECT rule on a table without columns

    Ashutosh Sharma <ashu.coek88@gmail.com> — 2019-02-18T05:51:10Z

    On Sun, Feb 17, 2019 at 11:10 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Ashutosh Sharma <ashu.coek88@gmail.com> writes:
> > [ allow-create-view-on-table-without-columns-v3.patch ]
>
> Pushed.  I revised the test cases a bit --- notably, I wanted to be
> sure we exercised pg_dump's createDummyViewAsClause for this, especially
> after noticing that it wasn't being tested at all before :-(
>

Okay. Thanks for that changes in the test-cases and committing the patch.

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