cache_key already exists errors in PostgreSQL

[FlipperPA]

We're having an issues using Django's database cache backend with PostgreSQL and django-ratelimit. We're wondering if this rings a bell with anyone.

Here's a sample of the errors we're seeing in PostgreSQL:

--
Jan 13 13:23:52 myco-pg1-w postgres[276652]: [16-1] 61e06d19.438ac (myco_web_user@myco_web) ERROR:  duplicate key value violates unique constraint "django_db_cache_pkey"
Jan 13 13:23:52 myco-pg1-w postgres[276652]: [16-2] 61e06d19.438ac (myco_web_user@myco_web) DETAIL:  Key (cache_key)=(:1:rl:28c8c88c214fb338eda51b4670e2ecc3) already exists.
Jan 13 13:23:52 myco-pg1-w postgres[276652]: [16-3] 61e06d19.438ac (myco_web_user@myco_web) STATEMENT:  INSERT INTO "django_db_cache" ("cache_key", "value", "expires") VALUES (':1:rl:28c8c88c214fb338eda51b4670e2ecc3', 'gAVLAS4=', '2022-01-13T19:23:57'::timestamp)
--
Jan 13 13:24:08 myco-pg1-w postgres[242597]: [222-1] 61e06bfe.3b3a5 (myco_web_user@myco_web) ERROR:  duplicate key value violates unique constraint "django_db_cache_pkey"
Jan 13 13:24:08 myco-pg1-w postgres[242597]: [222-2] 61e06bfe.3b3a5 (myco_web_user@myco_web) DETAIL:  Key (cache_key)=(:1:rl:cf8c7c89bd2905e99bf28775266466b1) already exists.
Jan 13 13:24:08 myco-pg1-w postgres[242597]: [222-3] 61e06bfe.3b3a5 (myco_web_user@myco_web) STATEMENT:  INSERT INTO "django_db_cache" ("cache_key", "value", "expires") VALUES (':1:rl:cf8c7c89bd2905e99bf28775266466b1', 'gAVLAS4=', '2022-01-13T19:24:13'::timestamp)
--
Jan 13 13:24:20 myco-pg1-w postgres[17644]: [15-1] 61e06e37.44ec (myco_web_user@myco_web) ERROR:  duplicate key value violates unique constraint "django_db_cache_pkey"
Jan 13 13:24:20 myco-pg1-w postgres[17644]: [15-2] 61e06e37.44ec (myco_web_user@myco_web) DETAIL:  Key (cache_key)=(:1:rl:aefc785514b81360eb2a7347251def1a) already exists.
Jan 13 13:24:20 myco-pg1-w postgres[17644]: [15-3] 61e06e37.44ec (myco_web_user@myco_web) STATEMENT:  INSERT INTO "django_db_cache" ("cache_key", "value", "expires") VALUES (':1:rl:aefc785514b81360eb2a7347251def1a', 'gAVLAS4=', '2022-01-13T19:24:25'::timestamp)
--
Jan 13 13:24:44 myco-pg1-w postgres[11512]: [15-1] 61e06dc2.2cf8 (myco_web_user@myco_web) ERROR:  duplicate key value violates unique constraint "django_db_cache_pkey"
Jan 13 13:24:44 myco-pg1-w postgres[11512]: [15-2] 61e06dc2.2cf8 (myco_web_user@myco_web) DETAIL:  Key (cache_key)=(:1:rl:c68e29bf5507ec60e8f7b9dd9cc2f87f) already exists.
Jan 13 13:24:44 myco-pg1-w postgres[11512]: [15-3] 61e06dc2.2cf8 (myco_web_user@myco_web) STATEMENT:  INSERT INTO "django_db_cache" ("cache_key", "value", "expires") VALUES (':1:rl:c68e29bf5507ec60e8f7b9dd9cc2f87f', 'gAVLAS4=', '2022-01-13T19:24:49'::timestamp)

We noticed that the offending cache keys are generated by django-ratelimit because of the :1:rl: prefix in the cache_key.

When we de-pickle the values, they're single integers, like 1, 2, 3, etc. I haven't taken a deep dive into the code yet. Django's cache DB backend code runs everything in a try / except block, which would explain why we are not seeing errors from Django itself, and only in the database. The relevant block seems to be here: https://github.com/django/django/blob/main/django/core/cache/backends/db.py#L135-L190

But it also seems that the if / elif / else block, following the SELECT, should prevent a key conflict. Does this ring a bell for anyone as an issue? This would be difficult to do a simple repro for, but we can try, if necessary.

[J4bbi]

I have the same issue using a Postgres cache.

[xMinhx]

have the same issue

[jsocol]

I'm curious if the ratelimiter still works or if these errors are more of an annoyance/noise for logs?

When we de-pickle the values, they're single integers, like 1, 2, 3, etc.

Those are the usage counts.

I don't know that this is fixable. The first thing ratelimit tries to do is add the new key to the cache, and whether that succeeds or fails is important for the rest of processing the request. It also looks like the database backend doesn't have an atomic increment—and since it's storing encoded, pickled values, I'm not sure that it could.

Given the lack of atomic increment, I'm not sure it's reasonable to call the database backend "supported" for use with this library. Races will potentially allow multiple transactions to commit the same count number.

I do think it might be possible to build a "partial" implementation of the cache interface base—one that only supports integer values and so can support atomic/transactional increment—based on a dedicated model. That would require its own expired-row-reaping cron, too, since it wouldn't be in the usual cache table. I'd be very open to including an implementation like that, maybe in django_ratelimit/contrib/modelcache or something similar, but I'm probably not going to take it on myself.

(Theoretically there are a few ways to make this library work but it requires a storage mechanism with either atomic increment or check-and-set to work reliably.)

At the very least, I will add something to the documentation, though, about requiring a cache backend with support for an atomic increments to work reliably.

[ARezaK]

Realistically should not be using database as cache for something like this

[danmoz]

That would require its own expired-row-reaping cron, too, since it wouldn't be in the usual cache table

I'm not sure it would be required... couldn't you just could have a second ORM call that reaps expired rows as part of the same thread of execution that does the atomic insert on the model table?

[danmoz]

Realistically should not be using database as cache for something like this

I get why you're being downvoted, but I agree. If you're using a rate limit to protect your DB from crashing, then storing the rate limit in the DB makes no sense, because no DB == no rate limit.

[benjaoming]

no DB == no rate limit.

no DB => no website

You might be using ratelimiting to protect against other types of annoying behavior, such as sign-up spam or a lot of form submissions.

[jsocol]

That would require its own expired-row-reaping cron, too, since it wouldn't be in the usual cache table

I'm not sure it would be required... couldn't you just could have a second ORM call that reaps expired rows as part of the same thread of execution that does the atomic insert on the model table?

True, maybe not required but definitely preferable to doing several queries to check the ratelimits before we even get to the view.

I added notes to the docs in #265 highlighting the need for atomic increments.

I'm still open to either contrib-style implementation of the partial cache interface, or other ideas or recipes to add to the docs, if anyone is interested in pursuing those. For now, I'm going to close this out and say that the DB cache backends and any others without atomic increment operations aren't supported.