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

Commit2523928

Browse files
committed
Reject change of output-column collation in CREATE OR REPLACE VIEW.
checkViewTupleDesc() didn't get the memo that it should verifysame attcollation along with same type/typmod. (A quick scandid not find other similar oversights.)Per bug #17404 from Pierre-Aurélien Georges. On another dayI might've back-patched this, but today I'm feeling paranoidabout unnecessary behavioral changes in back branches.Discussion:https://postgr.es/m/17404-8a4a270ef30a6709@postgresql.org
1 parent4d373e0 commit2523928

File tree

3 files changed

+56
-23
lines changed

3 files changed

+56
-23
lines changed

‎src/backend/commands/view.c

Lines changed: 18 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -282,7 +282,12 @@ checkViewTupleDesc(TupleDesc newdesc, TupleDesc olddesc)
282282
NameStr(oldattr->attname),
283283
NameStr(newattr->attname)),
284284
errhint("Use ALTER VIEW ... RENAME COLUMN ... to change name of view column instead.")));
285-
/* XXX would it be safe to allow atttypmod to change? Not sure */
285+
286+
/*
287+
* We cannot allow type, typmod, or collation to change, since these
288+
* properties may be embedded in Vars of other views/rules referencing
289+
* this one. Other column attributes can be ignored.
290+
*/
286291
if (newattr->atttypid!=oldattr->atttypid||
287292
newattr->atttypmod!=oldattr->atttypmod)
288293
ereport(ERROR,
@@ -293,7 +298,18 @@ checkViewTupleDesc(TupleDesc newdesc, TupleDesc olddesc)
293298
oldattr->atttypmod),
294299
format_type_with_typemod(newattr->atttypid,
295300
newattr->atttypmod))));
296-
/* We can ignore the remaining attributes of an attribute... */
301+
302+
/*
303+
* At this point, attcollations should be both valid or both invalid,
304+
* so applying get_collation_name unconditionally should be fine.
305+
*/
306+
if (newattr->attcollation!=oldattr->attcollation)
307+
ereport(ERROR,
308+
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
309+
errmsg("cannot change collation of view column \"%s\" from \"%s\" to \"%s\"",
310+
NameStr(oldattr->attname),
311+
get_collation_name(oldattr->attcollation),
312+
get_collation_name(newattr->attcollation))));
297313
}
298314

299315
/*

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

Lines changed: 21 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -54,27 +54,27 @@ CREATE VIEW key_dependent_view_no_cols AS
5454
--
5555
-- CREATE OR REPLACE VIEW
5656
--
57-
CREATE TABLE viewtest_tbl (a int, b int);
57+
CREATE TABLE viewtest_tbl (a int, b int, c numeric(10,1), d text COLLATE "C");
5858
COPY viewtest_tbl FROM stdin;
5959
CREATE OR REPLACE VIEW viewtest AS
6060
SELECT * FROM viewtest_tbl;
6161
CREATE OR REPLACE VIEW viewtest AS
6262
SELECT * FROM viewtest_tbl WHERE a > 10;
6363
SELECT * FROM viewtest;
64-
a | b
65-
----+----
66-
15 | 20
67-
20 | 25
64+
a | b| c | d
65+
----+----+-----+-------
66+
15 | 20 | 3.3 | xyzz
67+
20 | 25 | 4.4 | xyzzy
6868
(2 rows)
6969

7070
CREATE OR REPLACE VIEW viewtest AS
71-
SELECT a, b FROM viewtest_tbl WHERE a > 5 ORDER BY b DESC;
71+
SELECT a, b, c, d FROM viewtest_tbl WHERE a > 5 ORDER BY b DESC;
7272
SELECT * FROM viewtest;
73-
a | b
74-
----+----
75-
20 | 25
76-
15 | 20
77-
10 | 15
73+
a | b| c | d
74+
----+----+-----+-------
75+
20 | 25 | 4.4 | xyzzy
76+
15 | 20 | 3.3 | xyzz
77+
10 | 15 | 2.2 | xyz
7878
(3 rows)
7979

8080
-- should fail
@@ -88,11 +88,19 @@ ERROR: cannot change name of view column "a" to "?column?"
8888
HINT: Use ALTER VIEW ... RENAME COLUMN ... to change name of view column instead.
8989
-- should fail
9090
CREATE OR REPLACE VIEW viewtest AS
91-
SELECT a, b::numeric FROM viewtest_tbl;
91+
SELECT a, b::numeric, c, d FROM viewtest_tbl;
9292
ERROR: cannot change data type of view column "b" from integer to numeric
93+
-- should fail
94+
CREATE OR REPLACE VIEW viewtest AS
95+
SELECT a, b, c::numeric(10,2), d FROM viewtest_tbl;
96+
ERROR: cannot change data type of view column "c" from numeric(10,1) to numeric(10,2)
97+
-- should fail
98+
CREATE OR REPLACE VIEW viewtest AS
99+
SELECT a, b, c, d COLLATE "POSIX" FROM viewtest_tbl;
100+
ERROR: cannot change collation of view column "d" from "C" to "POSIX"
93101
-- should work
94102
CREATE OR REPLACE VIEW viewtest AS
95-
SELECT a, b, 0 ASc FROM viewtest_tbl;
103+
SELECT a, b,c, d,0 ASe FROM viewtest_tbl;
96104
DROP VIEW viewtest;
97105
DROP TABLE viewtest_tbl;
98106
-- tests for temporary views

‎src/test/regress/sql/create_view.sql

Lines changed: 17 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -67,12 +67,13 @@ CREATE VIEW key_dependent_view_no_cols AS
6767
-- CREATE OR REPLACE VIEW
6868
--
6969

70-
CREATETABLEviewtest_tbl (aint, bint);
70+
CREATETABLEviewtest_tbl (aint, bint, cnumeric(10,1), dtext COLLATE"C");
71+
7172
COPY viewtest_tblFROM stdin;
72-
510
73-
1015
74-
1520
75-
2025
73+
5101.1xy
74+
10152.2xyz
75+
15203.3xyzz
76+
20254.4xyzzy
7677
\.
7778

7879
CREATE OR REPLACEVIEWviewtestAS
@@ -84,7 +85,7 @@ CREATE OR REPLACE VIEW viewtest AS
8485
SELECT*FROM viewtest;
8586

8687
CREATE OR REPLACEVIEWviewtestAS
87-
SELECT a, bFROM viewtest_tblWHERE a>5ORDER BY bDESC;
88+
SELECT a, b, c, dFROM viewtest_tblWHERE a>5ORDER BY bDESC;
8889

8990
SELECT*FROM viewtest;
9091

@@ -98,11 +99,19 @@ CREATE OR REPLACE VIEW viewtest AS
9899

99100
-- should fail
100101
CREATE OR REPLACEVIEWviewtestAS
101-
SELECT a, b::numericFROM viewtest_tbl;
102+
SELECT a, b::numeric, c, dFROM viewtest_tbl;
103+
104+
-- should fail
105+
CREATE OR REPLACEVIEWviewtestAS
106+
SELECT a, b, c::numeric(10,2), dFROM viewtest_tbl;
107+
108+
-- should fail
109+
CREATE OR REPLACEVIEWviewtestAS
110+
SELECT a, b, c, d COLLATE"POSIX"FROM viewtest_tbl;
102111

103112
-- should work
104113
CREATE OR REPLACEVIEWviewtestAS
105-
SELECT a, b,0AScFROM viewtest_tbl;
114+
SELECT a, b,c, d,0ASeFROM viewtest_tbl;
106115

107116
DROPVIEW viewtest;
108117
DROPTABLE viewtest_tbl;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp