Skip to content

Commit 2523928

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

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 AS c FROM viewtest_tbl;
103+
SELECT a, b, c, d, 0 AS e 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-
CREATE TABLE viewtest_tbl (a int, b int);
70+
CREATE TABLE viewtest_tbl (a int, b int, c numeric(10,1), d text COLLATE "C");
71+
7172
COPY viewtest_tbl FROM stdin;
72-
5 10
73-
10 15
74-
15 20
75-
20 25
73+
5 10 1.1 xy
74+
10 15 2.2 xyz
75+
15 20 3.3 xyzz
76+
20 25 4.4 xyzzy
7677
\.
7778

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

8687
CREATE OR REPLACE VIEW viewtest AS
87-
SELECT a, b FROM viewtest_tbl WHERE a > 5 ORDER BY b DESC;
88+
SELECT a, b, c, d FROM viewtest_tbl WHERE a > 5 ORDER BY b DESC;
8889

8990
SELECT * FROM viewtest;
9091

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

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

103112
-- should work
104113
CREATE OR REPLACE VIEW viewtest AS
105-
SELECT a, b, 0 AS c FROM viewtest_tbl;
114+
SELECT a, b, c, d, 0 AS e FROM viewtest_tbl;
106115

107116
DROP VIEW viewtest;
108117
DROP TABLE viewtest_tbl;

0 commit comments

Comments
 (0)