From 1f0621b085638ed905bfa1419d20b4154d07b9cc Mon Sep 17 00:00:00 2001 From: おさむのひと <46447427+samunohito@users.noreply.github.com> Date: Sun, 13 Apr 2025 20:44:44 +0900 Subject: レプリケーション設定時におけるinsertOne()の挙動を調整 (#15109) MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit * returningを含むクエリをmasterで動かす * wip * wip * fix CHANGELOG.md * 調整 * fix * fix import --- packages/backend/src/models/_.ts | 83 ++++++++++++++++++++++++++++------------ 1 file changed, 58 insertions(+), 25 deletions(-) (limited to 'packages/backend/src/models') diff --git a/packages/backend/src/models/_.ts b/packages/backend/src/models/_.ts index e852b302f3..e1ea2a2604 100644 --- a/packages/backend/src/models/_.ts +++ b/packages/backend/src/models/_.ts @@ -3,29 +3,48 @@ * SPDX-License-Identifier: AGPL-3.0-only */ -import { FindOneOptions, InsertQueryBuilder, ObjectLiteral, Repository, SelectQueryBuilder } from 'typeorm'; +import { + FindOneOptions, + InsertQueryBuilder, + ObjectLiteral, + QueryRunner, + Repository, + SelectQueryBuilder, +} from 'typeorm'; +import { PostgresConnectionOptions } from 'typeorm/driver/postgres/PostgresConnectionOptions.js'; import { RelationCountLoader } from 'typeorm/query-builder/relation-count/RelationCountLoader.js'; import { RelationIdLoader } from 'typeorm/query-builder/relation-id/RelationIdLoader.js'; -import { RawSqlResultsToEntityTransformer } from 'typeorm/query-builder/transformer/RawSqlResultsToEntityTransformer.js'; -import { MiAbuseUserReport } from '@/models/AbuseUserReport.js'; +import { + RawSqlResultsToEntityTransformer, +} from 'typeorm/query-builder/transformer/RawSqlResultsToEntityTransformer.js'; import { MiAbuseReportNotificationRecipient } from '@/models/AbuseReportNotificationRecipient.js'; +import { MiAbuseUserReport } from '@/models/AbuseUserReport.js'; import { MiAccessToken } from '@/models/AccessToken.js'; import { MiAd } from '@/models/Ad.js'; import { MiAnnouncement } from '@/models/Announcement.js'; import { MiAnnouncementRead } from '@/models/AnnouncementRead.js'; import { MiAntenna } from '@/models/Antenna.js'; import { MiApp } from '@/models/App.js'; -import { MiAvatarDecoration } from '@/models/AvatarDecoration.js'; import { MiAuthSession } from '@/models/AuthSession.js'; +import { MiAvatarDecoration } from '@/models/AvatarDecoration.js'; import { MiBlocking } from '@/models/Blocking.js'; -import { MiChannelFollowing } from '@/models/ChannelFollowing.js'; +import { MiBubbleGameRecord } from '@/models/BubbleGameRecord.js'; +import { MiChannel } from '@/models/Channel.js'; import { MiChannelFavorite } from '@/models/ChannelFavorite.js'; +import { MiChannelFollowing } from '@/models/ChannelFollowing.js'; +import { MiChatApproval } from '@/models/ChatApproval.js'; +import { MiChatMessage } from '@/models/ChatMessage.js'; +import { MiChatRoom } from '@/models/ChatRoom.js'; +import { MiChatRoomInvitation } from '@/models/ChatRoomInvitation.js'; +import { MiChatRoomMembership } from '@/models/ChatRoomMembership.js'; import { MiClip } from '@/models/Clip.js'; -import { MiClipNote } from '@/models/ClipNote.js'; import { MiClipFavorite } from '@/models/ClipFavorite.js'; +import { MiClipNote } from '@/models/ClipNote.js'; import { MiDriveFile } from '@/models/DriveFile.js'; import { MiDriveFolder } from '@/models/DriveFolder.js'; import { MiEmoji } from '@/models/Emoji.js'; +import { MiFlash } from '@/models/Flash.js'; +import { MiFlashLike } from '@/models/FlashLike.js'; import { MiFollowing } from '@/models/Following.js'; import { MiFollowRequest } from '@/models/FollowRequest.js'; import { MiGalleryLike } from '@/models/GalleryLike.js'; @@ -35,7 +54,6 @@ import { MiInstance } from '@/models/Instance.js'; import { MiMeta } from '@/models/Meta.js'; import { MiModerationLog } from '@/models/ModerationLog.js'; import { MiMuting } from '@/models/Muting.js'; -import { MiRenoteMuting } from '@/models/RenoteMuting.js'; import { MiNote } from '@/models/Note.js'; import { MiNoteFavorite } from '@/models/NoteFavorite.js'; import { MiNoteReaction } from '@/models/NoteReaction.js'; @@ -50,42 +68,38 @@ import { MiPromoRead } from '@/models/PromoRead.js'; import { MiRegistrationTicket } from '@/models/RegistrationTicket.js'; import { MiRegistryItem } from '@/models/RegistryItem.js'; import { MiRelay } from '@/models/Relay.js'; +import { MiRenoteMuting } from '@/models/RenoteMuting.js'; +import { MiRetentionAggregation } from '@/models/RetentionAggregation.js'; +import { MiReversiGame } from '@/models/ReversiGame.js'; +import { MiRole } from '@/models/Role.js'; +import { MiRoleAssignment } from '@/models/RoleAssignment.js'; import { MiSignin } from '@/models/Signin.js'; import { MiSwSubscription } from '@/models/SwSubscription.js'; import { MiSystemAccount } from '@/models/SystemAccount.js'; +import { MiSystemWebhook } from '@/models/SystemWebhook.js'; import { MiUsedUsername } from '@/models/UsedUsername.js'; import { MiUser } from '@/models/User.js'; import { MiUserIp } from '@/models/UserIp.js'; import { MiUserKeypair } from '@/models/UserKeypair.js'; import { MiUserList } from '@/models/UserList.js'; +import { MiUserListFavorite } from '@/models/UserListFavorite.js'; import { MiUserListMembership } from '@/models/UserListMembership.js'; +import { MiUserMemo } from '@/models/UserMemo.js'; import { MiUserNotePining } from '@/models/UserNotePining.js'; import { MiUserPending } from '@/models/UserPending.js'; import { MiUserProfile } from '@/models/UserProfile.js'; import { MiUserPublickey } from '@/models/UserPublickey.js'; import { MiUserSecurityKey } from '@/models/UserSecurityKey.js'; -import { MiUserMemo } from '@/models/UserMemo.js'; import { MiWebhook } from '@/models/Webhook.js'; -import { MiSystemWebhook } from '@/models/SystemWebhook.js'; -import { MiChannel } from '@/models/Channel.js'; -import { MiRetentionAggregation } from '@/models/RetentionAggregation.js'; -import { MiRole } from '@/models/Role.js'; -import { MiRoleAssignment } from '@/models/RoleAssignment.js'; -import { MiFlash } from '@/models/Flash.js'; -import { MiFlashLike } from '@/models/FlashLike.js'; -import { MiUserListFavorite } from '@/models/UserListFavorite.js'; -import { MiChatMessage } from '@/models/ChatMessage.js'; -import { MiChatRoom } from '@/models/ChatRoom.js'; -import { MiChatRoomMembership } from '@/models/ChatRoomMembership.js'; -import { MiChatRoomInvitation } from '@/models/ChatRoomInvitation.js'; -import { MiChatApproval } from '@/models/ChatApproval.js'; -import { MiBubbleGameRecord } from '@/models/BubbleGameRecord.js'; -import { MiReversiGame } from '@/models/ReversiGame.js'; import type { QueryDeepPartialEntity } from 'typeorm/query-builder/QueryPartialEntity.js'; export interface MiRepository { createTableColumnNames(this: Repository & MiRepository): string[]; + insertOne(this: Repository & MiRepository, entity: QueryDeepPartialEntity, findOptions?: Pick, 'relations'>): Promise; + + insertOneImpl(this: Repository & MiRepository, entity: QueryDeepPartialEntity, findOptions?: Pick, 'relations'>, queryRunner?: QueryRunner): Promise; + selectAliasColumnNames(this: Repository & MiRepository, queryBuilder: InsertQueryBuilder, builder: SelectQueryBuilder): void; } @@ -94,6 +108,21 @@ export const miRepository = { return this.metadata.columns.filter(column => column.isSelect && !column.isVirtual).map(column => column.databaseName); }, async insertOne(entity, findOptions?) { + const opt = this.manager.connection.options as PostgresConnectionOptions; + if (opt.replication) { + const queryRunner = this.manager.connection.createQueryRunner('master'); + try { + return this.insertOneImpl(entity, findOptions, queryRunner); + } finally { + await queryRunner.release(); + } + } else { + return this.insertOneImpl(entity, findOptions); + } + }, + async insertOneImpl(entity, findOptions?, queryRunner?) { + // ---- insert + returningの結果を共通テーブル式(CTE)に保持するクエリを生成 ---- + const queryBuilder = this.createQueryBuilder().insert().values(entity); // eslint-disable-next-line @typescript-eslint/no-non-null-assertion const mainAlias = queryBuilder.expressionMap.mainAlias!; @@ -101,7 +130,9 @@ export const miRepository = { mainAlias.name = 't'; const columnNames = this.createTableColumnNames(); queryBuilder.returning(columnNames.reduce((a, c) => `${a}, ${queryBuilder.escape(c)}`, '').slice(2)); - const builder = this.createQueryBuilder().addCommonTableExpression(queryBuilder, 'cte', { columnNames }); + + // ---- 共通テーブル式(CTE)から結果を取得 ---- + const builder = this.createQueryBuilder(undefined, queryRunner).addCommonTableExpression(queryBuilder, 'cte', { columnNames }); // eslint-disable-next-line @typescript-eslint/no-non-null-assertion builder.expressionMap.mainAlias!.tablePath = 'cte'; this.selectAliasColumnNames(queryBuilder, builder); @@ -204,7 +235,9 @@ export { }; export type AbuseUserReportsRepository = Repository & MiRepository; -export type AbuseReportNotificationRecipientRepository = Repository & MiRepository; +export type AbuseReportNotificationRecipientRepository = + Repository + & MiRepository; export type AccessTokensRepository = Repository & MiRepository; export type AdsRepository = Repository & MiRepository; export type AnnouncementsRepository = Repository & MiRepository; -- cgit v1.2.3-freya From d5fe6e36aecd2551fb2f3b80af4d1c42e0010fc7 Mon Sep 17 00:00:00 2001 From: anatawa12 Date: Tue, 15 Apr 2025 16:10:17 +0900 Subject: fix: avatarId が null のときにも avatarUrl が non null 担ってることがある問題 (#15833) MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- packages/backend/src/core/WebhookTestService.ts | 8 ++++---- packages/backend/src/core/entities/UserEntityService.ts | 8 ++++---- packages/backend/src/models/User.ts | 4 ++++ packages/backend/src/server/ServerService.ts | 2 +- packages/backend/src/server/web/ClientServerService.ts | 2 +- packages/backend/src/server/web/FeedService.ts | 2 +- 6 files changed, 15 insertions(+), 11 deletions(-) (limited to 'packages/backend/src/models') diff --git a/packages/backend/src/core/WebhookTestService.ts b/packages/backend/src/core/WebhookTestService.ts index 222153fd2a..9cf985b688 100644 --- a/packages/backend/src/core/WebhookTestService.ts +++ b/packages/backend/src/core/WebhookTestService.ts @@ -411,8 +411,8 @@ export class WebhookTestService { name: user.name, username: user.username, host: user.host, - avatarUrl: user.avatarUrl, - avatarBlurhash: user.avatarBlurhash, + avatarUrl: user.avatarId == null ? null : user.avatarUrl, + avatarBlurhash: user.avatarId == null ? null : user.avatarBlurhash, avatarDecorations: user.avatarDecorations.map(it => ({ id: it.id, angle: it.angle, @@ -441,8 +441,8 @@ export class WebhookTestService { createdAt: new Date().toISOString(), updatedAt: user.updatedAt?.toISOString() ?? null, lastFetchedAt: user.lastFetchedAt?.toISOString() ?? null, - bannerUrl: user.bannerUrl, - bannerBlurhash: user.bannerBlurhash, + bannerUrl: user.bannerId == null ? null : user.bannerUrl, + bannerBlurhash: user.bannerId == null ? null : user.bannerBlurhash, isLocked: user.isLocked, isSilenced: false, isSuspended: user.isSuspended, diff --git a/packages/backend/src/core/entities/UserEntityService.ts b/packages/backend/src/core/entities/UserEntityService.ts index e252ff509e..d4769d24d4 100644 --- a/packages/backend/src/core/entities/UserEntityService.ts +++ b/packages/backend/src/core/entities/UserEntityService.ts @@ -486,8 +486,8 @@ export class UserEntityService implements OnModuleInit { name: user.name, username: user.username, host: user.host, - avatarUrl: user.avatarUrl ?? this.getIdenticonUrl(user), - avatarBlurhash: user.avatarBlurhash, + avatarUrl: (user.avatarId == null ? null : user.avatarUrl) ?? this.getIdenticonUrl(user), + avatarBlurhash: (user.avatarId == null ? null : user.avatarBlurhash), avatarDecorations: user.avatarDecorations.length > 0 ? this.avatarDecorationService.getAll().then(decorations => user.avatarDecorations.filter(ud => decorations.some(d => d.id === ud.id)).map(ud => ({ id: ud.id, angle: ud.angle || undefined, @@ -533,8 +533,8 @@ export class UserEntityService implements OnModuleInit { createdAt: this.idService.parse(user.id).date.toISOString(), updatedAt: user.updatedAt ? user.updatedAt.toISOString() : null, lastFetchedAt: user.lastFetchedAt ? user.lastFetchedAt.toISOString() : null, - bannerUrl: user.bannerUrl, - bannerBlurhash: user.bannerBlurhash, + bannerUrl: user.bannerId == null ? null : user.bannerUrl, + bannerBlurhash: user.bannerId == null ? null : user.bannerBlurhash, isLocked: user.isLocked, isSilenced: this.roleService.getUserPolicies(user.id).then(r => !r.canPublicNote), isSuspended: user.isSuspended, diff --git a/packages/backend/src/models/User.ts b/packages/backend/src/models/User.ts index bc652cea62..baf4eefdf1 100644 --- a/packages/backend/src/models/User.ts +++ b/packages/backend/src/models/User.ts @@ -118,21 +118,25 @@ export class MiUser { @JoinColumn() public banner: MiDriveFile | null; + // avatarId が null になったとしてもこれが null でない可能性があるため、このフィールドを使うときは avatarId の non-null チェックをすること @Column('varchar', { length: 512, nullable: true, }) public avatarUrl: string | null; + // bannerId が null になったとしてもこれが null でない可能性があるため、このフィールドを使うときは bannerId の non-null チェックをすること @Column('varchar', { length: 512, nullable: true, }) public bannerUrl: string | null; + // avatarId が null になったとしてもこれが null でない可能性があるため、このフィールドを使うときは avatarId の non-null チェックをすること @Column('varchar', { length: 128, nullable: true, }) public avatarBlurhash: string | null; + // bannerId が null になったとしてもこれが null でない可能性があるため、このフィールドを使うときは bannerId の non-null チェックをすること @Column('varchar', { length: 128, nullable: true, }) diff --git a/packages/backend/src/server/ServerService.ts b/packages/backend/src/server/ServerService.ts index b899053287..355d7ca08e 100644 --- a/packages/backend/src/server/ServerService.ts +++ b/packages/backend/src/server/ServerService.ts @@ -221,7 +221,7 @@ export class ServerService implements OnApplicationShutdown { reply.header('Cache-Control', 'public, max-age=86400'); if (user) { - reply.redirect(user.avatarUrl ?? this.userEntityService.getIdenticonUrl(user)); + reply.redirect((user.avatarId == null ? null : user.avatarUrl) ?? this.userEntityService.getIdenticonUrl(user)); } else { reply.redirect('/static-assets/user-unknown.png'); } diff --git a/packages/backend/src/server/web/ClientServerService.ts b/packages/backend/src/server/web/ClientServerService.ts index 927970e2e2..30a911088e 100644 --- a/packages/backend/src/server/web/ClientServerService.ts +++ b/packages/backend/src/server/web/ClientServerService.ts @@ -534,7 +534,7 @@ export class ClientServerService { return await reply.view('user', { user, profile, me, - avatarUrl: user.avatarUrl ?? this.userEntityService.getIdenticonUrl(user), + avatarUrl: _user.avatarUrl, sub: request.params.sub, ...await this.generateCommonPugData(this.meta), clientCtx: htmlSafeJsonStringify({ diff --git a/packages/backend/src/server/web/FeedService.ts b/packages/backend/src/server/web/FeedService.ts index 9d810ddc84..eae7645321 100644 --- a/packages/backend/src/server/web/FeedService.ts +++ b/packages/backend/src/server/web/FeedService.ts @@ -65,7 +65,7 @@ export class FeedService { generator: 'Misskey', description: `${user.notesCount} Notes, ${profile.followingVisibility === 'public' ? user.followingCount : '?'} Following, ${profile.followersVisibility === 'public' ? user.followersCount : '?'} Followers${profile.description ? ` · ${profile.description}` : ''}`, link: author.link, - image: user.avatarUrl ?? this.userEntityService.getIdenticonUrl(user), + image: (user.avatarId == null ? null : user.avatarUrl) ?? this.userEntityService.getIdenticonUrl(user), feedLinks: { json: `${author.link}.json`, atom: `${author.link}.atom`, -- cgit v1.2.3-freya From 7a41cfe28bf28daa32878ff3c77790242df1e0a3 Mon Sep 17 00:00:00 2001 From: "饺子w (Yumechi)" <35571479+eternal-flame-AD@users.noreply.github.com> Date: Wed, 23 Apr 2025 05:29:42 +0000 Subject: enhance(backend): DB note (userId) インデクス -> (userId, id) 複合インデクスにする (#15879) MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit * enhance(backend): use composite index for ordering notes by user Signed-off-by: eternal-flame-AD * fixup! enhance(backend): use composite index for ordering notes by user --------- Signed-off-by: eternal-flame-AD --- CHANGELOG.md | 1 + .../migration/1745378064470-composite-note-index.js | 21 +++++++++++++++++++++ packages/backend/src/models/Note.ts | 2 +- 3 files changed, 23 insertions(+), 1 deletion(-) create mode 100644 packages/backend/migration/1745378064470-composite-note-index.js (limited to 'packages/backend/src/models') diff --git a/CHANGELOG.md b/CHANGELOG.md index 0524d3c5bb..d36cb8dd2b 100644 --- a/CHANGELOG.md +++ b/CHANGELOG.md @@ -22,6 +22,7 @@ - Enhance: ジョブキューの成功/失敗したジョブも一定数・一定期間保存するようにし、後から問題を調査することを容易に - Enhance: フォローしているユーザーならフォロワー限定投稿のノートでもアンテナで検知できるように (Cherry-picked from https://github.com/yojo-art/cherrypick/pull/568 and https://github.com/team-shahu/misskey/pull/38) +- Enhance: ユーザーごとにノートの表示が高速化するように - Fix: システムアカウントの名前がサーバー名と同期されない問題を修正 - Fix: 大文字を含むユーザの URL で紹介された場合に 404 エラーを返す問題 #15813 - Fix: リードレプリカ設定時にレコードの追加・更新・削除を伴うクエリを発行した際はmasterノードで実行されるように調整( #10897 ) diff --git a/packages/backend/migration/1745378064470-composite-note-index.js b/packages/backend/migration/1745378064470-composite-note-index.js new file mode 100644 index 0000000000..49e835d38c --- /dev/null +++ b/packages/backend/migration/1745378064470-composite-note-index.js @@ -0,0 +1,21 @@ +/* + * SPDX-FileCopyrightText: syuilo and misskey-project + * SPDX-License-Identifier: AGPL-3.0-only + */ + +export class CompositeNoteIndex1745378064470 { + name = 'CompositeNoteIndex1745378064470'; + + async up(queryRunner) { + await queryRunner.query(`CREATE INDEX "IDX_724b311e6f883751f261ebe378" ON "note" ("userId", "id" DESC)`); + await queryRunner.query(`DROP INDEX IF EXISTS "IDX_5b87d9d19127bd5d92026017a7"`); + // Flush all cached Linear Scan Plans and redo statistics for composite index + // this is important for Postgres to learn that even in highly complex queries, using this index first can reduce the result set significantly + await queryRunner.query(`ANALYZE "user", "note"`); + } + + async down(queryRunner) { + await queryRunner.query(`DROP INDEX IF EXISTS "IDX_724b311e6f883751f261ebe378"`); + await queryRunner.query(`CREATE INDEX "IDX_5b87d9d19127bd5d92026017a7" ON "note" ("userId")`); + } +} diff --git a/packages/backend/src/models/Note.ts b/packages/backend/src/models/Note.ts index 9a95c6faab..c5ca2b5776 100644 --- a/packages/backend/src/models/Note.ts +++ b/packages/backend/src/models/Note.ts @@ -10,6 +10,7 @@ import { MiUser } from './User.js'; import { MiChannel } from './Channel.js'; import type { MiDriveFile } from './DriveFile.js'; +@Index(['userId', 'id']) @Entity('note') export class MiNote { @PrimaryColumn(id()) @@ -65,7 +66,6 @@ export class MiNote { }) public cw: string | null; - @Index() @Column({ ...id(), comment: 'The ID of author.', -- cgit v1.2.3-freya