Thread: ON SELECT rule on a table without columns
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
Attachment
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"
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
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
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;
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=#
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
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)
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.
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
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
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 createrule. > > +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
Attachment
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
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
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
Attachment
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
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
Attachment
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
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