Check collation when creating partitioned index

petere · petere · commit a11c9c42ea31 · 2023-12-01T16:05:41.000+01:00
When creating a partitioned index, the partition key must be a subset of the index's columns. But this currently doesn't check that the collations between the partition key and the index definition match. So you can construct a unique index that fails to enforce uniqueness. (This would most likely involve a nondeterministic collation, so it would have to be crafted explicitly and is not something that would just happen by accident.) This patch adds the required collation check. As a result, any previously allowed unique index that has a collation mismatch would no longer be allowed to be created. Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us> Discussion: https://www.postgresql.org/message-id/flat/3327cb54-f7f1-413b-8fdb-7a9dceebb938%40eisentraut.org
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
@@ -1011,10 +1011,13 @@ DefineIndex(Oid tableId,
 			{
 				if (key->partattrs[i] == indexInfo->ii_IndexAttrNumbers[j])
 				{
-					/* Matched the column, now what about the equality op? */
+					/* Matched the column, now what about the collation and equality op? */
 					Oid			idx_opfamily;
 					Oid			idx_opcintype;
 
+					if (key->partcollation[i] != collationIds[j])
+						continue;
+
 					if (get_opclass_opfamily_and_input_type(opclassIds[j],
 															&idx_opfamily,
 															&idx_opcintype))

Original file line number	Diff line number	Diff line change
`@@ -1011,10 +1011,13 @@ DefineIndex(Oid tableId,`
`1011`	`1011`	`{`
`1012`	`1012`	`if (key->partattrs[i] == indexInfo->ii_IndexAttrNumbers[j])`
`1013`	`1013`	`{`
`1014`		`- /* Matched the column, now what about the equality op? */`
	`1014`	`+ /* Matched the column, now what about the collation and equality op? */`
`1015`	`1015`	`Oid idx_opfamily;`
`1016`	`1016`	`Oid idx_opcintype;`
`1017`	`1017`
	`1018`	`+ if (key->partcollation[i] != collationIds[j])`
	`1019`	`+ continue;`
	`1020`	`+`
`1018`	`1021`	`if (get_opclass_opfamily_and_input_type(opclassIds[j],`
`1019`	`1022`	`&idx_opfamily,`
`1020`	`1023`	`&idx_opcintype))`