# Disable query cache?

**URL:** <https://forum.elixirforum.com/t/disable-query-cache/927>\
**Category:** Questions\
**Tags:** ecto\
**Created:** [June 24, 2016, 2:55pm UTC](https://forum.elixirforum.com/t/disable-query-cache/927 "2016-06-24T14:55:43Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![cjbottaro](https://forum.elixirforum.com/user_avatar/forum.elixirforum.com/cjbottaro/32/1469_2.png) [@cjbottaro](https://forum.elixirforum.com/u/cjbottaro)\
**Post date:** [June 24, 2016, 2:55pm UTC](https://forum.elixirforum.com/t/disable-query-cache/927/1 "2016-06-24T14:55:44Z")

</div>

Hello,

I’m building a custom database connection pool with some funky requirements (see [here](https://forum.elixirforum.com/t/info-on-implementing-custom-ecto-pool/854/1)). The end result is that a single repo’s pool will be connecting to _different_ databases. This make the query caching unhappy:

> \*\* (ArgumentError) query %Postgrex.Query{columns: [“id”, “name”], copy\_data: false, decoders: [#function\<6.39073818/1 in Postgrex.Types.decoder/2\>, #function\<6.39073818/1 in Postgrex.Types.decoder/2\>], encoders: [#function\<7.39073818/1 in Postgrex.Types.encoder/2\>], name: “”, null: nil, param\_formats: [:binary], result\_formats: [:binary, :binary], statement: “SELECT c0."id", c0."name" FROM "clients" AS c0 WHERE (c0."id" = $1)”, types: 110635} has invalid types for the connection

My question is how to turn off query caching? Or more low level, how to make Postgrex not check the `types` field in the query and connection structs?

Thanks for the help.

---

<div class="post-metadata">

**Author:** ![benwilson512](https://forum.elixirforum.com/user_avatar/forum.elixirforum.com/benwilson512/32/1457_2.png) [@benwilson512](https://forum.elixirforum.com/u/benwilson512)\
**Post date:** [June 24, 2016, 3:57pm UTC](https://forum.elixirforum.com/t/disable-query-cache/927/2 "2016-06-24T15:57:27Z")

</div>

I feel like the normal Ecto approach here is to have one Repo module per database. What about that approach won’t work here?

---

<div class="post-metadata">

**Author:** ![cjbottaro](https://forum.elixirforum.com/user_avatar/forum.elixirforum.com/cjbottaro/32/1469_2.png) [@cjbottaro](https://forum.elixirforum.com/u/cjbottaro)\
**Post date:** [June 24, 2016, 5:03pm UTC](https://forum.elixirforum.com/t/disable-query-cache/927/3 "2016-06-24T17:03:25Z")

</div>

500+ databases and they are created dynamically. Give them each a pool of 20 connections and then combine that with we have over 100 web/worker servers, that’s too many connections for pgBouncer.

So given that, I want to try the idea of an LRU connection cache (that implements the DBConnection.Pool behavior so it can be used with Ecto).

And it generally works, except for the aforementioned issue. Fishcakez offered a workaround, which works (but needs a code review) and I’ll post about in a little bit.

---

<div class="post-metadata">

**Author:** ![benwilson512](https://forum.elixirforum.com/user_avatar/forum.elixirforum.com/benwilson512/32/1457_2.png) [@benwilson512](https://forum.elixirforum.com/u/benwilson512)\
**Post date:** [June 24, 2016, 5:36pm UTC](https://forum.elixirforum.com/t/disable-query-cache/927/4 "2016-06-24T17:36:01Z")

</div>

Interesting, would love to see what the solution turns out to be.

---

<div class="post-metadata">

**Author:** ![cjbottaro](https://forum.elixirforum.com/user_avatar/forum.elixirforum.com/cjbottaro/32/1469_2.png) [@cjbottaro](https://forum.elixirforum.com/u/cjbottaro)\
**Post date:** [June 24, 2016, 7:09pm UTC](https://forum.elixirforum.com/t/disable-query-cache/927/5 "2016-06-24T19:09:37Z")

</div>

Here’s the solution (and a description of the problem)…

So the first time a query is run Ecto prepares the query which stores type information in the query struct, then subsequently when that query is executed, it uses that cached type info and can execute directly instead of preparing first.

Here’s the relevant code in `Ecto.Adapters.Postgres.Connection`:

```elixir
def prepare_execute(conn, name, sql, params, opts) do
  query = %Postgrex.Query{name: name, statement: sql}
  DBConnection.prepare_execute(conn, query, params, opts)
end

def execute(conn, sql, params, opts) when is_binary(sql) do
  query = %Postgrex.Query{name: "", statement: sql}
  case DBConnection.prepare_execute(conn, query, params, opts) do
    {:ok, _, query} -> {:ok, query}
    {:error, _} = err -> err
  end
end

def execute(conn, %{} = query, params, opts) do
  DBConnection.execute(conn, query, params, opts)
end

```

So the solution is to change those functions to simply discard the cached query info in the call to `prepare_execute`, then modify the other `execute` functions to always call `prepare_execute`:

```elixir
def prepare_execute(conn, name, sql, params, opts) do
  query = %Postgrex.Query{name: "", statement: sql}
  { :ok, result } = execute(conn, query, params, opts)
  { :ok, query, result }
end

def execute(conn, sql, params, opts) when is_binary(sql) do
  query = %Postgrex.Query{name: "", statement: sql}
  execute(conn, query, params, opts)
end

def execute(conn, %{} = query, params, opts) do
  { :ok, _query, result } = DBConnection.prepare_execute(conn, query, params, opts)
  { :ok, result }
end

```

Notice that `prepare_execute` returns the initial, almost bare `%Postgrex.Query{}` struct without any cached query info.

Obviously the downside to this is that every query now has to hit the db twice since we’re no longer saving any of the info returned by `prepare_execute`.

Huge thanks to @fishcakez to helping me with this; saving me many hours of trying to figure it out on my own.

There is still one last problem. Those modified functions live in `Ecto.Adapters.Postgres.Connection`, so it’s not just a matter of making a new `Ecto.Adapters.PostgresWithoutCache` adapter; I have to make both a new adapter module _and_ connection module for that adapter. And I’m not sure how to do that cleanly in a functional language. In an OOP language, I could simply inherit new classes and overwrite those specific methods… but how do something similar in Elixir?

I want to make a new Adapter module and new Connection module that are _exactly_ the same except for those three methods.

Thanks for the help.

---

<div class="post-metadata">

**Author:** ![benwilson512](https://forum.elixirforum.com/user_avatar/forum.elixirforum.com/benwilson512/32/1457_2.png) [@benwilson512](https://forum.elixirforum.com/u/benwilson512)\
**Post date:** [June 24, 2016, 7:20pm UTC](https://forum.elixirforum.com/t/disable-query-cache/927/6 "2016-06-24T19:20:59Z")

</div>

Is there no way to cache based on the database?