Expand my Community achievements bar.

We are excited to introduce our latest innovation to enhance the Adobe Campaign user experience — the Adobe Campaign v8 Web User Interface!
SOLVED

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

Avatar

Level 2

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. .

1 Accepted Solution

Avatar

Correct answer by
Employee Advisor

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.

View solution in original post

5 Replies

Avatar

Community Advisor

Hi @melinam63825268 

use "insertOrUpdate"  

Thanks,

David



David Kangni

Avatar

Level 2

Hi ,

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

melinam63825268_0-1679372572390.png

 

Avatar

Correct answer by
Employee Advisor

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.

Avatar

Community Advisor

Hello @melinam63825268 

 

change these two things:

1. operation change to: insertOrUpdate

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


     Manoj
     Find me on LinkedIn

Avatar

Administrator

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