AshPostgres

Manual relationships can leverage Ecto's full power, such as using Recursive Common Table Expressions (CTEs) to handle hierarchical data (e.g., finding all employees under a manager).

Note: For hierarchical data in Postgres, it is highly recommended to use AshPostgres.Ltree instead of manual recursive CTEs if possible, as it is built-in and optimized.

When implementing a recursive manual relationship with AshPostgres:

1. load/3: Use recursive_cte_query to fetch all descendants in a single query, then group them by the parent ID to return the required map structure.
2. ash_postgres_join/6: Use inner_lateral_join or left_lateral_join combined with a subquery containing the recursive CTE to allow the relationship to be joined efficiently.
3. ash_postgres_subquery/4: Construct a subquery that uses the recursive CTE to filter the destination records based on the current_binding (the parent).

When configuring foreign keys, you can choose between :nothing and :restrict:

• :nothing (Default): Allows the foreign-key check to be deferred until later in the transaction. This is useful when you need to update/delete the destination row first and then the reference within the same transaction.
• :restrict: Performs an immediate check of the foreign-key constraint, preventing the update or deletion from proceeding if it violates integrity.

Both options ultimately prevent invalid data due to Postgres's enforcement of referential integrity, but :nothing provides more flexibility during complex transactions.

# Use :nothing if you need to perform multi-step updates in a transaction
references do
  reference :post, on_delete: :nothing
end

# Use :restrict to prevent any modification that violates the constraint immediately
references do
  reference :post, on_delete: :restrict
end

The repo function in an Ash Resource is called multiple times during a single query execution. If the function returns a different repository on subsequent calls (e.g., by using Enum.random/1 directly), Ash will be unable to perform joins or lateral joins, resulting in an Ash.Error.Query.InvalidExpression.

To ensure stability, the function should use the process dictionary to memoize the chosen replica for the duration of a single unit of work. This ensures that repo(resource, :read) returns the same repository for all resources involved in a single query.

Best Practices:

• Memoization: Use Process.put/2 and Process.get/1 to lock in a replica for the current process.
• Consistency: Ensure the function returns the same repository for all resources in a query to allow joins.
• Clearing State: In long-lived processes like Phoenix.LiveView or GenServer, clear the memoized replica at the start of a unit of work (e.g., at the start of a callback) using Process.delete(:replica) so the process can pick a new replica for the next request. Never clear it while a query is actively being built.

# Example of a stable replica selector
defmodule MyApp.Repo do
  use Ecto.Repo,
    otp_app: :my_app,
    adapter: Ecto.Adapters.Postgres

  @replicas [
    MyApp.Repo.Replica1,
    MyApp.Repo.Replica2
  ]

  def replica do
    case Process.get(:replica) do
      nil ->
        replica = Enum.random(@replicas)
        Process.put(:replica, replica)
        replica

      replica ->
        replica
    end
  end
end

# To reset the replica choice for a new unit of work:
Process.delete(:replica)

Upserts in AshPostgres use PostgreSQL's INSERT ... ON CONFLICT mechanism, which is designed to arbitrate concurrent inserts of the same key.

Concurrency Behavior:

• READ COMMITTED (Default): If two transactions attempt to upsert the same identity simultaneously, the second transaction will wait for the first to commit. Once the first commits, the second transaction will proceed via the DO UPDATE branch. Both succeed, and the row reflects the last writer's values.
• REPEATABLE READ / SERIALIZABLE: Under these higher isolation levels, PostgreSQL may raise a serialization failure instead of waiting, treating it as a standard concurrent write conflict.

Note on PostgreSQL 17+: AshPostgres continues to use INSERT ... ON CONFLICT rather than the newer MERGE statement. This is because MERGE evaluates conditions against a snapshot and does not wait for concurrent inserts, which can lead to unique constraint violations when two transactions both attempt the WHEN NOT MATCHED branch for the same key.

Fragments allow you to use arbitrary Postgres expressions in your queries when Ash's built-in expressions are insufficient. You can use fragments in calculations, migrations, and filters.

Important: Escaping Question Marks In fragments, ? is a placeholder for an interpolated argument. If your SQL requires a literal ? (such as the JSONB key-exists operator ? or jsonb_path_exists filters), you must escape it as \?. Failure to do so will result in the error: fragment(...) expects extra arguments in the same amount of question marks in string.

# Simple expression
fragment("? / ?", points, count)

# Calling a function
fragment("repeat('hello', 4)")

# Using a subquery (last resort)
fragment("points > (SELECT SUM(points) FROM games WHERE user_id = ? AND id != ?)", user_id, id)

# In a calculation
calculations do
  calculate :lower_name, :string, expr(
    fragment("LOWER(?)", name)
  )
end

# In a migration
create table(:managers, primary_key: false) do
  add :id, :uuid, null: false, default: fragment("UUID_GENERATE_V4()"), primary_key: true
end