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

[melinam63825268]

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

[DavidKangni]

Hi @melinam63825268 

use "insertOrUpdate"  

Thanks,

David

[melinam63825268]

Hi ,

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

[akshaaga · Accepted solution]

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_]

Hello @melinam63825268 

change these two things:

1. operation change to: insertOrUpdate

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

[Sukrity_Wadhwa]

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!