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

Commitb9cff97

Browse files
committed
Don't allow CREATE TABLE AS to create a column with invalid collation
It is possible that an expression ends up with a collatable type butwithout a collation. CREATE TABLE AS could then create a table basedon that. But such a column cannot be dumped with valid SQL syntax, sowe disallow creating such a column.per test report from Noah Misch
1 parent8d3b421 commitb9cff97

File tree

6 files changed

+28
-8
lines changed

6 files changed

+28
-8
lines changed

‎src/backend/catalog/heap.c

Lines changed: 15 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -429,6 +429,7 @@ CheckAttributeNamesTypes(TupleDesc tupdesc, char relkind,
429429
{
430430
CheckAttributeType(NameStr(tupdesc->attrs[i]->attname),
431431
tupdesc->attrs[i]->atttypid,
432+
tupdesc->attrs[i]->attcollation,
432433
allow_system_table_mods);
433434
}
434435
}
@@ -442,7 +443,7 @@ CheckAttributeNamesTypes(TupleDesc tupdesc, char relkind,
442443
* --------------------------------
443444
*/
444445
void
445-
CheckAttributeType(constchar*attname,Oidatttypid,
446+
CheckAttributeType(constchar*attname,Oidatttypid,Oidattcollation,
446447
boolallow_system_table_mods)
447448
{
448449
charatt_typtype=get_typtype(atttypid);
@@ -493,12 +494,24 @@ CheckAttributeType(const char *attname, Oid atttypid,
493494

494495
if (attr->attisdropped)
495496
continue;
496-
CheckAttributeType(NameStr(attr->attname),attr->atttypid,
497+
CheckAttributeType(NameStr(attr->attname),attr->atttypid,attr->attcollation,
497498
allow_system_table_mods);
498499
}
499500

500501
relation_close(relation,AccessShareLock);
501502
}
503+
504+
/*
505+
* This might not be strictly invalid per SQL standard, but it is
506+
* pretty useless, and it cannot be dumped, so we must disallow
507+
* it.
508+
*/
509+
if (type_is_collatable(atttypid)&& !OidIsValid(attcollation))
510+
ereport(ERROR,
511+
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
512+
errmsg("no collation was derived for column \"%s\" with collatable type %s",
513+
attname,format_type_be(atttypid)),
514+
errhint("Use the COLLATE clause to set the collation explicitly.")));
502515
}
503516

504517
/*

‎src/backend/catalog/index.c

Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -352,6 +352,8 @@ ConstructTupleDescriptor(Relation heapRelation,
352352
to->atthasdef= false;
353353
to->attislocal= true;
354354
to->attinhcount=0;
355+
356+
to->attcollation=collationObjectId[i];
355357
}
356358
else
357359
{
@@ -388,6 +390,8 @@ ConstructTupleDescriptor(Relation heapRelation,
388390
to->atttypmod=-1;
389391
to->attislocal= true;
390392

393+
to->attcollation=collationObjectId[i];
394+
391395
ReleaseSysCache(tuple);
392396

393397
/*
@@ -399,11 +403,9 @@ ConstructTupleDescriptor(Relation heapRelation,
399403
* whether a table column is of a safe type (which is why we
400404
* needn't check for the non-expression case).
401405
*/
402-
CheckAttributeType(NameStr(to->attname),to->atttypid, false);
406+
CheckAttributeType(NameStr(to->attname),to->atttypid,to->attcollation,false);
403407
}
404408

405-
to->attcollation=collationObjectId[i];
406-
407409
/*
408410
* We do not yet have the correct relation OID for the index, so just
409411
* set it invalid for now.InitializeAttributeOids() will fix it

‎src/backend/commands/tablecmds.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -4206,7 +4206,7 @@ ATExecAddColumn(AlteredTableInfo *tab, Relation rel,
42064206
typeOid=HeapTupleGetOid(typeTuple);
42074207

42084208
/* make sure datatype is legal for a column */
4209-
CheckAttributeType(colDef->colname,typeOid, false);
4209+
CheckAttributeType(colDef->colname,typeOid,collOid,false);
42104210

42114211
/* construct new attribute's pg_attribute entry */
42124212
attribute.attrelid=myrelid;
@@ -6515,7 +6515,7 @@ ATPrepAlterColumnType(List **wqueue,
65156515
typenameTypeIdModColl(NULL,typeName,&targettype,&targettypmod,&targetcollid);
65166516

65176517
/* make sure datatype is legal for a column */
6518-
CheckAttributeType(colName,targettype, false);
6518+
CheckAttributeType(colName,targettype,targetcollid,false);
65196519

65206520
if (tab->relkind==RELKIND_RELATION)
65216521
{

‎src/include/catalog/heap.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -117,7 +117,7 @@ extern Form_pg_attribute SystemAttributeByName(const char *attname,
117117
externvoidCheckAttributeNamesTypes(TupleDesctupdesc,charrelkind,
118118
boolallow_system_table_mods);
119119

120-
externvoidCheckAttributeType(constchar*attname,Oidatttypid,
120+
externvoidCheckAttributeType(constchar*attname,Oidatttypid,Oidattcollation,
121121
boolallow_system_table_mods);
122122

123123
#endif/* HEAP_H */

‎src/test/regress/expected/collate.linux.utf8.out

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -627,6 +627,9 @@ ERROR: collation mismatch between implicit collations "en_US.utf8" and "C"
627627
LINE 1: SELECT a, b FROM collate_test1 EXCEPT SELECT a, b FROM colla...
628628
^
629629
HINT: You can override the collation by applying the COLLATE clause to one or both expressions.
630+
CREATE TABLE test_u AS SELECT a, b FROM collate_test1 UNION ALL SELECT a, b FROM collate_test3; -- fail
631+
ERROR: no collation was derived for column "b" with collatable type text
632+
HINT: Use the COLLATE clause to set the collation explicitly.
630633
-- casting
631634
SELECT CAST('42' AS text COLLATE "C");
632635
ERROR: COLLATE clause not allowed in cast target

‎src/test/regress/sql/collate.linux.utf8.sql

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -188,6 +188,8 @@ SELECT a, b COLLATE "C" FROM collate_test1 UNION SELECT a, b FROM collate_test3
188188
SELECT a, bFROM collate_test1 INTERSECTSELECT a, bFROM collate_test3ORDER BY2;-- fail
189189
SELECT a, bFROM collate_test1 EXCEPTSELECT a, bFROM collate_test3ORDER BY2;-- fail
190190

191+
CREATETABLEtest_uASSELECT a, bFROM collate_test1UNION ALLSELECT a, bFROM collate_test3;-- fail
192+
191193

192194
-- casting
193195

0 commit comments

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