I am trying to code a bank transfer service using Elixir/Erlang, Postgrex and PostgreSQL.
Erlang v 26
Elixir v 1.16.0
Postgrex v 0.17.4
The logic I am trying to build is the following, wrapped in a transaction BEGIN - COMMIT.
Party A seeks to transfer $50 from his account to Party B, her account. Think a Venmo transfer from Party A to Party B for $50.
Initialize
Store the transfer amount in an Elixir variable *transfer_amount
Store the from account UID in an Elixir variable from_account
Store the to account UID in an Elixir Variable to_account
Store the authorization token in an Elixir variable auth_token
BEGIN the transaction
Read the master token value from the authorization data table and if the auth_token = master_token, then continue, else quit
Read the balance from Party A’s account using the from_account UID
If from_account** balance is >= than transfer_amount, then continue, else quit
Update from_account balance by subtracting transfer_amount
Update to_account balance by adding transfer_amount to balance
COMMIT the transaction
What I tried is wrapping the SQL statement built from the above into a query and then use Postgrex.query - see below. The code returns an error.
My question for the group is this the correct/best design pattern to try and accomplish the objective - a P2P bank transfer, to wrap the SQL logic into a SQL statement that has a series of “reads” and then a “read / write” using Postgrex.query! Does Postgres support “Transaction” syntax? Can the read, write, comparisons all be included into a single statement and then sent using Postgrex.query?
24)> Postgrex.query!(pid3, "BEGIN; SELECT token_masterid FROM token; IF token_id = master_token THEN SELECT from_account, balance_avail FROM main_account … [note not……; COMMIT", [])
** (Postgrex.Error) ERROR 42601
A more simple version produces this error
iex(14)> Postgrex.query!(pid3, "BEGIN transaction; SELECT * FROM account; COMMIT;", [])
** (Postgrex.Error) ERROR 42601 (syntax_error) cannot insert multiple commands into a prepared statement
iex(14)> Postgrex.query!(pid3, "BEGIN transaction; SELECT * FROM account; COMMIT;", [])
** (Postgrex.Error) ERROR 42601 (syntax_error) cannot insert multiple commands into a prepared statement
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: 16: 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:
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 !
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:
