primary key violates unique constraint upon sending lots of create payment requests with the same customer id

[heyrutvik]

Discovered while running payment-confirm loadtesting script.

Log:

{
  ...
  "flow":"PaymentsCreate",
  "extra":{
    "api_authentication":"ApiKey",
    "error":"{\"error\":{\"type\":\"server_not_available\",\"code\":\"RE_00\",\"message\":\"Something went wrong.\"}}\n├╴crates/router/src/core/payments.rs:96:10\n│\n├─▶ DatabaseError: A unique constraint violation occurred\n│   ├╴crates/router/src/types/storage/query/generics.rs:168:22\n│   ╰╴Error while inserting CustomerNew { customer_id: \"StripeCustomer\", merchant_id: \"merchant_1669119076606\", name: Some(\"John Doe\"), email: Some(*****@example.com), phone: Some(*** alloc::string::String ***), description: None, phone_country_code: Some(\"+65\"), address: None, metadata: None }\n│\n╰─▶ Failed to issue a query: duplicate key value violates unique constraint \"customers_pkey\"\n    ╰╴crates/router/src/types/storage/query/generics.rs:167:27",
    "http.client_ip":"192.168.48.10",
    "http.flavor":"1.1",
    "http.host":"router-server:8080",
    "http.method":"POST",
    "http.route":"/payments"
    ...
  }
}

Steps to reproduce: check out rnd-loadtest-failing-requests branch I made a minor change which gives us access to logs of the router container. once you checked out the branch:

cd loadtest
docker compose build # will take some time
bash loadtest.sh -s payment-confirm

the last command will give you something like http_req_failed................: 0.18% ✓ 7 ✗ 3718 in the end. The numbers could be different. Those 7 requests are failed.

You can access the logs stored at loadtest/logs.tmp/logs. Search for "server_not_available" string in it.

Notes:

1. Those customer queries need to be in a single transaction but currently we are not using pgdb as a relational storage so that would not be a good solution.

Possible solutions:

1. We implement api level locking in future, that will handle parallel request errors. (cc @jarnura)