immich-app/immich
1
0
Fork 0
mirror of https://github.com/immich-app/immich.git synced 2026-07-25 14:00:45 +03:00
Code Issues 783 Packages Projects Releases 22 Wiki Activity

[PR #6015] [MERGED] fix(server): Reduce number of bound parameters in Access queries #11046

New Issue
Closed
opened 2026-02-05 14:32:37 +03:00 by OVERLORD · 0 comments
OVERLORD commented 2026-02-05 14:32:37 +03:00
Owner
Copy Link

📋 Pull Request Information

Original PR: https://github.com/immich-app/immich/pull/6015
Author: @adamantike
Created: 12/27/2023
Status: ✅ Merged
Merged: 12/28/2023
Merged by: @alextran1502

Base: main ← Head: fix/server/reduce-bind-parameters-in-access-queries

📝 Commits (2)

  • bac22ba fix(server): Reduce number of bound parameters in Access queries
  • 351ba0e fix: Use array overlapping instead of CTEs

📊 Changes

1 file changed (+19 additions, -32 deletions)

View changed files

📝 server/src/infra/repositories/access.repository.ts (+19 -32)

📄 Description

According to https://github.com/typeorm/typeorm/issues/7565, the introduction of bulk queries for permission checks could quickly reach the limit of 65536 bound parameters allowed by the PostgreSQL connection.

To avoid reaching that limit, this first change refactors the Access queries that are expanding the set of ids multiple times. For example, asset.checkSharedLinkAccess expands the ids 4 times, so providing just ~16400 ids is enough to break the query.

Refactored queries:

  • activity.checkCreateAccess
-- Before
SELECT "AlbumEntity"."id" AS "AlbumEntity_id"
FROM "albums" "AlbumEntity"
    LEFT JOIN "albums_shared_users_users" "AlbumEntity_AlbumEntity__AlbumEntity_sharedUsers"
        ON "AlbumEntity_AlbumEntity__AlbumEntity_sharedUsers"."albumsId"="AlbumEntity"."id"
    LEFT JOIN "users" "AlbumEntity__AlbumEntity_sharedUsers"
        ON "AlbumEntity__AlbumEntity_sharedUsers"."id"="AlbumEntity_AlbumEntity__AlbumEntity_sharedUsers"."usersId"
        AND ("AlbumEntity__AlbumEntity_sharedUsers"."deletedAt" IS NULL)
WHERE
    (
        "AlbumEntity"."id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
        AND "AlbumEntity"."isActivityEnabled" = $11
        AND "AlbumEntity__AlbumEntity_sharedUsers"."id" = $12
    )
    OR (
        "AlbumEntity"."id" IN ($13, $14, $15, $16, $17, $18, $19, $20, $21, $22)
        AND "AlbumEntity"."isActivityEnabled" = $23
        AND "AlbumEntity"."ownerId" = $24
    )
    AND "AlbumEntity"."deletedAt" IS NULL

-- After
SELECT "album"."id" AS "album_id"
FROM "albums" "album"
    LEFT JOIN "albums_shared_users_users" "album_sharedUsers"
        ON "album_sharedUsers"."albumsId"="album"."id"
    LEFT JOIN "users" "sharedUsers"
        ON "sharedUsers"."id"="album_sharedUsers"."usersId"
        AND "sharedUsers"."deletedAt" IS NULL
WHERE
    "album"."id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
    AND "album"."isActivityEnabled" = true
    AND (
        "album"."ownerId" = $11
        OR "sharedUsers"."id" = $12
    )
    AND "album"."deletedAt" IS NULL
  • asset.checkAlbumAccess
-- Before
SELECT
    "asset"."id" AS "assetId",
    "asset"."livePhotoVideoId" AS "livePhotoVideoId"
FROM "albums" "album"
    INNER JOIN "albums_assets_assets" "album_asset"
        ON "album_asset"."albumsId"="album"."id"
    INNER JOIN "assets" "asset"
        ON "asset"."id"="album_asset"."assetsId"
        AND "asset"."deletedAt" IS NULL
    LEFT JOIN "albums_shared_users_users" "album_sharedUsers"
        ON "album_sharedUsers"."albumsId"="album"."id"
    LEFT JOIN "users" "sharedUsers"
        ON "sharedUsers"."id"="album_sharedUsers"."usersId"
        AND "sharedUsers"."deletedAt" IS NULL
WHERE
    (
        "album"."ownerId" = $1
        OR "sharedUsers"."id" = $2
    )
    AND (
        "asset"."id" IN ($3, $4, $5, $6, $7, $8, $9, $10, $11, $12)
        OR "asset"."livePhotoVideoId" IN ($13, $14, $15, $16, $17, $18, $19, $20, $21, $22)
    )
    AND "album"."deletedAt" IS NULL

-- After
SELECT
    "asset"."id" AS "assetId",
    "asset"."livePhotoVideoId" AS "livePhotoVideoId"
FROM "albums" "album"
    INNER JOIN "albums_assets_assets" "album_asset"
        ON "album_asset"."albumsId"="album"."id"
    INNER JOIN "assets" "asset"
        ON "asset"."id"="album_asset"."assetsId"
        AND "asset"."deletedAt" IS NULL
    LEFT JOIN "albums_shared_users_users" "album_sharedUsers"
        ON "album_sharedUsers"."albumsId"="album"."id"
    LEFT JOIN "users" "sharedUsers"
        ON "sharedUsers"."id"="album_sharedUsers"."usersId"
        AND "sharedUsers"."deletedAt" IS NULL
WHERE
    array["assets"."id", "assets"."livePhotoVideoId"] && array[$1, $2, $3, $4, $5, $6, $7, $8, $9, $10]::uuid[]
    AND (
        "album"."ownerId" = $11
        OR "sharedUsers"."id" = $12
    )
    AND "album"."deletedAt" IS NULL
  • asset.checkSharedLinkAccess
-- Before
SELECT
    "assets"."id" AS "assetId",
    "assets"."livePhotoVideoId" AS "assetLivePhotoVideoId",
    "albumAssets"."id" AS "albumAssetId",
    "albumAssets"."livePhotoVideoId" AS "albumAssetLivePhotoVideoId"
FROM "shared_links" "sharedLink"
    LEFT JOIN "albums" "album"
        ON "album"."id"="sharedLink"."albumId"
        AND "album"."deletedAt" IS NULL
    LEFT JOIN "shared_link__asset" "assets_sharedLink"
        ON "assets_sharedLink"."sharedLinksId"="sharedLink"."id"
    LEFT JOIN "assets" "assets"
        ON "assets"."id"="assets_sharedLink"."assetsId"
        AND "assets"."deletedAt" IS NULL
    LEFT JOIN "albums_assets_assets" "album_albumAssets"
        ON "album_albumAssets"."albumsId"="album"."id"
    LEFT JOIN "assets" "albumAssets"
        ON "albumAssets"."id"="album_albumAssets"."assetsId"
        AND "albumAssets"."deletedAt" IS NULL
WHERE
    "sharedLink"."id" = $1
    AND (
        "assets"."id" IN ($2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
        OR "albumAssets"."id" IN ($12, $13, $14, $15, $16, $17, $18, $19, $20, $21)
        OR "assets"."livePhotoVideoId" IN ($22, $23, $24, $25, $26, $27, $28, $29, $30, $31)
        OR "albumAssets"."livePhotoVideoId" IN ($32, $33, $34, $35, $36, $37, $38, $39, $40, $41)
    )

-- After
SELECT
    "assets"."id" AS "assetId",
    "assets"."livePhotoVideoId" AS "assetLivePhotoVideoId",
    "albumAssets"."id" AS "albumAssetId",
    "albumAssets"."livePhotoVideoId" AS "albumAssetLivePhotoVideoId"
FROM "shared_links" "sharedLink"
    LEFT JOIN "albums" "album"
        ON "album"."id"="sharedLink"."albumId"
        AND "album"."deletedAt" IS NULL
    LEFT JOIN "shared_link__asset" "assets_sharedLink"
        ON "assets_sharedLink"."sharedLinksId"="sharedLink"."id"
    LEFT JOIN "assets" "assets"
        ON "assets"."id"="assets_sharedLink"."assetsId"
        AND "assets"."deletedAt" IS NULL
    LEFT JOIN "albums_assets_assets" "album_albumAssets"
        ON "album_albumAssets"."albumsId"="album"."id"
    LEFT JOIN "assets" "albumAssets"
        ON "albumAssets"."id"="album_albumAssets"."assetsId"
        AND "albumAssets"."deletedAt" IS NULL
WHERE
    "sharedLink"."id" = $1
    AND array["assets"."id", "assets"."livePhotoVideoId", "albumAssets"."id", "albumAssets"."livePhotoVideoId"] && array[$2, $3, $4, $5, $6, $7, $8, $9, $10, $11]::uuid[]

🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.

## 📋 Pull Request Information **Original PR:** https://github.com/immich-app/immich/pull/6015 **Author:** [@adamantike](https://github.com/adamantike) **Created:** 12/27/2023 **Status:** ✅ Merged **Merged:** 12/28/2023 **Merged by:** [@alextran1502](https://github.com/alextran1502) **Base:** `main` ← **Head:** `fix/server/reduce-bind-parameters-in-access-queries` --- ### 📝 Commits (2) - [`bac22ba`](https://github.com/immich-app/immich/commit/bac22ba20f49c2c5ccfd9c918204998cfd3fbe24) fix(server): Reduce number of bound parameters in Access queries - [`351ba0e`](https://github.com/immich-app/immich/commit/351ba0e36de36ea5cf8066ca2f9b98b4d6ce7ff2) fix: Use array overlapping instead of CTEs ### 📊 Changes **1 file changed** (+19 additions, -32 deletions) <details> <summary>View changed files</summary> 📝 `server/src/infra/repositories/access.repository.ts` (+19 -32) </details> ### 📄 Description According to https://github.com/typeorm/typeorm/issues/7565, the introduction of bulk queries for permission checks could quickly reach the limit of 65536 bound parameters allowed by the PostgreSQL connection. To avoid reaching that limit, this first change refactors the Access queries that are expanding the set of ids multiple times. For example, `asset.checkSharedLinkAccess` expands the ids 4 times, so providing just ~16400 ids is enough to break the query. Refactored queries: * `activity.checkCreateAccess` ```sql -- Before SELECT "AlbumEntity"."id" AS "AlbumEntity_id" FROM "albums" "AlbumEntity" LEFT JOIN "albums_shared_users_users" "AlbumEntity_AlbumEntity__AlbumEntity_sharedUsers" ON "AlbumEntity_AlbumEntity__AlbumEntity_sharedUsers"."albumsId"="AlbumEntity"."id" LEFT JOIN "users" "AlbumEntity__AlbumEntity_sharedUsers" ON "AlbumEntity__AlbumEntity_sharedUsers"."id"="AlbumEntity_AlbumEntity__AlbumEntity_sharedUsers"."usersId" AND ("AlbumEntity__AlbumEntity_sharedUsers"."deletedAt" IS NULL) WHERE ( "AlbumEntity"."id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10) AND "AlbumEntity"."isActivityEnabled" = $11 AND "AlbumEntity__AlbumEntity_sharedUsers"."id" = $12 ) OR ( "AlbumEntity"."id" IN ($13, $14, $15, $16, $17, $18, $19, $20, $21, $22) AND "AlbumEntity"."isActivityEnabled" = $23 AND "AlbumEntity"."ownerId" = $24 ) AND "AlbumEntity"."deletedAt" IS NULL -- After SELECT "album"."id" AS "album_id" FROM "albums" "album" LEFT JOIN "albums_shared_users_users" "album_sharedUsers" ON "album_sharedUsers"."albumsId"="album"."id" LEFT JOIN "users" "sharedUsers" ON "sharedUsers"."id"="album_sharedUsers"."usersId" AND "sharedUsers"."deletedAt" IS NULL WHERE "album"."id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10) AND "album"."isActivityEnabled" = true AND ( "album"."ownerId" = $11 OR "sharedUsers"."id" = $12 ) AND "album"."deletedAt" IS NULL ``` * `asset.checkAlbumAccess` ```sql -- Before SELECT "asset"."id" AS "assetId", "asset"."livePhotoVideoId" AS "livePhotoVideoId" FROM "albums" "album" INNER JOIN "albums_assets_assets" "album_asset" ON "album_asset"."albumsId"="album"."id" INNER JOIN "assets" "asset" ON "asset"."id"="album_asset"."assetsId" AND "asset"."deletedAt" IS NULL LEFT JOIN "albums_shared_users_users" "album_sharedUsers" ON "album_sharedUsers"."albumsId"="album"."id" LEFT JOIN "users" "sharedUsers" ON "sharedUsers"."id"="album_sharedUsers"."usersId" AND "sharedUsers"."deletedAt" IS NULL WHERE ( "album"."ownerId" = $1 OR "sharedUsers"."id" = $2 ) AND ( "asset"."id" IN ($3, $4, $5, $6, $7, $8, $9, $10, $11, $12) OR "asset"."livePhotoVideoId" IN ($13, $14, $15, $16, $17, $18, $19, $20, $21, $22) ) AND "album"."deletedAt" IS NULL -- After SELECT "asset"."id" AS "assetId", "asset"."livePhotoVideoId" AS "livePhotoVideoId" FROM "albums" "album" INNER JOIN "albums_assets_assets" "album_asset" ON "album_asset"."albumsId"="album"."id" INNER JOIN "assets" "asset" ON "asset"."id"="album_asset"."assetsId" AND "asset"."deletedAt" IS NULL LEFT JOIN "albums_shared_users_users" "album_sharedUsers" ON "album_sharedUsers"."albumsId"="album"."id" LEFT JOIN "users" "sharedUsers" ON "sharedUsers"."id"="album_sharedUsers"."usersId" AND "sharedUsers"."deletedAt" IS NULL WHERE array["assets"."id", "assets"."livePhotoVideoId"] && array[$1, $2, $3, $4, $5, $6, $7, $8, $9, $10]::uuid[] AND ( "album"."ownerId" = $11 OR "sharedUsers"."id" = $12 ) AND "album"."deletedAt" IS NULL ``` * `asset.checkSharedLinkAccess` ```sql -- Before SELECT "assets"."id" AS "assetId", "assets"."livePhotoVideoId" AS "assetLivePhotoVideoId", "albumAssets"."id" AS "albumAssetId", "albumAssets"."livePhotoVideoId" AS "albumAssetLivePhotoVideoId" FROM "shared_links" "sharedLink" LEFT JOIN "albums" "album" ON "album"."id"="sharedLink"."albumId" AND "album"."deletedAt" IS NULL LEFT JOIN "shared_link__asset" "assets_sharedLink" ON "assets_sharedLink"."sharedLinksId"="sharedLink"."id" LEFT JOIN "assets" "assets" ON "assets"."id"="assets_sharedLink"."assetsId" AND "assets"."deletedAt" IS NULL LEFT JOIN "albums_assets_assets" "album_albumAssets" ON "album_albumAssets"."albumsId"="album"."id" LEFT JOIN "assets" "albumAssets" ON "albumAssets"."id"="album_albumAssets"."assetsId" AND "albumAssets"."deletedAt" IS NULL WHERE "sharedLink"."id" = $1 AND ( "assets"."id" IN ($2, $3, $4, $5, $6, $7, $8, $9, $10, $11) OR "albumAssets"."id" IN ($12, $13, $14, $15, $16, $17, $18, $19, $20, $21) OR "assets"."livePhotoVideoId" IN ($22, $23, $24, $25, $26, $27, $28, $29, $30, $31) OR "albumAssets"."livePhotoVideoId" IN ($32, $33, $34, $35, $36, $37, $38, $39, $40, $41) ) -- After SELECT "assets"."id" AS "assetId", "assets"."livePhotoVideoId" AS "assetLivePhotoVideoId", "albumAssets"."id" AS "albumAssetId", "albumAssets"."livePhotoVideoId" AS "albumAssetLivePhotoVideoId" FROM "shared_links" "sharedLink" LEFT JOIN "albums" "album" ON "album"."id"="sharedLink"."albumId" AND "album"."deletedAt" IS NULL LEFT JOIN "shared_link__asset" "assets_sharedLink" ON "assets_sharedLink"."sharedLinksId"="sharedLink"."id" LEFT JOIN "assets" "assets" ON "assets"."id"="assets_sharedLink"."assetsId" AND "assets"."deletedAt" IS NULL LEFT JOIN "albums_assets_assets" "album_albumAssets" ON "album_albumAssets"."albumsId"="album"."id" LEFT JOIN "assets" "albumAssets" ON "albumAssets"."id"="album_albumAssets"."assetsId" AND "albumAssets"."deletedAt" IS NULL WHERE "sharedLink"."id" = $1 AND array["assets"."id", "assets"."livePhotoVideoId", "albumAssets"."id", "albumAssets"."livePhotoVideoId"] && array[$2, $3, $4, $5, $6, $7, $8, $9, $10, $11]::uuid[] ``` --- <sub>🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.</sub>
OVERLORD added the pull-request label 2026-02-05 14:32:37 +03:00
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
accessibility
changelog:enhancement
changelog:security
changelog:skip
changelog:translation
cli
date-time
dependencies
documentation
external-library
format
good first issue
mobile-beta
mobile-beta
mobile-beta
needs-answer
nice to have
pull-request
Mirrored from GitHub Pull Request
 sharing
tech-debt
📱mobile
🖥️web
🗄️server
🧠machine-learning
No Label pull-request
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
OVERLORD
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: immich-app/immich#11046
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?