Ecto and Postgresql

Created by etori.sangiacomo

SELECT schema, table,
       pg_size_pretty(pg_relation_size(esq_tab)) AS size,
       pg_size_pretty(pg_total_relation_size(esq_tab)) AS total size
  FROM (SELECT tablename AS tabela,
               schemaname AS esquema,
               schemaname||'.'||tablename AS esq_tab
          FROM pg_catalog.pg_tables
         WHERE schemaname NOT
            IN ('pg_catalog', 'information_schema', 'pg_toast') ) AS x
 ORDER BY pg_total_relation_size(esq_tab) DESC;

1. Transactional Support: Ecto.Multi allows grouping multiple database operations into a single transaction. This ensures that either all operations succeed or none of them are persisted, maintaining data consistency.
2. Composition of Operations: You can compose different database operations such as inserts, updates, deletes, and queries in an organized and concise structure.
3. Parallel Execution: Operations within an Ecto.Multi can be executed in parallel, which can significantly improve performance in scenarios where multiple operations need to be performed simultaneously.

alias Ecto.Multi
multi = Multi.new()
|> Multi.insert(:user, User.changeset(%User{}, %{name: "Alice", age: 30}))
|> Multi.insert(:address, Address.changeset(%Address{}, %{street: "123 Main St", city: "Anytown"}))
case Repo.transaction(multi) do
{:ok, %{user: user, address: address}} ->
IO.puts "User and Address created successfully!"
{:error, _failed_op, _changeset, _multi} ->
IO.puts "Failed to create User or Address."
end

• Multi.new() creates a new transactional context.
• Multi.insert(:user, ...) and Multi.insert(:address, ...) are separate insert operations within the transaction.
• Repo.transaction(multi) executes all operations defined in multi within a single transaction. If all operations succeed, it returns {:ok, ...}, otherwise {:error, ...}.

• Atomicity: All operations within an Ecto.Multi are atomic, meaning either all are successfully performed, or none are persisted in the database.
• Flexibility: In addition to Multi.insert/3, there are functions such as Multi.update/4, Multi.delete/3, and Multi.run/4 that allow executing a wide range of operations within a single transaction.
• 
  

ALTER TABLE sanctions_lists
    ADD COLUMN soundex_code varchar
    GENERATED ALWAYS
    AS (
        CASE
            WHEN full_name is NULL THEN soundex('XXXXXXX')
            ELSE soundex(full_name)
        END
    ) STORED