Hello,
we have simple phoenix app. It’s simple eshop. It’s not production code, it’s running only for demo, so don’t care about the logic, security and so on.
We use these packages:
- phoenix 1.3.4
- ecto 2.12
- phoenix_ecto 3.6.0
- postgrex 0.13.5
We have schemas where cart_item has one product and session can have multiple cart_items.
So cart_items are:
defmodule Eshop.Public.CartItem do
use Ecto.Schema
import Ecto.Changeset
alias Eshop.Public.{CartItem, Product}
@primary_key {:id, :binary_id, autogenerate: true}
@foreign_key_type :binary_id
schema "cart_items" do
belongs_to(:product, Product)
field(:products_count, :integer, default: 1)
field(:session_id, :binary_id)
timestamps()
end
@doc false
def changeset(%CartItem{} = cart, attrs) do
cart
|> cast(attrs, [:session_id, :products_count, :product_id])
|> validate_required([:session_id, :products_count, :product_id])
end
end
Products:
defmodule Eshop.Public.Product do
use Ecto.Schema
import Ecto.Changeset
alias Eshop.Public.Product
@primary_key {:id, :binary_id, autogenerate: true}
@foreign_key_type :binary_id
schema "products" do
field(:description, :string)
field(:name, :string)
field(:price, :integer)
field(:slug, :string)
timestamps()
end
@doc false
def changeset(%Product{} = product, attrs) do
product
|> cast(attrs, [:name, :description, :price, :slug])
|> validate_required([:name, :description, :price, :slug])
end
end
Repo:
defmodule Eshop.Repo do
use Ecto.Repo, otp_app: :eshop
@doc """
Dynamically loads the repository url from the
DATABASE_URL environment variable.
"""
def init(_, opts) do
{:ok, Keyword.put(opts, :url, System.get_env("DATABASE_URL"))}
end
end
When someone add something to his cart we redirect him to cart page where we show all cart items for current session. Nothing special. We call this function:
Public.list_cart_items(session_id)
This function returns list of cart items.
def list_cart_items(session_id) do
query = from(ci in CartItem, where: ci.session_id == ^session_id)
Repo.all(query)
|> Repo.preload(:product)
end
All works but when someone open cart page it creates long running sql queries, but really long(couple of, until we restart app). It shows all data, all queries finished. But when I look into postgres database I can see these long running queries in pg_stat_activity table. I see these long running queries/requests:
SELECT p0."id", p0."description", p0."name", p0."price", p0."slug", p0."inserted_at", p0."updated_at", p0."id" FROM "products" AS p0 WHERE (p0."id" = ANY($1))
SELECT c0."id", c0."product_id", c0."products_count", c0."session_id", c0."inserted_at", c0."updated_at" FROM "cart_items" AS c0 WHERE (c0."session_id" = $1)
ROLLBACK TO SAVEPOINT postgrex_query
Any idea where can be problem?
