Why Ecto doesn't allow limit in delete_all?

Questions / Help
1

Why Ecto doesn’t allow :limit in Repo.delete_all/2 ? It’s useful in some cases. It should be supported.

2

I have no idea why but if you’re looking for workarounds you might try something like:

query = from r in Review, select: r.id, limit: 10
review_ids = Repo.all(query)

query = from r in Review, where: r.id in ^review_ids
Repo.delete_all(query)
2 Likes
3

It’s not supported at all in Postgres:

https://www.postgresql.org/message-id/425494B0.3020806%40samurai.com

Ecto shouldn’t have any trouble producing the subquery suggested there as a workaround.

4 Likes
4

In my case, I have a delete fun that runs everyday and the data can be really huge. I need to do it in batches to avoid table locks. Select and delete is a workaround but I’m looking for the most efficient solution.

5

I see. Yup looks good but looks like doing it in raw SQL with no select, just straight delete with limit will work best in my situation.

6

I think you’re already there, but query/4 is a nice escape hatch for your raw SQL.

1 Like
7

I think you’re looking for Repo.stream.

2 Likes
8

Yes this is what I used. I still think that Ecto should add support to :limit with Repo.delete_all/2 though :slight_smile:

1 Like