Paging Mnesia Tables - how to get the next part of data?

Questions / Help
1

Hi, I’m new to Elixir and I’m trying to learn more about elixir by creating a relational database in elixir. I’m using the :mnesia-select/4 function to see if I can page my data in elixir from the Mnesia table. I am using the following

 def paging(match_spec, page_size) do
    {_atom, {data, cont}} =
      :mnesia.transaction(fn ->
        :mnesia.select(MyTable, match_spec, page_size, :none)
      end)

    result = Mnesia.Table.Query.decode_paging_data(data)
   result
 end

The problem I am facing is that every time I run the function, I get the same data. What I want to do is every time I run this function, I get the next part of the data.

1 Like
2

Per the :mnesia.select/4 docs, you’ll notice that there is an unused variable you are getting back: cont.

The cont is to be used as an argument to :mnesia.select/1, if more or all answers are needed.

As you are not using this to “continue” your pagination, you are simply getting the first page every time. Notice that :mnesia.select/1 gives you a new cont, and if you pass that in, you get the next “page”—repeat until it returns :"$end_of_table" instead.

A complete solution would need to work together with two functions: one to start the pagination, and another to continue it. An :ets example:

defmodule Pagination do
  @spec start_pagination(
          :ets.table(),
          :ets.match_spec(),
          non_neg_integer()
        ) :: {[tuple()], :ets.select_continuation()} | nil
  @doc """
  Starts paginating objects returned by a `match_spec` applied to a `table`.

  If the table is empty, returns `nil`.
  Otherwise, returns the first page with at most `per_page` objects,
  and a continuation to get the `next_page/1`.
  """
  def start_pagination(table, match_spec, per_page) do
    case :ets.select(table, match_spec, per_page) do
      {page, continuation} ->
        {page, continuation}

      :"$end_of_table" ->
        nil
    end
  end

  @spec next_page(:ets.select_continuation()) ::
          {[tuple()], :ets.select_continuation()} | nil
  @doc """
  Continues paginating objects from a `continuation` returned by `start_pagination/3`.

  If the table is empty, returns `nil`.
  Otherwise, returns the next page with at most `per_page` objects,
  and a continuation to get the `next_page/1`.
  """
  def next_page(continuation) do
    case :ets.select(continuation) do
      {page, continuation} ->
        {page, continuation}

      :"$end_of_table" ->
        nil
    end
  end
end

This is similar to “cursor-based” pagination in SQL land, with many of the same caveats.

1 Like
3

In my example above, I chose to return {page, continuation} or nil here, as they fit nicely into the Stream APIs. This allows you to build higher-level APIs, and hide the continuation as an accumulator in Stream.resource/3, letting you do things like:

defmodule Pagination.Stream do
  @spec pages(
          :ets.table(),
          :ets.match_spec(),
          non_neg_integer()
        ) :: Enumerable.t()
  @doc """
  Produces a lazy `Stream` of (at most) `page_size`-length lists of objects
  returned by a `match_spec` applied to a `table`.
  """
  def pages(table, match_spec, per_page) do
    not_started = make_ref()

    Stream.unfold(
      not_started,
      fn
        ^not_started -> Pagination.start_pagination(table, match_spec, per_page)
        continuation -> Pagination.next_page(continuation)
      end
    )
  end

  @spec objects(
          :ets.table(),
          :ets.match_spec(),
          non_neg_integer()
        ) :: Enumerable.t()
  @doc """
  Produces a lazy `Stream` of objects returned by a `match_spec` applied to a `table`,
  loaded into memory in `page_size` chunks.
  """
  def objects(table, match_spec, per_page) do
    pages(table, match_spec, per_page) |> Stream.flat_map(&Function.identity/1)
  end
end

This lets you do things like

Pagination.Stream.pages(table, match_spec, page_size) |> Enum.take(2)

to get the first two pages, or

Pagination.Stream.objects(table, match_spec, page_size) |> Enum.take(100)

to get the first 100 objects, regardless of page_size, but using the pagination mechanism under the hood to limit the number of objects loaded out of :ets at any point in time.

2 Likes
4

Thank you @christhekeele, I see my error, It seems rather straightforward from here.

1 Like
5

Hello,

This is my function

  def get(_role) do
    match_pattern =
      {MishkaAuth.User.Users, :_, :_, :_, :_, :_, :_, :_, :_, :"$9", :_, :_, :_, :_, :_}

    guard = []
    result_fun = [:"$$"]
    # Number of records to fetch
    limit = 1

    :mnesia.transaction(fn ->
      case :mnesia.select(__MODULE__, [{match_pattern, guard, result_fun}], limit, :read) do
        {matches, cont} when is_list(matches) and matches != [] ->
          # Handle the fetched matches and the continuation for potential future use
          {:ok, matches, cont}

        _ ->
          # Handle the case where no matches are found or an error occurs
          {:error, :no_matches}
      end
    end)
  end

When I call it like this

{:atomic, {:ok, matches, cont}} = MishkaAuth.User.Users.get :role

and put the cont into this

:mnesia.transaction(fn -> :mnesia.select(cont) end)

I got this error

{:aborted, :wrong_transaction}

I just want to show next page and every page show 1 record

Could you help me in this Thanks

Updated:

In a transaction, if I do it, it goes to the next page, but the problem is that the user may want the results and then click again.
So, every time the user requests, I have to create a transaction from the beginning and run it 4 times to reach page 4?
Not a good way to paginate?

for example:

{_record, cont} =
      :mnesia.activity(:async_dirty, fn ->
        :mnesia.select(__MODULE__, [{match_pattern, guard, result_fun}], limit, :read)
      end)

    :mnesia.activity(:async_dirty, fn -> :mnesia.select(cont) end)

if I need to go page 4, I need to do something like this, it is an abstraction

{_record, cont} =
      :mnesia.activity(:async_dirty, fn ->
        :mnesia.select(__MODULE__, [{match_pattern, guard, result_fun}], limit, :read)
      end)

{_record, cont1} = :mnesia.activity(:async_dirty, fn -> :mnesia.select(cont) end)
{_record, cont2} = :mnesia.activity(:async_dirty, fn -> :mnesia.select(cont1) end)
{_record, cont3} = :mnesia.activity(:async_dirty, fn -> :mnesia.select(cont2) end)

It is just useful for Infinite Scroll, not Numerical pagination

6

This isn’t a Mnesia problem, it’s a general problem when paginating with limit + offset.

Asking a SQL database for OFFSET 1000000 LIMIT 10 is going to need to do a lot of work for those ten rows, it’s just not work that has to be written explicitly.

The alternatives are similar as well:

  • rely on a persistent “cursor” in the DB, like select’s continuation. Can be tricky to keep the relevant parts running if clients disconnect, and may hold locks too long.

  • paginate based on attributes of the record. For instance, searching for id > THE_LAST_ID_SEEN when id is an autoincrementing key. This makes page 1000 cost the same as page 1 (compared to the offset method) but only if you’re also accessing pages 1…999 first.

HOWEVER

There is a Mnesia-specific concern here: the “limit” passed to select isn’t strictly enforced. From the docs:

For efficiency, NObjects is a recommendation only and the result can contain anything from an empty list to all available results.

1 Like
7

Thank you.

Would you mind explaining or giving example code about it, please? unfortunately I could not understand

Thank you in advance. I just need some way to have good performance and less resource usage. please give me suggestion

8

When I made my previous post, I was thinking something like this:

defmodule MnesiaCursorDontUse do
  defstruct :tab, :match_pattern, :guard, :result_fun, :limit

  def start(tab, match_pattern, guard, result_fun, limit) do
    spawn(__MODULE__, :run_loop, [%__MODULE__{
      tab: tab,
      match_patten: match_pattern,
      guard: guard,
      result_fun: result_fun,
      limit: limit
    }])
  end

  def run_loop(state) do
    :mnesia.activity(:async_dirty, fn ->
      # do not do this, see discussion below
      loop(state)
    end)
  end

  defp loop(state, cont \\ nil) do
    # seriously do not do this
    receive do
      {:next_page, {pid, ref}} ->
        {result, new_cont} = 
          if cont do
            :mnesia.select(cont)
          else
            :mnesia.select(state.tab, [{state.match_pattern, state.guard, state.result_fun}], state.limit, :read)
          end

        if result == :"$end_of_table" do
          send(pid, {:mnesia_cursor, ref, []})
        else
          send(pid, {:mnesia_cursor, ref, result})

          loop(state, new_cont)
        end
    end
  end
end

Basically, you’d start one of these up and then send it :next_page messages to iterate through the whole Mnesia table.

BUT DO NOT DO THIS

Looking at things harder, select takes a read lock - so any attempts to write to the Mnesia table will block while a MnesiaCursorDontUse is alive. That’s the “may hold locks too long” that I referenced as a downside in my original post.

1 Like