SQL error when requesting stats by author

[steelbrain]

URL: https://npm-stat.com/charts.html?author=steelbrain&from=2015-07-15&to=2018-07-14

JSON response: https://gist.github.com/steelbrain/fd9d27b7c0a515a23d479f0d9551d023

How it was rendered in the browser:

[pvorb]

I couldn't reproduce the issue, but I've updated the service since you opened this issue, so I guess it's fixed now.

Could you please confirm this is no longer an issue?

[johnbrett]

I recently tried a search and got this error, but then searched again and it didn't happen. Not sure if that'll help with debugging :/

[pvorb]

Well, could you be a little more precise what you mean with "recently"?

[johnbrett]

Oh apologies :) I'd meant like 30 minutes before I posted the comment.

[kumavis]

@pvorb I get the same ERROR: duplicate key value for https://npm-stat.com/charts.html?author=kumavis

[MoOx]

I just faced this issue. Then I waited a little, & then, while trying again after having prepared an issue & trying to double check the problem, issue was gone!

[marvinhagemeister]

Just ran into the same error when checking the stats for my username.

URL to reproduce: https://npm-stat.com/charts.html?author=marvinhagemeister&from=2019-07-05&to=2020-07-05

[pvorb]

@marvinhagemeister It takes a while but it works without a problem for me. Can you please try again?

[marvinhagemeister]

@pvorb Weird, you're right it works now. Not sure what changed :shrug:

[pvorb]

I know that npm returned 500 errors today, so probably this was the cause.

[steelbrain]

Guess from personal experience: When you request the stats once, even if you abort the network connection, the request continues, so if you make a subsequent request you get this error.

Fix is to just wait for the first request to finish, after it finishes, the results are cached. So you can request again and it'll respond from cache and it'll work fine.

[willin]

Could not fetch data. 500 error {"timestamp":"2021-11-24T07:40:18.685+0000","status":500,"error":"Internal Server Error","message":"jOOQ; SQL [insert into \"public\".\"download_count\" (\"package_name\", \"date\", \"count\") values (?, cast(? as date), ?)]; ERROR: duplicate key value violates unique constraint \"72_72_download_count_pkey\"\n Detail: Key (package_name, date)=(@white-matrix/blindbox-sdk, 2020-11-23) already exists.; nested exception is org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint \"72_72_download_count_pkey\"\n Detail: Key (package_name, date)=(@white-matrix/blindbox-sdk, 2020-11-23) already exists.","path":"/npm-stat/api/download-counts"}

https://npm-stat.com/charts.html?author=willin

[pvorb]

Yep, that's the same problem. The transaction fails because of two concurrent writes. I could add a retry mechanism, but I think this problem is quite rare and fixed by reloading the site.