FIX: Docker validation trigger may allow for duplicate images to be added [PostgreSQL]
Created:
10/20/2025 4:16:24 AM by Alana Tripp
Status:
Awaiting Release on
Last pulled:
10/24/2025 9:57:44 PM
Description:
See https://forums.inedo.com/post/18788
Based on the behavior, it's clear that there is a duplicate image. Although `DockerImages__ValidateUniqueDigest` is designed to prevent that, a race condition could for two images to be inserted.
To resolve this, we should use an advisory lock.
Proposed (untested) code:
```
CREATE OR REPLACE FUNCTION "DockerImages__ValidateUniqueDigest"()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
lock_key BIGINT;
BEGIN
SELECT HASHTEXT(CONCAT_WS('DockerImages__ValidateUniqueDigest', CAST(DR."Feed_Id" AS text), NEW."Image_Digest"))
INTO lock_key
FROM "DockerRepositories" DR
WHERE DR."DockerRepository_Id" = NEW."DockerRepository_Id";
PERFORM pg_advisory_xact_lock(lock_key);
IF EXISTS (
SELECT *
FROM "DockerRepositories" DR_I
INNER JOIN "DockerImages" DI
ON DI."Image_Digest" = NEW."Image_Digest"
INNER JOIN "DockerRepositories" DR
ON DR."DockerRepository_Id" = DI."DockerRepository_Id"
WHERE DR_I."DockerRepository_Id" = NEW."DockerRepository_Id"
AND DR."Feed_Id" = DR_I."Feed_Id"
AND DI."DockerImage_Id" <> NEW."DockerImage_Id"
AND DI."DockerRepository_Id" = NEW."DockerRepository_Id"
) THEN
RAISE EXCEPTION 'Image_Digest must be unique across the containing feed.';
END IF;
RETURN NEW;
END $$;
```