On Sat, Feb 9, 2019 at 12:20 AM Ashutosh Sharma <ashu.coe...@gmail.com> wrote: > > On Fri, Feb 8, 2019 at 11:32 PM Tom Lane <t...@sss.pgh.pa.us> wrote: > > > > Ashutosh Sharma <ashu.coe...@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
diff --git a/src/backend/commands/view.c b/src/backend/commands/view.c index 65f4b40..c49ae97 100644 --- a/src/backend/commands/view.c +++ b/src/backend/commands/view.c @@ -111,10 +111,6 @@ DefineVirtualRelation(RangeVar *relation, List *tlist, bool replace, } } - if (attrList == NIL) - ereport(ERROR, - (errcode(ERRCODE_INVALID_TABLE_DEFINITION), - errmsg("view must have at least one column"))); /* * Look up, check permissions on, and lock the creation namespace; also diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out index 141fc6d..ee41c40 100644 --- 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) + -- 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) + +drop materialized view mv0; +drop table mt0; diff --git a/src/test/regress/expected/select_into.out b/src/test/regress/expected/select_into.out index f373fae..26aeffc 100644 --- a/src/test/regress/expected/select_into.out +++ b/src/test/regress/expected/select_into.out @@ -140,3 +140,12 @@ INSERT INTO b SELECT 1 INTO f; ERROR: SELECT ... INTO is not allowed here LINE 1: INSERT INTO b SELECT 1 INTO f; ^ +-- Create a new table named "st1" using table "st0" without columns. +-- Should succeed. +create table st0(); +select * into st1 from st0; +select * from st1; +-- +(0 rows) + +drop table st1, st0; diff --git a/src/test/regress/sql/create_view.sql b/src/test/regress/sql/create_view.sql index 9480030..e5ca690 100644 --- a/src/test/regress/sql/create_view.sql +++ b/src/test/regress/sql/create_view.sql @@ -580,6 +580,11 @@ select pg_get_viewdef('tt23v', true); select pg_get_ruledef(oid, true) from pg_rewrite where ev_class = 'tt23v'::regclass and ev_type = '1'; +-- create view on a table without columns +create table t0(); +create view v0 as select * from t0; +select * from v0; + -- clean up all the random objects we made above \set VERBOSITY terse \\ -- suppress cascade details DROP SCHEMA temp_view_test CASCADE; diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql index d96175a..3da3369 100644 --- a/src/test/regress/sql/matview.sql +++ b/src/test/regress/sql/matview.sql @@ -236,3 +236,10 @@ SELECT mvtest_func(); SELECT * FROM mvtest1; SELECT * FROM mvtest2; ROLLBACK; + +-- create materialized view on a table without columns +create table mt0(); +create materialized view mv0 as select * from mt0; +select * from mv0; +drop materialized view mv0; +drop table mt0; diff --git a/src/test/regress/sql/select_into.sql b/src/test/regress/sql/select_into.sql index a708fef..bd82a19 100644 --- a/src/test/regress/sql/select_into.sql +++ b/src/test/regress/sql/select_into.sql @@ -104,3 +104,10 @@ COPY (SELECT 1 INTO frak UNION SELECT 2) TO 'blob'; SELECT * FROM (SELECT 1 INTO f) bar; CREATE VIEW foo AS SELECT 1 INTO b; INSERT INTO b SELECT 1 INTO f; + +-- Create a new table named "st1" using table "st0" without columns. +-- Should succeed. +create table st0(); +select * into st1 from st0; +select * from st1; +drop table st1, st0;