Workflow persistense improvement

Workflow persistense improvement

Postby Raol » 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

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;
Raol
 
Posts: 1
Joined: Mon 30 May 2016 08:40

Re: Workflow persistense improvement

Postby Shalex » Thu 02 Jun 2016 10:13

Thank you for your feedback. We will investigate the question and notify you about the result.
Shalex
Devart Team
 
Posts: 7780
Joined: Thu 14 Aug 2008 12:44

Re: Workflow persistense improvement

Postby Shalex » Wed 22 Jun 2016 15:33

Raol wrote: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.
Shalex
Devart Team
 
Posts: 7780
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for PostgreSQL