ON SELECT rule on a table without columns

classic Classic list List threaded Threaded
16 messages Options
Reply | Threaded
Open this post in threaded view
|

ON SELECT rule on a table without columns

Ashutosh Sharma
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

fix-query-rewrite.patch (980 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: ON SELECT rule on a table without columns

Rushabh Lathia


On Fri, Feb 8, 2019 at 12:18 PM Ashutosh Sharma <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: ON SELECT rule on a table without columns

Andres Freund
In reply to this post by Ashutosh Sharma
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

Reply | Threaded
Open this post in threaded view
|

Re: ON SELECT rule on a table without columns

Rushabh Lathia


On Fri, Feb 8, 2019 at 12:48 PM Andres Freund <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: ON SELECT rule on a table without columns

Ashutosh Sharma
In reply to this post by Andres Freund
On Fri, Feb 8, 2019 at 12:48 PM Andres Freund <[hidden email]> 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)

Reply | Threaded
Open this post in threaded view
|

Re: ON SELECT rule on a table without columns

Andres Freund
In reply to this post by Rushabh Lathia


On February 8, 2019 10:05:03 AM GMT+01:00, Rushabh Lathia <[hidden email]> wrote:

>On Fri, Feb 8, 2019 at 12:48 PM Andres Freund <[hidden email]>
>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.

Reply | Threaded
Open this post in threaded view
|

Re: ON SELECT rule on a table without columns

Ashutosh Sharma
On Fri, Feb 8, 2019 at 3:05 PM Andres Freund <[hidden email]> wrote:

>
>
>
> On February 8, 2019 10:05:03 AM GMT+01:00, Rushabh Lathia <[hidden email]> wrote:
> >On Fri, Feb 8, 2019 at 12:48 PM Andres Freund <[hidden email]>
> >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

Reply | Threaded
Open this post in threaded view
|

Re: ON SELECT rule on a table without columns

Tom Lane-2
In reply to this post by Andres Freund
Andres Freund <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: ON SELECT rule on a table without columns

Ashutosh Sharma
On Fri, Feb 8, 2019 at 7:55 PM Tom Lane <[hidden email]> wrote:
>
> Andres Freund <[hidden email]> 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

allow-create-view-on-table-without-columns.patch (2K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: ON SELECT rule on a table without columns

Tom Lane-2
Ashutosh Sharma <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: ON SELECT rule on a table without columns

Ashutosh Sharma
On Fri, Feb 8, 2019 at 11:32 PM Tom Lane <[hidden email]> wrote:

>
> Ashutosh Sharma <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: ON SELECT rule on a table without columns

Ashutosh Sharma
On Sat, Feb 9, 2019 at 12:20 AM Ashutosh Sharma <[hidden email]> wrote:

>
> On Fri, Feb 8, 2019 at 11:32 PM Tom Lane <[hidden email]> wrote:
> >
> > Ashutosh Sharma <[hidden email]> 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

allow-create-view-on-table-without-columns-v2.patch (5K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: ON SELECT rule on a table without columns

Andres Freund
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

Reply | Threaded
Open this post in threaded view
|

Re: ON SELECT rule on a table without columns

Ashutosh Sharma
Thanks Andres for the quick review.

On Mon, Feb 11, 2019 at 3:52 PM Andres Freund <[hidden email]> 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

allow-create-view-on-table-without-columns-v3.patch (8K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: ON SELECT rule on a table without columns

Tom Lane-2
Ashutosh Sharma <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: ON SELECT rule on a table without columns

Ashutosh Sharma
On Sun, Feb 17, 2019 at 11:10 PM Tom Lane <[hidden email]> wrote:
>
> Ashutosh Sharma <[hidden email]> 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