Foreign Key Constraint violation on delete?
I'm running immich v1.132.3 on docker. I wanted to delete a bunch of files that I had uploaded into Immich back in April under the wrong user - they were my wife's photos but I uploaded them as me. I wrote some python code to verify and then delete the files: I query the assets table in postgres to get the id of the assets within a specific createdAt date and then I issue a bunch of deletes via the /api/assets/ api.
It was going well, though slowly as one might imagine on a 16Gb ras pi 5 but now I'm following the docker logs and I am seeing a bunch of errors from postgres like this:
immich_server | [Nest] 8 - 05/15/2025, 11:46:51 PM ERROR [Microservices:{"id":"89ac19fa-7de5-408f-a5af-25a140a7c9ad","deleteOnDisk":true}] Unable to run job handler (backgroundTask/asset-deletion): PostgresError: update or delete on table "assets" violates foreign key constraint "FK_91704e101438fd0653f582426dc" on table "asset_stack"
immich_server | PostgresError: update or delete on table "assets" violates foreign key constraint "FK_91704e101438fd0653f582426dc" on table "asset_stack"
immich_server | at ErrorResponse (/usr/src/app/node_modules/postgres/cjs/src/connection.js:790:26)
immich_server | at handle (/usr/src/app/node_modules/postgres/cjs/src/connection.js:476:6)
immich_server | at Socket.data (/usr/src/app/node_modules/postgres/cjs/src/connection.js:315:9)
immich_server | at Socket.emit (node:events:518:28)
immich_server | at addChunk (node:internal/streams/readable:561:12)
immich_server | at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
immich_server | at Readable.push (node:internal/streams/readable:392:5)
immich_server | at TCP.onStreamRead (node:internal/stream_base_commons:189:23)
immich_server | [Nest] 8 - 05/15/2025, 11:46:53 PM LOG [Microservices:TrashService] Queued 3949 asset(s) for deletion from the trash
to be continued in next reply...
It was going well, though slowly as one might imagine on a 16Gb ras pi 5 but now I'm following the docker logs and I am seeing a bunch of errors from postgres like this:
immich_server | [Nest] 8 - 05/15/2025, 11:46:51 PM ERROR [Microservices:{"id":"89ac19fa-7de5-408f-a5af-25a140a7c9ad","deleteOnDisk":true}] Unable to run job handler (backgroundTask/asset-deletion): PostgresError: update or delete on table "assets" violates foreign key constraint "FK_91704e101438fd0653f582426dc" on table "asset_stack"
immich_server | PostgresError: update or delete on table "assets" violates foreign key constraint "FK_91704e101438fd0653f582426dc" on table "asset_stack"
immich_server | at ErrorResponse (/usr/src/app/node_modules/postgres/cjs/src/connection.js:790:26)
immich_server | at handle (/usr/src/app/node_modules/postgres/cjs/src/connection.js:476:6)
immich_server | at Socket.data (/usr/src/app/node_modules/postgres/cjs/src/connection.js:315:9)
immich_server | at Socket.emit (node:events:518:28)
immich_server | at addChunk (node:internal/streams/readable:561:12)
immich_server | at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
immich_server | at Readable.push (node:internal/streams/readable:392:5)
immich_server | at TCP.onStreamRead (node:internal/stream_base_commons:189:23)
immich_server | [Nest] 8 - 05/15/2025, 11:46:53 PM LOG [Microservices:TrashService] Queued 3949 asset(s) for deletion from the trash
to be continued in next reply...
