PGS-220000 PostgreSQL error: ERROR: duplicate key value violates unique constraint "nmssubscription_subscription" | Community
Skip to main content
M
melinam63825268
Level 2
March 16, 2023
Solved

PGS-220000 PostgreSQL error: ERROR: duplicate key value violates unique constraint "nmssubscription_subscription"

  • March 16, 2023
  • 4 replies
  • 2339 views

Trying below code to add a subscription for a recipient through javascript. This query runs successfully for a newly created recipient . But it fails if I am trying for a recipient who have subscribed and unsubscribed for the same service already.

 

xtk.session.Write(<subscription _operation="insert" xtkschema="nms:subscription" _key = "@recipient-id,@service-id,@sourceId,@created" created= {formateddate} service-id={ serviceId } recipient-id={ recipientId } sourceId={ sourceId} deleteStatus='0'/>);

Error:

03/16/2023 11:14:11 AM SCR-160012 Javascript&colon; error while evaluating script 'renameWKF3253/js2'.
03/16/2023 11:14:11 AM SOP-330011 Error while executing the method 'Write' of service 'xtk:persist|xtk:session'.
03/16/2023 11:14:11 AM XSV-350023 Unable to save document of type 'Subscriptions (nms:subscription)'.
03/16/2023 11:14:10 AM 80 Param(3)=40698347 Param(4)=2028
03/16/2023 11:14:10 AM WDB-200001 SQL statement 'INSERT INTO NmsSubscription (tsCreated, iDeleteStatus, iServiceId, iRecipientId, iSourceId) VALUES (:#(1)#, :#(2)#, :#(3)#, :#(4)#, :#(5)#)' could not be executed. Param(0)=03/16/2023 11:14:10 AM Param(1)=0 Param(2)=475757
03/16/2023 11:14:10 AM PGS-220000 PostgreSQL error: ERROR: duplicate key value violates unique constraint "nmssubscription_subscription" DETAIL: Key (iserviceid, irecipientid, isourceid)=(46456, 870769, 2340) already exists. .

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by akshaaga

Hi @melinam63825268 ,

The error message suggests that the record you're trying to insert violates a unique constraint on the "nms:subscription" table, specifically the "nmssubscription_subscription" constraint on the "iserviceid, irecipientid, isourceid" columns. This constraint ensures only one subscription record per service, recipient, and source.

Based on the error message, it seems like you're trying to insert a subscription record that already exists in the table. This could be because the recipient has already subscribed to the service and the subscription record was not properly deleted when they unsubscribed or because there is a race condition where two subscription records are being created simultaneously for the same recipient and service.

To resolve the issue, you may need to check if a subscription record already exists for the recipient and service before attempting to insert a new one. You could also try updating the existing subscription record instead of inserting a new one.

4 replies

DavidKangni
Community Advisor
DavidKangniCommunity Advisor
Community Advisor
March 16, 2023

Hi @melinam63825268 

use "insertOrUpdate"  

Thanks,

David

David Kangni
M
melinam63825268Author
Level 2
March 21, 2023

Hi ,

Thanks for your response. Still I am facing same issue and getting below error 

 

akshaaga
Adobe Employee
akshaagaAdobe EmployeeAccepted solution
Adobe Employee
March 21, 2023

Hi @melinam63825268 ,

The error message suggests that the record you're trying to insert violates a unique constraint on the "nms:subscription" table, specifically the "nmssubscription_subscription" constraint on the "iserviceid, irecipientid, isourceid" columns. This constraint ensures only one subscription record per service, recipient, and source.

Based on the error message, it seems like you're trying to insert a subscription record that already exists in the table. This could be because the recipient has already subscribed to the service and the subscription record was not properly deleted when they unsubscribed or because there is a race condition where two subscription records are being created simultaneously for the same recipient and service.

To resolve the issue, you may need to check if a subscription record already exists for the recipient and service before attempting to insert a new one. You could also try updating the existing subscription record instead of inserting a new one.

    Manoj_Kumar
    Community Advisor
    Manoj_KumarCommunity Advisor
    Community Advisor
    March 22, 2023

    Hello @melinam63825268 

     

    change these two things:

    1. operation change to: insertOrUpdate

    2. remove created from key: @recipient-id,@service-id,@sourceId

    Manoj  | https://themartech.pro
      Sukrity_Wadhwa
      Community Manager
      Sukrity_WadhwaCommunity Manager
      Community Manager
      April 3, 2023

      Hi @melinam63825268,

      Were you able to resolve this query with the help of the given solutions or do you still need more help here? Do let us know. In case the given solutions were helpful, then kindly choose the one that helped you the most as the 'Correct Reply'.
      Thanks!

      Sukrity Wadhwa
      Terms & ConditionsAccessibility statement

      Loading footer...