Using Postgrex and SQL for bank transfers in PostgreSQL. epgsql erlang as alternative

I think it’s probably easier if you use Ecto which can create queries that are casted, etc.

Within Ecto, you can create a transaction.

Are you using Phoenix or are you trying to do this standalone? Phoenix has a lot of auxiliary functions that help put things together easily.

I don’t think multistatement queries like the above are supported - see also:

If you’re absolutely 100% required to do it SQL-side, you could make a stored procedure and then call that with Postgrex.query in a single statement.

The more-idiomatic approach would be to use Postgrex.transaction and then do the steps you’ve described using Elixir (and more DB queries) inside that transaction block.

Actually, should be possible to use a DO block (PostgreSQL: Documentation: 18: DO); this is effectively treated as a single SQL statement even though we may be doing more (possibly much more) work than that.

This is not so different than the stored procedure route, except that the DO block is implicitly ephemeral; no need to change the schema.

Thank you for the responses. Great suggestions. I am trying to avoid if possible using stored procedures - to not have code level logic incorporated within the database application - to preserve flexibility if we want to swap out the datastore at a future date. However, the resiliency and reliability of the code is also of the highest priority given the nature of the application. We will try both methods and report back the findings for those interested.

Also, if anybody has a good reference for Postrgrex.transaction examples, please share (any additional documentation would be helpful). Thank you for the suggestion for using Ecto. We will explore this as well - since this is a very simple, single use case scenarrio - where we need high performance at scale, one question we have is since Ecto is a more robust and fulsome package - will the performance be slower since not using many of the features… or is this a false worry? we might want to containerize the service for easy replication - build a network of interconnected nodes - so trying to keep the code base as streamlined and compact as possible.

Ecto maintains a database pool and parses / creates queries. There is some overhead in casting and validating data, but it is unavoidable as you would have to do that in any homegrown solution anyway. Ecto does this via Ecto.ChangeSet.

The major difference would be that there is a cost in converting from an Ecto.Struct to raw sql. This would be on the order of 5-10us and should be heavily outweighed by any networking variance. These benchmarks are from the Ecto Sqlite repo:

• ecto_sqlite3/bench/scripts/micro/to_sql_bench.exs at main · elixir-sqlite/ecto_sqlite3 · GitHub
• ecto_sqlite3/bench/results/to_sql.md at main · elixir-sqlite/ecto_sqlite3 · GitHub

So, I don’t think there should be much of a practical negatives in performance difference in using Ecto over raw, but I have been surprised before.

We have been trying to run the multiple queried within the transaction block using Postgrex.transaction But we can’t get the queries to execute within a single transaction. The function only return the last query executed. Is there more details anywhere on how to build the queries and assign variables within a transaction block using this function? Any assistance much appreciated !

{:ok, res} = Postgrex.transaction(pid3, fn(pid3) ->
  Postgrex.query!(pid3, "SELECT owner_id, balance_avail FROM account", [])
  Postgrex.query!(pid3, "SELECT owner_id, balance_book FROM account", [])
end)

iex(16)> res
%Postgrex.Result{
  command: :select,
  columns: ["owner_id", "balance_book"],
  rows: [
    ["f245cb8bd2df8391281a3606e1841ba4", 7594777],
    ["3d58a9bd15d268b4ccd335d5c1a9fe1b", 9735911],
    ["3f6fbdbdfeffd3a5fe16f6235d3115fd", 6308644],

Hi @gbaird this seems more to do with a general comfort level with how Elixir works. All functions will only return one value, that isn’t related to Postgrex. If you want to return the information from multiple queries, bind each result to a variable and return a tuple:

{:ok, res} = Postgrex.transaction(pid3, fn(pid3) ->
  result1 = Postgrex.query!(pid3, "SELECT owner_id, balance_avail FROM account", [])
  result2 = Postgrex.query!(pid3, "SELECT owner_id, balance_book FROM account", [])
  {result1, result2}
end)

Keep in mind with Postgres that transactions are by default merely READ COMMITTED. Your use case may require stricter transaction modes.