Error Creating Transfer After Upgrading from Version 0.2.4 to 1.0.6 – PostgreSQL ambiguous_function Operator Issue

[zeadhani]

Issue Summary

When creating a transfer after upgrading from version 0.2.4 to 1.0.6, an error occurs during the operation. The PostgreSQL error ambiguous_function appears, indicating that an operator is not unique, with the hint suggesting the need for explicit type casts.

Steps to Reproduce

1. Upgrade ash_double_entry from version 0.2.4 to 1.0.6.
2. Run the transfer creation process using the following context: -> Transfer involves inserting into or updating the balances table.
3. Encounter an error during the operation with the following debug logs:

[debug] QUERY ERROR source="balances" db=0.0ms UPDATE "balances" AS b0 SET "balance" = (CASE ... ) WHERE ... ** (Postgrex.Error) operator is not unique: - unknown Hint: Could not choose a best candidate operator. You might need to add explicit type casts.

Observed Behavior

The transfer creation fails with an ambiguous_function error in PostgreSQL.

Expected Behavior

The transfer should be created without any errors, and the balances table should update correctly.

Additional Details

• Elixir Version: 1.17.3
• Ash Framework Version: 3.0
• Postgrex.Error: Code 42725

Troubleshooting Done

• Verified that no duplicate identifiers exist in accounts.
• Reviewed data types and confirmed they align with what is expected in the balances table.

[zachdaniel]

What specific ash, ash_postgres and ash_sql versions are you using?

[zachdaniel]

Also ash_money

[zachdaniel]

Please make sure you're on the latest of all of them.

[zeadhani]

I am using the following versions, all of which are updated:

•   ash_postgres: ~> 2.4.12
•   ash_sql: ~> 0.2.38
•   ash_double_entry: ~> 1.0.6
•   ash_money: ~> 0.1.13
•   ex_money_sql: ~> 1.11

[zachdaniel]

I've made a small change in main that may help, but if not I will have to look into this next week. Please try it out and let me know.