On Sat, Feb 9, 2019 at 12:20 AM Ashutosh Sharma <[email protected]> wrote:
>
> On Fri, Feb 8, 2019 at 11:32 PM Tom Lane <[email protected]> wrote:
> >
> > Ashutosh Sharma <[email protected]> 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;
