Workflow persistense improvement
Posted: Mon 30 May 2016 09:19
Hi. We've faced with the problem, while were running concurrent workflow creation test. Some of workflow instances were failed to create. After investigation we've found following error in the pg_log
We've examined the original source of the CreateServiceDeployment function and found that the problem was in there.
During concurrent execution duplicate exception is still possible.
So we suggest doing following (in PostgreSQL 9.5 or higher). Leverage functionality of ON CONFLICT statement and rewrite procedure as
Code: Select all
2016-05-27 17:14:13 EEST ERROR: duplicate key value violates unique constraint "ix_servicedeploymenthash"
2016-05-27 17:14:13 EEST DETAIL: Key (servicedeploymenthash)=(57597b83-6444-a92a-16b6-3776f6302a83) already exists.
2016-05-27 17:14:13 EEST CONTEXT: SQL statement "INSERT INTO ServiceDeploymentsTable (ServiceDeploymentHash, SiteName, RelativeServicePath, RelativeApplicationPath, ServiceName, ServiceNamespace) VALUES (p_serviceDeploymentHash, p_siteName, p_relativeServicePath, p_relativeApplicationPath, p_serviceName, p_serviceNamespace) RETURNING id"
PL/pgSQL function createservicedeployment(uuid,character varying,character varying,character varying,character varying,character varying) line 6 at SQL statementCode: Select all
SELECT Id INTO p_serviceDeploymentId FROM ServiceDeploymentsTable WHERE ServiceDeploymentHash = p_serviceDeploymentHash;
IF p_serviceDeploymentId IS NULL THEN
INSERT INTO ServiceDeploymentsTable (ServiceDeploymentHash, SiteName, RelativeServicePath, RelativeApplicationPath, ServiceName, ServiceNamespace) VALUES (p_serviceDeploymentHash, p_siteName, p_relativeServicePath, p_relativeApplicationPath, p_serviceName, p_serviceNamespace) RETURNING id INTO p_serviceDeploymentId;
END IF; So we suggest doing following (in PostgreSQL 9.5 or higher). Leverage functionality of ON CONFLICT statement and rewrite procedure as
Code: Select all
CREATE OR REPLACE FUNCTION public.createservicedeployment(
p_servicedeploymenthash uuid,
p_sitename character varying,
p_relativeservicepath character varying,
p_relativeapplicationpath character varying,
p_servicename character varying,
p_servicenamespace character varying)
RETURNS bigint AS
$BODY$
DECLARE p_serviceDeploymentId BIGINT;
DECLARE p_rowcount INT;
BEGIN
INSERT INTO ServiceDeploymentsTable (ServiceDeploymentHash, SiteName, RelativeServicePath, RelativeApplicationPath, ServiceName, ServiceNamespace)
VALUES (p_serviceDeploymentHash, p_siteName, p_relativeServicePath, p_relativeApplicationPath, p_serviceName, p_serviceNamespace)
ON CONFLICT (ServiceDeploymentHash) DO NOTHING
RETURNING id INTO p_serviceDeploymentId;
GET DIAGNOSTICS p_rowcount = ROW_COUNT;
IF(p_rowcount = 0)
THEN
SELECT Id INTO p_serviceDeploymentId FROM ServiceDeploymentsTable WHERE ServiceDeploymentHash = p_serviceDeploymentHash;
END IF;
RETURN p_serviceDeploymentId;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION public.createservicedeployment(uuid, character varying, character varying, character varying, character varying, character varying)
OWNER TO postgres;