8000 Check collation when creating partitioned index · postgres/postgres@a11c9c4 · GitHub
[go: up one dir, main page]
Skip to content
{"payload":{"commit":{"oid":"a11c9c42ea3193ff7a977764788bda43ebb07b35","url":"/postgres/postgres/commit/a11c9c42ea3193ff7a977764788bda43ebb07b35","authoredDate":"2023-12-01T15:48:06.000+01:00","committedDate":"2023-12-01T16:05:41.000+01:00","shortMessage":null,"shortMessageMarkdown":"\u003cdiv\u003eCheck collation when creating partitioned index\u003c/div\u003e","shortMessageMarkdownLink":null,"bodyMessageHtml":"When creating a partitioned index, the partition key must be a subset\nof the index's columns. But this currently doesn't check that the\ncollations between the partition key and the index definition match.\nSo you can construct a unique index that fails to enforce uniqueness.\n(This would most likely involve a nondeterministic collation, so it\nwould have to be crafted explicitly and is not something that would\njust happen by accident.)\n\nThis patch adds the required collation check. As a result, any\npreviously allowed unique index that has a collation mismatch would no\nlonger be allowed to be created.\n\nReviewed-by: Tom Lane \u0026lt;tgl@sss.pgh.pa.us\u0026gt;\nDiscussion: \u003ca href=\"https://www.postgresql.org/message-id/flat/3327cb54-f7f1-413b-8fdb-7a9dceebb938%40eisentraut.org\" rel=\"nofollow\"\u003ehttps://www.postgresql.org/message-id/flat/3327cb54-f7f1-413b-8fdb-7a9dceebb938%40eisentraut.org\u003c/a\u003e","authors":[{"login":"petere","displayName":"Peter Eisentraut","avatarUrl":"https://avatars.githubusercontent.com/u/105543?v=4","path":"/petere","isGitHub":false}],"committerAttribution":false,"committer":{"login":"petere","displayName":"Peter Eisentraut","avatarUrl":"https://avatars.githubusercontent.com/u/105543?v=4","path":"/petere","isGitHub":false},"parents":["5b2dcead39904ae56e2c17cb5c7a14e6f255790c"],"globalRelayId":"C_kwDOAA4m0toAKGExMWM5YzQyZWEzMTkzZmY3YTk3Nzc2NDc4OGJkYTQzZWJiMDdiMzU","sha1":"5b2dcead39904ae56e2c17cb5c7a14e6f255790c","sha2":"a11c9c42ea3193ff7a977764788bda43ebb07b35"},"currentUser":null,"repo":{"id":927442,"defaultBranch":"master","name":"postgres","ownerLogin":"postgres","currentUserCanPush":false,"isFork":false,"isEmpty":false,"createdAt":"2010-09-21T11:35:45.000Z","ownerAvatar":"https://avatars.githubusercontent.com/u/177543?v=4","public":true,"private":false,"isOrgOwned":true},"diffEntryData":[{"diffLines":[{"stylingDirective":null,"type":"HUNK","blobLineNumber":1010,"text":"@@ -1011,10 +1011,13 @@ DefineIndex(Oid tableId,","html":"@@ -1011,10 +1011,13 @@ DefineIndex(Oid tableId,","displayNoNewLineWarning":false,"position":0,"left":1010,"right":1010},{"stylingDirective":null,"type":"CONTEXT","blobLineNumber":1011,"text":" \t\t\t{","html":" \t\t\t{","displayNoNewLineWarning":false,"position":1,"left":1011,"right":1011},{"stylingDirective":null,"type":"CONTEXT","blobLineNumber":1012,"text":" \t\t\t\tif (key-\u003epartattrs[i] == indexInfo-\u003eii_IndexAttrNumbers[j])","html":" \t\t\t\t\u003cspan class=pl-k\u003eif\u003c/span\u003e (\u003cspan class=pl-s1\u003ekey\u003c/span\u003e\u003cspan class=pl-c1\u003e-\u0026gt;\u003c/span\u003e\u003cspan class=pl-c1\u003epartattrs\u003c/span\u003e[\u003cspan class=pl-s1\u003ei\u003c/span\u003e] \u003cspan class=pl-c1\u003e==\u003c/span\u003e \u003cspan class=pl-s1\u003eindexInfo\u003c/span\u003e\u003cspan class=pl-c1\u003e-\u0026gt;\u003c/span\u003e\u003cspan class=pl-c1\u003eii_IndexAttrNumbers\u003c/span\u003e[\u003cspan class=pl-s1\u003ej\u003c/span\u003e])","displayNoNewLineWarning":false,"position":2,"left":1012,"right":1012},{"stylingDirective":null,"type":"CONTEXT","blobLineNumber":1013,"text":" \t\t\t\t{","html":" \t\t\t\t{","displayNoNewLineWarning":false,"position":3,"left":1013,"right":1013},{"stylingDirective":null,"type":"DELETION","blobLineNumber":1014,"text":"-\t\t\t\t\t/* Matched the column, now what about the equality op? */","html":"-\t\t\t\t\t\u003cspan class=\"pl-c\"\u003e/* Matched the column, now what about the equality op? */\u003c/span\u003e","displayNoNewLineWarning":false,"position":4,"left":1014,"right":1013},{"stylingDirective":null,"type":"ADDITION","blobLineNumber":1014,"text":"+\t\t\t\t\t/* Matched the column, now what about the collation and equality op? */","html":"+\t\t\t\t\t\u003cspan class=\"pl-c\"\u003e/* Matched the column, now what about the \u003cspan class=\"x x-first x-last\"\u003ecollation and \u003c/span\u003eequality op? */\u003c/span\u003e","displayNoNewLineWarning":false,"position":5,"left":1014,"right":1014},{"stylingDirective":null,"type":"CONTEXT","blobLineNumber":1015,"text":" \t\t\t\t\tOid\t\t\tidx_opfamily;","html":" \t\t\t\t\t\u003cspan class=pl-smi\u003eOid\u003c/span\u003e\t\t\t\u003cspan class=pl-s1\u003eidx_opfamily\u003c/span\u003e;","displayNoNewLineWarning":false,"position":6,"left":1015,"right":1015},{"stylingDirective":null,"type":"CONTEXT","blobLineNumber":1016,"text":" \t\t\t\t\tOid\t\t\tidx_opcintype;","html":" \t\t\t\t\t\u003cspan class=pl-smi\u003eOid\u003c/span\u003e\t\t\t\u003cspan class=pl-s1\u003eidx_opcintype\u003c/span\u003e;","displayNoNewLineWarning":false,"position":7,"left":1016,"right":1016},{"stylingDirective":null,"type":"CONTEXT","blobLineNumber":1017,"text":" ","html":"\u003cbr\u003e","displayNoNewLineWarning":false,"position":8,"left":1017,"right":1017},{"stylingDirective":null,"type":"ADDITION","blobLineNumber":1018,"text":"+\t\t\t\t\tif (key-\u003epartcollation[i] != collationIds[j])","html":"+\t\t\t\t\t\u003cspan class=pl-k\u003eif\u003c/span\u003e (\u003cspan class=pl-s1\u003ekey\u003c/span\u003e\u003cspan class=pl-c1\u003e-\u0026gt;\u003c/span\u003e\u003cspan class=pl-c1\u003epartcollation\u003c/span\u003e[\u003cspan class=pl-s1\u003ei\u003c/span\u003e] \u003cspan class=pl-c1\u003e!=\u003c/span\u003e \u003cspan class=pl-s1\u003ecollationIds\u003c/span\u003e[\u003cspan class=pl-s1\u003ej\u003c/span\u003e])","displayNoNewLineWarning":false,"position":9,"left":1017,"right":1018},{"stylingDirective":null,"type":"ADDITION","blobLineNumber":1019,"text":"+\t\t\t\t\t\tcontinue;","html":"+\t\t\t\t\t\t\u003cspan class=pl-k\u003econtinue\u003c/span\u003e;","displayNoNewLineWarning":false,"position":10,"left":1017,"right":1019},{"stylingDirective":null,"type":"ADDITION","blobLineNumber":1020,"text":"+","html":"+","displayNoNewLineWarning":false,"position":11,"left":1017,"right":1020},{"stylingDirective":null,"type":"CONTEXT","blobLineNumber":1021,"text":" \t\t\t\t\tif (get_opclass_opfamily_and_input_type(opclassIds[j],","html":" \t\t\t\t\t\u003cspan class=pl-k\u003eif\u003c/span\u003e (\u003cspan class=pl-en\u003eget_opclass_opfamily_and_input_type\u003c/span\u003e(\u003cspan class=pl-s1\u003eopclassIds\u003c/span\u003e[\u003cspan class=pl-s1\u003ej\u003c/span\u003e],","displayNoNewLineWarning":false,"position":12,"left":1018,"right":1021},{"stylingDirective":null,"type":"CONTEXT","blobLineNumber":1022,"text":" \t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\u0026idx_opfamily,","html":" \t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\u003cspan class=pl-c1\u003e\u0026amp;\u003c/span\u003e\u003cspan class=pl-s1\u003eidx_opfamily\u003c/span\u003e,","displayNoNewLineWarning":false,"position":13,"left":1019,"right":1022},{"stylingDirective":null,"type":"CONTEXT","blobLineNumber":1023,"text":" \t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\u0026idx_opcintype))","html":" \t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\u003cspan class=pl-c1\u003e\u0026amp;\u003c/span\u003e\u003cspan class=pl-s1\u003eidx_opcintype\u003c/span\u003e))","displayNoNewLineWarning":false,"position":14,"left":1020,"right":1023}],"diffNumber":0,"diffSize":"0 Bytes","isBinary":false,"isTooBig":false,"collapsed":false,"isSubmodule":false,"lineCount":4420,"linesChanged":5,"newTreeEntry":{"lineCount":4420,"path":"src/backend/commands/indexcmds.c","mode":100644,"isGenerated":false},"oldTreeEntry":{"lineCount":0,"path":"src/backend/commands/indexcmds.c","mode":100644},"linesAdded":4,"linesDeleted":1,"path":"src/backend/commands/indexcmds.c","pathDigest":"111f77b321d894beea2b6a25406b3d540e5e2441c3f13841b60c0bb5bb961063","status":"MODIFIED","truncatedReason":null,"oldOid":"5b2dcead39904ae56e2c17cb5c7a14e6f255790c","newOid":"a11c9c42ea3193ff7a977764788bda43ebb07b35","copilotChatReference":null,"deletedSha":"5b2dcead39904ae56e2c17cb5c7a14e6f255790c","canToggleRichDiff":false,"defaultToRichDiff":false,"proseDifffHtml":null,"renderInfo":null,"dependencyDiffPath":null,"submodule":null}],"splitViewPreference":"unified","ignoreWhitespace":false,"repoOwnerGlobalRelayId":"MDEyOk9yZ2FuaXphdGlvbjE3NzU0Mw==","commentsPreference":"visible","diffLineSpacingPreference":"relaxed","useMonospaceFont":false,"pasteUrlLinkAsPlainText":false,"userNotices":[],"path":"/postgres/postgres/commit/a11c9c42ea3193ff7a977764788bda43ebb07b35","fileTreeExpanded":true,"headerInfo":{"additions":4,"deletions":1,"filesChanged":1,"filesChangedString":"1"},"moreDiffsToLoad":false,"asyncDiffLoadInfo":{"startIndex":1,"truncated":false,"byteCount":519,"lineShownCount":15},"commentInfo":{"canComment":false,"locked":false,"canLock":false,"repoArchived":false},"csrf_tokens":{"/users/diffview?diff=split":{"post":"CplRCJgCUYX2G-RkNf-iee_Jk9gg5tP_zY-PZwnlOI0_-4VxknIcs301xaj12meVgpvxVXsniRlk6BV71kGxSw"},"/users/diffview?diff=unified":{"post":"98VY1rJN0E5cPgezZ46cWUdyPXdsg0BBcT0PYLh0rO_Cp4yvuD2deNcQJn-nq1m1KiBf-jdCGqfYWpV8Z9AlKQ"},"/notifications/thread":{"post":"K_FbVtIhrciC-tKivTLttvfI7SNZ2lHJq7UQm9ToaScv1tOEgOoo-BGjhoEFCuMSn_d6DXvzifgXjzhoFpSeWg"}}},"title":"Check collation when creating partitioned index · postgres/postgres@a11c9c4","appPayload":{"helpUrl":"https://docs.github.com","findInDiffWorkerPath":"/assets-cdn/worker/find-in-diff-worker-2bfe39677d14.js","enabled_features":{"diff_ux_refresh_beta":false,"diff_inline_comments":true,"diff_ux_refresh_ssr_five":false,"diff_ux_refresh_ssr_ten":false,"react_diff_line_type_character_correction":true}}}

Commit a11c9c4

Browse files
peterepetere
committed
Check collation when creating partitioned index
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
1 parent 5b2dcea commit a11c9c4

File tree

1 file changed

+4
-1
lines changed

1 file changed

+4
-1
lines changed

‎src/backend/commands/indexcmds.c

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1011,10 +1011,13 @@ DefineIndex(Oid tableId,
10111011
{
10121012
if (key->partattrs[i] == indexInfo->ii_IndexAttrNumbers[j])
10131013
{
1014-
/* Matched the column, now what about the equality op? */
1014+
/* Matched the column, now what about the collation and equality op? */
10151015
Oid idx_opfamily;
10161016
Oid idx_opcintype;
10171017

1018+
if (key->partcollation[i] != collationIds[j])
1019+
continue;
1020+
10181021
if (get_opclass_opfamily_and_input_type(opclassIds[j],
10191022
&idx_opfamily,
10201023
&idx_opcintype))

0 commit comments

Comments
 (0)
0