Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit1651b9a

Browse files
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 throughexecutor startup before deciding not to actually run the query if WITH NODATA is specified. This behavior is problematic for pg_dump because itmay cause errors to be raised that we would rather not see before aREFRESH MATERIALIZED VIEW command is issued. See for example bug #13907from Marian Krucina. This change is not sufficient to fix that particularbug, 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 returnedcommand tag for WITH NO DATA cases will now be "CREATE MATERIALIZED VIEW"or "CREATE TABLE AS", not "SELECT 0". We could preserve the old behaviorbut it would take more code, and arguably that was just an implementationartifact not intended behavior anyhow.In 9.5 and HEAD, also get rid of the static variable CreateAsReladdr, whichwas trouble waiting to happen; there is not any prohibition on nestedCREATE commands.Back-patch to 9.3 where CREATE MATERIALIZED VIEW was introduced.Michael Paquier and Tom LaneReport: <20160202161407.2778.24659@wrigleys.postgresql.org>
1 parentd372cb1 commit1651b9a

File tree

8 files changed

+410
-181
lines changed

8 files changed

+410
-181
lines changed

‎src/backend/commands/createas.c

Lines changed: 236 additions & 141 deletions
Large diffs are not rendered by default.

‎src/backend/commands/view.c

Lines changed: 5 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -82,25 +82,14 @@ DefineVirtualRelation(RangeVar *relation, List *tlist, bool replace,
8282
attrList=NIL;
8383
foreach(t,tlist)
8484
{
85-
TargetEntry*tle=lfirst(t);
85+
TargetEntry*tle=(TargetEntry*)lfirst(t);
8686

8787
if (!tle->resjunk)
8888
{
89-
ColumnDef*def=makeNode(ColumnDef);
90-
91-
def->colname=pstrdup(tle->resname);
92-
def->typeName=makeTypeNameFromOid(exprType((Node*)tle->expr),
93-
exprTypmod((Node*)tle->expr));
94-
def->inhcount=0;
95-
def->is_local= true;
96-
def->is_not_null= false;
97-
def->is_from_type= false;
98-
def->storage=0;
99-
def->raw_default=NULL;
100-
def->cooked_default=NULL;
101-
def->collClause=NULL;
102-
def->collOid=exprCollation((Node*)tle->expr);
103-
def->location=-1;
89+
ColumnDef*def=makeColumnDef(tle->resname,
90+
exprType((Node*)tle->expr),
91+
exprTypmod((Node*)tle->expr),
92+
exprCollation((Node*)tle->expr));
10493

10594
/*
10695
* It's possible that the column is of a collatable type but the
@@ -117,7 +106,6 @@ DefineVirtualRelation(RangeVar *relation, List *tlist, bool replace,
117106
}
118107
else
119108
Assert(!OidIsValid(def->collOid));
120-
def->constraints=NIL;
121109

122110
attrList=lappend(attrList,def);
123111
}

‎src/backend/nodes/makefuncs.c

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -476,6 +476,36 @@ makeTypeNameFromOid(Oid typeOid, int32 typmod)
476476
returnn;
477477
}
478478

479+
/*
480+
* makeColumnDef -
481+
*build a ColumnDef node to represent a simple column definition.
482+
*
483+
* Type and collation are specified by OID.
484+
* Other properties are all basic to start with.
485+
*/
486+
ColumnDef*
487+
makeColumnDef(constchar*colname,OidtypeOid,int32typmod,OidcollOid)
488+
{
489+
ColumnDef*n=makeNode(ColumnDef);
490+
491+
n->colname=pstrdup(colname);
492+
n->typeName=makeTypeNameFromOid(typeOid,typmod);
493+
n->inhcount=0;
494+
n->is_local= true;
495+
n->is_not_null= false;
496+
n->is_from_type= false;
497+
n->storage=0;
498+
n->raw_default=NULL;
499+
n->cooked_default=NULL;
500+
n->collClause=NULL;
501+
n->collOid=collOid;
502+
n->constraints=NIL;
503+
n->fdwoptions=NIL;
504+
n->location=-1;
505+
506+
returnn;
507+
}
508+
479509
/*
480510
* makeFuncExpr -
481511
*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
externTypeName*makeTypeNameFromNameList(List*names);
7373
externTypeName*makeTypeNameFromOid(OidtypeOid,int32typmod);
7474

75+
externColumnDef*makeColumnDef(constchar*colname,
76+
OidtypeOid,int32typmod,OidcollOid);
77+
7578
externFuncExpr*makeFuncExpr(Oidfuncid,Oidrettype,List*args,
7679
Oidfunccollid,Oidinputcollid,CoercionFormfformat);
7780

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

Lines changed: 51 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -454,28 +454,67 @@ SELECT * FROM boxmv ORDER BY id;
454454
DROP TABLE boxes CASCADE;
455455
NOTICE: drop cascades to materialized view boxmv
456456
-- make sure that column names are handled correctly
457-
CREATE TABLE v (i int, j int);
458-
CREATE MATERIALIZED VIEW mv_v (ii) AS SELECT i, j AS jj FROM v;
459-
ALTER TABLE v RENAME COLUMN i TO x;
460-
INSERT INTO v values (1, 2);
461-
CREATE UNIQUE INDEX mv_v_ii ON mv_v (ii);
462-
REFRESH MATERIALIZED VIEW mv_v;
463-
UPDATE v SET j = 3 WHERE x = 1;
464-
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_v;
465-
SELECT * FROM v;
457+
CREATE TABLE mvtest_v (i int, j int);
458+
CREATE MATERIALIZED VIEW mvtest_mv_v (ii, jj, kk) AS SELECT i, j FROM mvtest_v; -- error
459+
ERROR: too many column names were specified
460+
CREATE MATERIALIZED VIEW mvtest_mv_v (ii, jj) AS SELECT i, j FROM mvtest_v; -- ok
461+
CREATE MATERIALIZED VIEW mvtest_mv_v_2 (ii) AS SELECT i, j FROM mvtest_v; -- ok
462+
CREATE MATERIALIZED VIEW mvtest_mv_v_3 (ii, jj, kk) AS SELECT i, j FROM mvtest_v WITH NO DATA; -- error
463+
ERROR: too many column names were specified
464+
CREATE MATERIALIZED VIEW mvtest_mv_v_3 (ii, jj) AS SELECT i, j FROM mvtest_v WITH NO DATA; -- ok
465+
CREATE MATERIALIZED VIEW mvtest_mv_v_4 (ii) AS SELECT i, j FROM mvtest_v WITH NO DATA; -- ok
466+
ALTER TABLE mvtest_v RENAME COLUMN i TO x;
467+
INSERT INTO mvtest_v values (1, 2);
468+
CREATE UNIQUE INDEX mvtest_mv_v_ii ON mvtest_mv_v (ii);
469+
REFRESH MATERIALIZED VIEW mvtest_mv_v;
470+
UPDATE mvtest_v SET j = 3 WHERE x = 1;
471+
REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv_v;
472+
REFRESH MATERIALIZED VIEW mvtest_mv_v_2;
473+
REFRESH MATERIALIZED VIEW mvtest_mv_v_3;
474+
REFRESH MATERIALIZED VIEW mvtest_mv_v_4;
475+
SELECT * FROM mvtest_v;
466476
x | j
467477
---+---
468478
1 | 3
469479
(1 row)
470480

471-
SELECT * FROMmv_v;
481+
SELECT * FROMmvtest_mv_v;
472482
ii | jj
473483
----+----
474484
1 | 3
475485
(1 row)
476486

477-
DROP TABLE v CASCADE;
478-
NOTICE: drop cascades to materialized view mv_v
487+
SELECT * FROM mvtest_mv_v_2;
488+
ii | j
489+
----+---
490+
1 | 3
491+
(1 row)
492+
493+
SELECT * FROM mvtest_mv_v_3;
494+
ii | jj
495+
----+----
496+
1 | 3
497+
(1 row)
498+
499+
SELECT * FROM mvtest_mv_v_4;
500+
ii | j
501+
----+---
502+
1 | 3
503+
(1 row)
504+
505+
DROP TABLE mvtest_v CASCADE;
506+
NOTICE: drop cascades to 4 other objects
507+
DETAIL: drop cascades to materialized view mvtest_mv_v
508+
drop cascades to materialized view mvtest_mv_v_2
509+
drop cascades to materialized view mvtest_mv_v_3
510+
drop cascades to materialized view mvtest_mv_v_4
511+
-- make sure that create WITH NO DATA does not plan the query (bug #13907)
512+
create materialized view mvtest_error as select 1/0 as x; -- fail
513+
ERROR: division by zero
514+
create materialized view mvtest_error as select 1/0 as x with no data;
515+
refresh materialized view mvtest_error; -- fail here
516+
ERROR: division by zero
517+
drop materialized view mvtest_error;
479518
-- make sure that matview rows can be referenced as source rows (bug #9398)
480519
CREATE TABLE v AS SELECT generate_series(1,10) AS a;
481520
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: 28 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -175,17 +175,34 @@ SELECT * FROM boxmv ORDER BY id;
175175
DROPTABLE boxes CASCADE;
176176

177177
-- make sure that column names are handled correctly
178-
CREATETABLEv (iint, jint);
179-
CREATE MATERIALIZED VIEW mv_v (ii)ASSELECT i, jAS jjFROM v;
180-
ALTERTABLE v RENAME COLUMN i TO x;
181-
INSERT INTO vvalues (1,2);
182-
CREATEUNIQUE INDEXmv_v_iiON mv_v (ii);
183-
REFRESH MATERIALIZED VIEW mv_v;
184-
UPDATE vSET j=3WHERE x=1;
185-
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_v;
186-
SELECT*FROM v;
187-
SELECT*FROM mv_v;
188-
DROPTABLE v CASCADE;
178+
CREATETABLEmvtest_v (iint, jint);
179+
CREATE MATERIALIZED VIEW mvtest_mv_v (ii, jj, kk)ASSELECT i, jFROM mvtest_v;-- error
180+
CREATE MATERIALIZED VIEW mvtest_mv_v (ii, jj)ASSELECT i, jFROM mvtest_v;-- ok
181+
CREATE MATERIALIZED VIEW mvtest_mv_v_2 (ii)ASSELECT i, jFROM mvtest_v;-- ok
182+
CREATE MATERIALIZED VIEW mvtest_mv_v_3 (ii, jj, kk)ASSELECT i, jFROM mvtest_v WITH NO DATA;-- error
183+
CREATE MATERIALIZED VIEW mvtest_mv_v_3 (ii, jj)ASSELECT i, jFROM mvtest_v WITH NO DATA;-- ok
184+
CREATE MATERIALIZED VIEW mvtest_mv_v_4 (ii)ASSELECT i, jFROM mvtest_v WITH NO DATA;-- ok
185+
ALTERTABLE mvtest_v RENAME COLUMN i TO x;
186+
INSERT INTO mvtest_vvalues (1,2);
187+
CREATEUNIQUE INDEXmvtest_mv_v_iiON mvtest_mv_v (ii);
188+
REFRESH MATERIALIZED VIEW mvtest_mv_v;
189+
UPDATE mvtest_vSET j=3WHERE x=1;
190+
REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv_v;
191+
REFRESH MATERIALIZED VIEW mvtest_mv_v_2;
192+
REFRESH MATERIALIZED VIEW mvtest_mv_v_3;
193+
REFRESH MATERIALIZED VIEW mvtest_mv_v_4;
194+
SELECT*FROM mvtest_v;
195+
SELECT*FROM mvtest_mv_v;
196+
SELECT*FROM mvtest_mv_v_2;
197+
SELECT*FROM mvtest_mv_v_3;
198+
SELECT*FROM mvtest_mv_v_4;
199+
DROPTABLE mvtest_v CASCADE;
200+
201+
-- make sure that create WITH NO DATA does not plan the query (bug #13907)
202+
create materialized view mvtest_errorasselect1/0as x;-- fail
203+
create materialized view mvtest_errorasselect1/0as x with no data;
204+
refresh materialized view mvtest_error;-- fail here
205+
drop materialized view mvtest_error;
189206

190207
-- make sure that matview rows can be referenced as source rows (bug #9398)
191208
CREATETABLEvASSELECT 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
DROPSCHEMA selinto_schema CASCADE;
5454
DROPUSER selinto_user;
5555

56+
-- Tests for WITH NO DATA and column name consistency
57+
CREATETABLEctas_base (iint, jint);
58+
INSERT INTO ctas_baseVALUES (1,2);
59+
CREATETABLEctas_nodata (ii, jj, kk)ASSELECT i, jFROM ctas_base;-- Error
60+
CREATETABLEctas_nodata (ii, jj, kk)ASSELECT i, jFROM ctas_base WITH NO DATA;-- Error
61+
CREATETABLEctas_nodata (ii, jj)ASSELECT i, jFROM ctas_base;-- OK
62+
CREATETABLEctas_nodata_2 (ii, jj)ASSELECT i, jFROM ctas_base WITH NO DATA;-- OK
63+
CREATETABLEctas_nodata_3 (ii)ASSELECT i, jFROM ctas_base;-- OK
64+
CREATETABLEctas_nodata_4 (ii)ASSELECT i, jFROM 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+
DROPTABLE ctas_base;
70+
DROPTABLE ctas_nodata;
71+
DROPTABLE ctas_nodata_2;
72+
DROPTABLE ctas_nodata_3;
73+
DROPTABLE 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)
[8]ページ先頭
©2009-2025 Movatter.jp