Replacing stored procedures with Elixir processes?

Yup, I see it. I shared this link with the team.

I think I was confused what the IF found does. I think I understand now.

It makes that syntax irrelevant. The postgres documentation on it is very good: https://www.postgresqltutorial.com/postgresql-upsert/

EDIT: The SO link you provided in fact shows ON CONFLICT there in the top answer.

Thanks @benwilson512. This SO answer indicates that the code we’re using probably came from “the manual”.

Does the INSERT ON CONFLICT syntax still support IF found THEN RETURN; END IF;?

Cool, so at a minimum, you could replace the per-request summation with an update query that happens on an interval. You could also try to track which entries in your other table that you are trying to sum have been handled before or not.

I suppose my basic point here is this: This feels to me more like a database design problem, not a language problem. You are trying to sum rows in a database, the only way to do so and guarantee zero error is to do the summation in the database. The method ya’ll are doing right now is definitely inefficient, and I’m not surprised there are scaling issues. But before you break out a new language to solve your problem, I’d explore other options with the tools you have.

Elixir is awesome, and it can help with a lot or problems. At the end of the day though as long as the problem statement is “I want the values in these rows in table X to represent the totals of rows found in table Y” your database design and interaction pattern is going to matter far, far more than the language.

EDIT: In response to your edit: Try INSERT ON CONFLICT. In general my point about this being a database design issue remains important, particularly if you use those sums to make further database decisions.

Ya, definitely. EDIT: Actually, it might be important to not be delayed. I think we need to prevent future events if the sum hits a limit.

OK. I’m not super familiar with how Rails and stored procedures work so I’m a bit lost on that point. Regardless, I’d still check out on conflict.

To explore this from another angle though, how important is it that these updates are exactly up to date? Can they be delayed by a few dozen seconds?

Probably it’s an ID in the URL of the client. In the rails code, it’s present in the context of wherever this function is being called.

Excellent! So before we do any fancy Elixir things, your current code should really try to use INSERT ON CONFLICT which allows you to try to insert a value and, if one already exists, conditionally update it. Your stored procedure is basically inserting a summation row, but it first checks if one exists, and then adds to it. I feel like the native version is likely to perform much better.

200k over 2 hours is ~30 updates a second, which really ought to be doable in the DB.

I don’t want to discourage you from an Elixir solution but

will work fine until there is netsplit. Phoenix’s distributed tooling works great because it (aside from Presence) handles distributed communication but not distributed state. Distributed state is much, much harder because you have to deal with split brain, things coming apart and coming back together. Presence handles it, and it required some very fancy cutting edge stuff to make it work.

I still think there are solutions that the Elixir version could help with, depending on whether these sums can be slightly out of date or not, but all of those are going to involve choosing when to do a database update, not try to do the sums in Elixir.

Ya, it’s postgres

Ah! I finally get it. Which database are you on? This stored procedure is basically a home grown version of postgres’s INSERT ON CONFLICT so if you’re on postgres you may be able to refactor this to just use that and move on.

Beyond that though, I was referring to EXCEPTION WHEN unique_violation THEN. The whole thing is weird though because if the id is a primary key, indexing on (id, month) shouldn’t actually help because querying on just id should already be indexed. Where does arg_id come from in your stored procedure?