search

erlangbureau / jamdb_oracle

Oracle Database driver for Erlang
MIT License
106 stars 48 forks source link

Do I need a Ecto.Migrator Wrapper? Using autocommit off breaks the schema migrations version control #184

Open Macnolo0x7D4 opened 6 days ago

Macnolo0x7D4 commented 6 days ago

Hi there,

I'm working with an Oracle 21 XE database and aiming to utilize Elixir/Phoenix/Ecto in this project. I'm attempting to implement this driver to connect to the Oracle database, but I'm encountering some difficulties.

The first issue I noticed is that transactions aren't atomic. To address this, I've disabled autocommit and wrapped all CRUD operations within explicit transactions. However, I've encountered another problem when running migrations. The schema_migrations table isn't being updated due to an internal transaction that isn't closed when inserting the new version.

I've tried creating a wrapper for the Ecto migrator that adds a COMMIT query post-migration. Nevertheless, this approach fails when the pool size exceeds one, as different Repo instances might not query the same connection, leading to a mismatch in transaction contexts.

My query is: What's the optimal solution to this problem? Should I permanently disable autocommit or restrict the connection pool to a maximum size of one or if I really need a Ecto.Migrator wrapper?

vstavskyi commented 4 days ago

Oracle does not support transactional DDL. For DML commands, like insert and update schema_migrations, autocommit should be enabled. You can try to roll your DDL back by calling: $ mix ecto.rollback --step 1 Ecto.Migration