Skip to content

Commit 17bfef8

Browse files
tglsfdctglsfdc
committed
Fix CREATE MATVIEW/CREATE TABLE AS ... WITH NO DATA to not plan the query.
Previously, these commands always planned the given query and went through executor startup before deciding not to actually run the query if WITH NO DATA is specified. This behavior is problematic for pg_dump because it may cause errors to be raised that we would rather not see before a REFRESH MATERIALIZED VIEW command is issued. See for example bug #13907 from Marian Krucina. This change is not sufficient to fix that particular bug, because we also need to tweak pg_dump to issue the REFRESH later, but it's a necessary step on the way. A user-visible side effect of doing things this way is that the returned command tag for WITH NO DATA cases will now be "CREATE MATERIALIZED VIEW" or "CREATE TABLE AS", not "SELECT 0". We could preserve the old behavior but it would take more code, and arguably that was just an implementation artifact not intended behavior anyhow. In 9.5 and HEAD, also get rid of the static variable CreateAsReladdr, which was trouble waiting to happen; there is not any prohibition on nested CREATE commands. Back-patch to 9.3 where CREATE MATERIALIZED VIEW was introduced. Michael Paquier and Tom Lane Report: <20160202161407.2778.24659@wrigleys.postgresql.org>
1 parent 28f294a commit 17bfef8

File tree

8 files changed

+399
-166
lines changed

8 files changed

+399
-166
lines changed

‎src/backend/commands/createas.c

Lines changed: 230 additions & 131 deletions
Large diffs are not rendered by default.

‎src/backend/commands/view.c

Lines changed: 5 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -63,24 +63,14 @@ DefineVirtualRelation(RangeVar *relation, List *tlist, bool replace,
6363
attrList = NIL;
6464
foreach(t, tlist)
6565
{
66-
TargetEntry *tle = lfirst(t);
66+
TargetEntry *tle = (TargetEntry *) lfirst(t);
6767

6868
if (!tle->resjunk)
6969
{
70-
ColumnDef *def = makeNode(ColumnDef);
71-
72-
def->colname = pstrdup(tle->resname);
73-
def->typeName = makeTypeNameFromOid(exprType((Node *) tle->expr),
74-
exprTypmod((Node *) tle->expr));
75-
def->inhcount = 0;
76-
def->is_local = true;
77-
def->is_not_null = false;
78-
def->is_from_type = false;
79-
def->storage = 0;
80-
def->raw_default = NULL;
81-
def->cooked_default = NULL;
82-
def->collClause = NULL;
83-
def->collOid = exprCollation((Node *) tle->expr);
70+
ColumnDef *def = makeColumnDef(tle->resname,
71+
exprType((Node *) tle->expr),
72+
exprTypmod((Node *) tle->expr),
73+
exprCollation((Node *) tle->expr));
8474

8575
/*
8676
* It's possible that the column is of a collatable type but the
@@ -97,7 +87,6 @@ DefineVirtualRelation(RangeVar *relation, List *tlist, bool replace,
9787
}
9888
else
9989
Assert(!OidIsValid(def->collOid));
100-
def->constraints = NIL;
10190

10291
attrList = lappend(attrList, def);
10392
}

‎src/backend/nodes/makefuncs.c

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -445,6 +445,35 @@ makeTypeNameFromOid(Oid typeOid, int32 typmod)
445445
return n;
446446
}
447447

448+
/*
449+
* makeColumnDef -
450+
* build a ColumnDef node to represent a simple column definition.
451+
*
452+
* Type and collation are specified by OID.
453+
* Other properties are all basic to start with.
454+
*/
455+
ColumnDef *
456+
makeColumnDef(const char *colname, Oid typeOid, int32 typmod, Oid collOid)
457+
{
458+
ColumnDef *n = makeNode(ColumnDef);
459+
460+
n->colname = pstrdup(colname);
461+
n->typeName = makeTypeNameFromOid(typeOid, typmod);
462+
n->inhcount = 0;
463+
n->is_local = true;
464+
n->is_not_null = false;
465+
n->is_from_type = false;
466+
n->storage = 0;
467+
n->raw_default = NULL;
468+
n->cooked_default = NULL;
469+
n->collClause = NULL;
470+
n->collOid = collOid;
471+
n->constraints = NIL;
472+
n->fdwoptions = NIL;
473+
474+
return n;
475+
}
476+
448477
/*
449478
* makeFuncExpr -
450479
* build an expression tree representing a function call.

‎src/include/nodes/makefuncs.h

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -72,6 +72,9 @@ extern TypeName *makeTypeName(char *typnam);
7272
extern TypeName *makeTypeNameFromNameList(List *names);
7373
extern TypeName *makeTypeNameFromOid(Oid typeOid, int32 typmod);
7474

75+
extern ColumnDef *makeColumnDef(const char *colname,
76+
Oid typeOid, int32 typmod, Oid collOid);
77+
7578
extern FuncExpr *makeFuncExpr(Oid funcid, Oid rettype, List *args,
7679
Oid funccollid, Oid inputcollid, CoercionForm fformat);
7780

‎src/test/regress/expected/matview.out

Lines changed: 49 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -392,26 +392,65 @@ CREATE MATERIALIZED VIEW mv2 AS SELECT * FROM mv1
392392
DROP MATERIALIZED VIEW mv1 CASCADE;
393393
NOTICE: drop cascades to materialized view mv2
394394
-- make sure that column names are handled correctly
395-
CREATE TABLE v (i int, j int);
396-
CREATE MATERIALIZED VIEW mv_v (ii) AS SELECT i, j AS jj FROM v;
397-
ALTER TABLE v RENAME COLUMN i TO x;
398-
INSERT INTO v values (1, 2);
399-
CREATE UNIQUE INDEX mv_v_ii ON mv_v (ii);
400-
REFRESH MATERIALIZED VIEW mv_v;
401-
SELECT * FROM v;
395+
CREATE TABLE mvtest_v (i int, j int);
396+
CREATE MATERIALIZED VIEW mvtest_mv_v (ii, jj, kk) AS SELECT i, j FROM mvtest_v; -- error
397+
ERROR: too many column names were specified
398+
CREATE MATERIALIZED VIEW mvtest_mv_v (ii, jj) AS SELECT i, j FROM mvtest_v; -- ok
399+
CREATE MATERIALIZED VIEW mvtest_mv_v_2 (ii) AS SELECT i, j FROM mvtest_v; -- ok
400+
CREATE MATERIALIZED VIEW mvtest_mv_v_3 (ii, jj, kk) AS SELECT i, j FROM mvtest_v WITH NO DATA; -- error
401+
ERROR: too many column names were specified
402+
CREATE MATERIALIZED VIEW mvtest_mv_v_3 (ii, jj) AS SELECT i, j FROM mvtest_v WITH NO DATA; -- ok
403+
CREATE MATERIALIZED VIEW mvtest_mv_v_4 (ii) AS SELECT i, j FROM mvtest_v WITH NO DATA; -- ok
404+
ALTER TABLE mvtest_v RENAME COLUMN i TO x;
405+
INSERT INTO mvtest_v values (1, 2);
406+
CREATE UNIQUE INDEX mvtest_mv_v_ii ON mvtest_mv_v (ii);
407+
REFRESH MATERIALIZED VIEW mvtest_mv_v;
408+
REFRESH MATERIALIZED VIEW mvtest_mv_v_2;
409+
REFRESH MATERIALIZED VIEW mvtest_mv_v_3;
410+
REFRESH MATERIALIZED VIEW mvtest_mv_v_4;
411+
SELECT * FROM mvtest_v;
402412
x | j
403413
---+---
404414
1 | 2
405415
(1 row)
406416

407-
SELECT * FROM mv_v;
417+
SELECT * FROM mvtest_mv_v;
408418
ii | jj
409419
----+----
410420
1 | 2
411421
(1 row)
412422

413-
DROP TABLE v CASCADE;
414-
NOTICE: drop cascades to materialized view mv_v
423+
SELECT * FROM mvtest_mv_v_2;
424+
ii | j
425+
----+---
426+
1 | 2
427+
(1 row)
428+
429+
SELECT * FROM mvtest_mv_v_3;
430+
ii | jj
431+
----+----
432+
1 | 2
433+
(1 row)
434+
435+
SELECT * FROM mvtest_mv_v_4;
436+
ii | j
437+
----+---
438+
1 | 2
439+
(1 row)
440+
441+
DROP TABLE mvtest_v CASCADE;
442+
NOTICE: drop cascades to 4 other objects
443+
DETAIL: drop cascades to materialized view mvtest_mv_v
444+
drop cascades to materialized view mvtest_mv_v_2
445+
drop cascades to materialized view mvtest_mv_v_3
446+
drop cascades to materialized view mvtest_mv_v_4
447+
-- make sure that create WITH NO DATA does not plan the query (bug #13907)
448+
create materialized view mvtest_error as select 1/0 as x; -- fail
449+
ERROR: division by zero
450+
create materialized view mvtest_error as select 1/0 as x with no data;
451+
refresh materialized view mvtest_error; -- fail here
452+
ERROR: division by zero
453+
drop materialized view mvtest_error;
415454
-- make sure that matview rows can be referenced as source rows (bug #9398)
416455
CREATE TABLE v AS SELECT generate_series(1,10) AS a;
417456
CREATE MATERIALIZED VIEW mv_v AS SELECT a FROM v WHERE a <= 5;

‎src/test/regress/expected/select_into.out

Lines changed: 38 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -50,6 +50,44 @@ DETAIL: drop cascades to table selinto_schema.tmp1
5050
drop cascades to table selinto_schema.tmp2
5151
drop cascades to table selinto_schema.tmp3
5252
DROP USER selinto_user;
53+
-- Tests for WITH NO DATA and column name consistency
54+
CREATE TABLE ctas_base (i int, j int);
55+
INSERT INTO ctas_base VALUES (1, 2);
56+
CREATE TABLE ctas_nodata (ii, jj, kk) AS SELECT i, j FROM ctas_base; -- Error
57+
ERROR: too many column names were specified
58+
CREATE TABLE ctas_nodata (ii, jj, kk) AS SELECT i, j FROM ctas_base WITH NO DATA; -- Error
59+
ERROR: too many column names were specified
60+
CREATE TABLE ctas_nodata (ii, jj) AS SELECT i, j FROM ctas_base; -- OK
61+
CREATE TABLE ctas_nodata_2 (ii, jj) AS SELECT i, j FROM ctas_base WITH NO DATA; -- OK
62+
CREATE TABLE ctas_nodata_3 (ii) AS SELECT i, j FROM ctas_base; -- OK
63+
CREATE TABLE ctas_nodata_4 (ii) AS SELECT i, j FROM ctas_base WITH NO DATA; -- OK
64+
SELECT * FROM ctas_nodata;
65+
ii | jj
66+
----+----
67+
1 | 2
68+
(1 row)
69+
70+
SELECT * FROM ctas_nodata_2;
71+
ii | jj
72+
----+----
73+
(0 rows)
74+
75+
SELECT * FROM ctas_nodata_3;
76+
ii | j
77+
----+---
78+
1 | 2
79+
(1 row)
80+
81+
SELECT * FROM ctas_nodata_4;
82+
ii | j
83+
----+---
84+
(0 rows)
85+
86+
DROP TABLE ctas_base;
87+
DROP TABLE ctas_nodata;
88+
DROP TABLE ctas_nodata_2;
89+
DROP TABLE ctas_nodata_3;
90+
DROP TABLE ctas_nodata_4;
5391
--
5492
-- CREATE TABLE AS/SELECT INTO as last command in a SQL function
5593
-- have been known to cause problems

‎src/test/regress/sql/matview.sql

Lines changed: 26 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -132,15 +132,32 @@ CREATE MATERIALIZED VIEW mv2 AS SELECT * FROM mv1
132132
DROP MATERIALIZED VIEW mv1 CASCADE;
133133

134134
-- make sure that column names are handled correctly
135-
CREATE TABLE v (i int, j int);
136-
CREATE MATERIALIZED VIEW mv_v (ii) AS SELECT i, j AS jj FROM v;
137-
ALTER TABLE v RENAME COLUMN i TO x;
138-
INSERT INTO v values (1, 2);
139-
CREATE UNIQUE INDEX mv_v_ii ON mv_v (ii);
140-
REFRESH MATERIALIZED VIEW mv_v;
141-
SELECT * FROM v;
142-
SELECT * FROM mv_v;
143-
DROP TABLE v CASCADE;
135+
CREATE TABLE mvtest_v (i int, j int);
136+
CREATE MATERIALIZED VIEW mvtest_mv_v (ii, jj, kk) AS SELECT i, j FROM mvtest_v; -- error
137+
CREATE MATERIALIZED VIEW mvtest_mv_v (ii, jj) AS SELECT i, j FROM mvtest_v; -- ok
138+
CREATE MATERIALIZED VIEW mvtest_mv_v_2 (ii) AS SELECT i, j FROM mvtest_v; -- ok
139+
CREATE MATERIALIZED VIEW mvtest_mv_v_3 (ii, jj, kk) AS SELECT i, j FROM mvtest_v WITH NO DATA; -- error
140+
CREATE MATERIALIZED VIEW mvtest_mv_v_3 (ii, jj) AS SELECT i, j FROM mvtest_v WITH NO DATA; -- ok
141+
CREATE MATERIALIZED VIEW mvtest_mv_v_4 (ii) AS SELECT i, j FROM mvtest_v WITH NO DATA; -- ok
142+
ALTER TABLE mvtest_v RENAME COLUMN i TO x;
143+
INSERT INTO mvtest_v values (1, 2);
144+
CREATE UNIQUE INDEX mvtest_mv_v_ii ON mvtest_mv_v (ii);
145+
REFRESH MATERIALIZED VIEW mvtest_mv_v;
146+
REFRESH MATERIALIZED VIEW mvtest_mv_v_2;
147+
REFRESH MATERIALIZED VIEW mvtest_mv_v_3;
148+
REFRESH MATERIALIZED VIEW mvtest_mv_v_4;
149+
SELECT * FROM mvtest_v;
150+
SELECT * FROM mvtest_mv_v;
151+
SELECT * FROM mvtest_mv_v_2;
152+
SELECT * FROM mvtest_mv_v_3;
153+
SELECT * FROM mvtest_mv_v_4;
154+
DROP TABLE mvtest_v CASCADE;
155+
156+
-- make sure that create WITH NO DATA does not plan the query (bug #13907)
157+
create materialized view mvtest_error as select 1/0 as x; -- fail
158+
create materialized view mvtest_error as select 1/0 as x with no data;
159+
refresh materialized view mvtest_error; -- fail here
160+
drop materialized view mvtest_error;
144161

145162
-- make sure that matview rows can be referenced as source rows (bug #9398)
146163
CREATE TABLE v AS SELECT generate_series(1,10) AS a;

‎src/test/regress/sql/select_into.sql

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,25 @@ RESET SESSION AUTHORIZATION;
5353
DROP SCHEMA selinto_schema CASCADE;
5454
DROP USER selinto_user;
5555

56+
-- Tests for WITH NO DATA and column name consistency
57+
CREATE TABLE ctas_base (i int, j int);
58+
INSERT INTO ctas_base VALUES (1, 2);
59+
CREATE TABLE ctas_nodata (ii, jj, kk) AS SELECT i, j FROM ctas_base; -- Error
60+
CREATE TABLE ctas_nodata (ii, jj, kk) AS SELECT i, j FROM ctas_base WITH NO DATA; -- Error
61+
CREATE TABLE ctas_nodata (ii, jj) AS SELECT i, j FROM ctas_base; -- OK
62+
CREATE TABLE ctas_nodata_2 (ii, jj) AS SELECT i, j FROM ctas_base WITH NO DATA; -- OK
63+
CREATE TABLE ctas_nodata_3 (ii) AS SELECT i, j FROM ctas_base; -- OK
64+
CREATE TABLE ctas_nodata_4 (ii) AS SELECT i, j FROM ctas_base WITH NO DATA; -- OK
65+
SELECT * FROM ctas_nodata;
66+
SELECT * FROM ctas_nodata_2;
67+
SELECT * FROM ctas_nodata_3;
68+
SELECT * FROM ctas_nodata_4;
69+
DROP TABLE ctas_base;
70+
DROP TABLE ctas_nodata;
71+
DROP TABLE ctas_nodata_2;
72+
DROP TABLE ctas_nodata_3;
73+
DROP TABLE ctas_nodata_4;
74+
5675
--
5776
-- CREATE TABLE AS/SELECT INTO as last command in a SQL function
5877
-- have been known to cause problems

0 commit comments

Comments
 (0)