@inedo_1308 sounds good!
The code would almost certainly be the same, since it hasn't been updated since we did the PostgreSQL version of the script.
So, I think it's a race condition, though I don't know how it would happen. However, if it's a race condition, then it should be solved with an UPDLOCK (or whatever) in PostgreSQL.
- SELECT finds no matching blob in the database (thus
DockerBlob_Id is null)
- ... small delay ...
- UPDATE finds the matching blob because it was added (thus a row gets added to
insert)
- INSERT does run because there is a row in
inserted
- A NULL DockerBlob_Id is returned
If you're able to patch the procedure, could you add FOR UPDATE as follows? We are still relatively to PostgreSQL so I don't know if this the right way to do it in this case.
I think a second SELECT could also work, but I dunno.
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
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"
FOR UPDATE;
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 $$;
type of errors