Workflow persistense improvement

[Raol]

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

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 statement

We've examined the original source of the CreateServiceDeployment function and found that the problem was in there.

Code: 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;  

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

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;

[Shalex]

Thank you for your feedback. We will investigate the question and notify you about the result.

[Shalex]

So we suggest doing following (in PostgreSQL 9.5 or higher).

We do not update the predefined scripts because some users may use the previous versions of PostgreSQL Server. Thanks again for sharing your experience.