Hi @felfert ,
As an update, we'd are planning on this pattern instead of row/table locking (PG-3104). It gives us a lot more control and makes it a lot easier to avoid deadlocks.
I still can't reproduce the issue, but I see no reason this won't work.
CREATE OR REPLACE FUNCTION "DockerBlobs_CreateOrUpdateBlob"
(
"@Feed_Id" INT,
"@Blob_Digest" VARCHAR(128),
"@Blob_Size" BIGINT,
"@MediaType_Name" VARCHAR(255) = NULL,
"@Cached_Indicator" BOOLEAN = NULL,
"@Download_Count" INT = NULL,
"@DockerBlob_Id" INT = NULL
)
RETURNS INT
LANGUAGE plpgsql
AS $$
BEGIN
-- avoid race condition when two procs call at exact same time
PERFORM PG_ADVISORY_XACT_LOCK(HASHTEXT(CONCAT_WS('DockerBlobs_CreateOrUpdateBlob', "@Feed_Id", LOWER("@Blob_Digest"))));
SELECT "DockerBlob_Id"
INTO "@DockerBlob_Id"
FROM "DockerBlobs"
WHERE ("Feed_Id" = "@Feed_Id" OR ("Feed_Id" IS NULL AND "@Feed_Id" IS NULL))
AND "Blob_Digest" = "@Blob_Digest";
WITH updated AS
(
UPDATE "DockerBlobs"
SET "Blob_Size" = "@Blob_Size",
"MediaType_Name" = COALESCE("@MediaType_Name", "MediaType_Name"),
"Cached_Indicator" = COALESCE("@Cached_Indicator", "Cached_Indicator")
WHERE ("Feed_Id" = "@Feed_Id" OR ("Feed_Id" IS NULL AND "@Feed_Id" IS NULL))
AND "Blob_Digest" = "@Blob_Digest"
RETURNING *
)
INSERT INTO "DockerBlobs"
(
"Feed_Id",
"Blob_Digest",
"Download_Count",
"Blob_Size",
"MediaType_Name",
"Cached_Indicator"
)
SELECT
"@Feed_Id",
"@Blob_Digest",
COALESCE("@Download_Count", 0),
"@Blob_Size",
"@MediaType_Name",
COALESCE("@Cached_Indicator", 'N')
WHERE NOT EXISTS (SELECT * FROM updated)
RETURNING "DockerBlob_Id" INTO "@DockerBlob_Id";
RETURN "@DockerBlob_Id";
END $$;
