Ash Framework book - chapter 3 - Postgrex.Error when adding aggregates

Ash Forum Ash Questions / Help
1

I cloned GitHub - sevenseacat/tunez: The starter application for the Ash Framework book and I started to modify the repo as I worked my way through the chapters.

I got the following error once I got to chapter 3 and add the aggregates to my code:

Bread Crumbs:
  > Error returned from: Tunez.Music.Artist.search

Unknown Error

* ** (Postgrex.Error) ERROR 42883 (undefined_function) function any_value(bigint) does not exist

    query: SELECT a0."id", a0."name", a0."inserted_at", a0."biography", a0."previous_names", a0."updated_at", coalesce(s1."album_count"::bigint, $1::bigint)::bigint, s1."latest_album_year_released"::bigint::bigint, s1."cover_image_url"::text::text FROM "artists" AS a0 LEFT OUTER JOIN LATERAL (SELECT sa0."artist_id" AS "artist_id", coalesce(count(*) FILTER (WHERE $2), $3::bigint)::bigint AS "album_count", any_value(sa0."year_released"::bigint ORDER BY  sa0."year_released" DESC ) FILTER (WHERE $4 AND NOT (sa0."year_released"::bigint IS NULL))::bigint AS "latest_album_year_released", any_value(sa0."cover_image_url"::text ORDER BY  sa0."year_released" DESC ) FILTER (WHERE $5 AND NOT (sa0."cover_image_url"::text IS NULL))::text AS "cover_image_url" FROM "public"."albums" AS sa0 WHERE (a0."id" = sa0."artist_id") GROUP BY sa0."artist_id") AS s1 ON TRUE WHERE (a0."name"::text ILIKE $6) ORDER BY a0."updated_at" DESC, a0."id" LIMIT $7

    hint: No function matches the given name and argument types. You might need to add explicit type casts.
  (ecto_sql 3.12.1) lib/ecto/adapters/sql.ex:1096: Ecto.Adapters.SQL.raise_sql_call_error/1
  (ecto_sql 3.12.1) lib/ecto/adapters/sql.ex:994: Ecto.Adapters.SQL.execute/6
  (ecto 3.12.5) lib/ecto/repo/queryable.ex:232: Ecto.Repo.Queryable.execute/4
  (ecto 3.12.5) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3
  (ash_postgres 2.5.19) lib/data_layer.ex:786: anonymous fn/3 in AshPostgres.DataLayer.run_query/2
  (ash_postgres 2.5.19) lib/data_layer.ex:785: AshPostgres.DataLayer.run_query/2
  (ash 3.5.9) lib/ash/actions/read/read.ex:3481: Ash.Actions.Read.run_query/4
  (ash 3.5.9) lib/ash/actions/read/read.ex:629: anonymous fn/8 in Ash.Actions.Read.do_read/5
  (ash 3.5.9) lib/ash/actions/read/read.ex:1058: Ash.Actions.Read.maybe_in_transaction/3
  (ash 3.5.9) lib/ash/actions/read/read.ex:326: Ash.Actions.Read.do_run/3
  (ash 3.5.9) lib/ash/actions/read/read.ex:89: anonymous fn/3 in Ash.Actions.Read.run/3
  (ash 3.5.9) lib/ash/actions/read/read.ex:88: Ash.Actions.Read.run/3
  (ash 3.5.9) lib/ash.ex:2048: Ash.read/2
  (ash 3.5.9) lib/ash.ex:2006: Ash.read!/2
  (tunez 0.1.0) lib/tunez_web/live/artists/index_live.ex:20: TunezWeb.Artists.IndexLive.handle_params/3
  (phoenix_live_view 1.0.11) lib/phoenix_live_view/utils.ex:456: anonymous fn/5 in Phoenix.LiveView.Utils.call_handle_params!/5
  (telemetry 1.3.0) /Users/ggarciajr/Projects/tunez/deps/telemetry/src/telemetry.erl:324: :telemetry.span/3
  (phoenix_live_view 1.0.11) lib/phoenix_live_view/static.ex:322: Phoenix.LiveView.Static.call_mount_and_handle_params!/5
  (phoenix_live_view 1.0.11) lib/phoenix_live_view/static.ex:155: Phoenix.LiveView.Static.do_render/4
  (phoenix_live_view 1.0.11) lib/phoenix_live_view/controller.ex:39: Phoenix.LiveView.Controller.live_render/3

The error disappeared and everything worked after I checked out the end-of-chapter-3 branch. I thought the error could have been caused by a typo or some silly mistake, so I compared my local repo, with the changes I made along the way, with the code from the end-of-chapter-3 branch.

I didn’t find anything obvious other than just code in different order, so, I used a diff/merge tool to make sure my code looked exact the same as the end-of-chapter-3 branch.

I appreciate any help, as I have no idea what may be wrong.

I’m running with:
elixir --version
Erlang/OTP 27 [erts-15.2.6] [source] [64-bit] [smp:16:16] [ds:16:16:10] [async-threads:1] [jit]
Elixir 1.18.3 (compiled with Erlang/OTP 27)
Postgres 15.4

2

Take a look at your repo.ex file. In that file is a function expressing your minimum expected Postgres version :slight_smile: you’ll want to set it to the version you are using.

4

Thank you @zachdaniel. There was indeed a mismatch between what the project wanted - version 16 - and what I was running - version 15.

1 Like
5

Oh I’ll update that in the repo - we say the minimum requirements are PostgreSQL 15, so we should use it to make sure :sweat_smile:

6

This page is now one of the top results for “ash any_value”, so I’ll leave this here:

If you came here from a search engine, read this:

If you are using Postgres version 15 or lower, you may be seeing this error because your Ash project is expecting Postgres version 16 or higher.

To fix the error, you either need to upgrade to Postgres version 16 (or greater), or if you want to keep using Postgres version 15 (or lower), you can modify lib/[your_project]/repo.ex to use a version of Postgres that is lower than 16. For example:

  @impl true
  def min_pg_version do
    %Version{major: 15, minor: 0, patch: 0}
  end

This function should already be in your Repo module if you are using Ash. Just make sure the version is 15 or lower (if you want to keep using Postgres <=15), then restart your application, and the error should disappear. :slight_smile:

Original message: I just ran into this the other day. I don’t think it’s something you would be able to fix @sevenseacat since IIRC it happens when the user runs the Igniter setup task for ash_postgres:

You’ll also see a notice from AshPostgres. It has inferred the version of Post-
greSQL you’re running, and configured that in Tunez.Repo.min_pg_version/0

Actually, I guess the repo.ex file in the Tunez end-of-chapter branches should have the lesser version, so that anyone who jumps in at a random chapter can use a branch that doesn’t break mysteriously… (Actually, this could be a rabbit hole unto itself… Will the lower version work for PG v16 users?)

I encountered this issue since I use Docker for Postgres (which is configured to use postgres:15 to ensure that my local Postgres is on the same version with a specific remote instance). But I also have Postgres client utilties installed through apt-get, so I think the Igniter task inferred that I am on version 16 since that’s what pg_config returns (not sure if that’s how AshPostgres figures out my PG version, but that’s what made sense to me).

I was wondering if some sort of pull request might be in order… the "any_value" string is determined here, but I never tracked down where a better error message might actually be raised. It is one hell of a cryptic error message, though.

7

Sure, I can’t fix it in people’s own versions of Tunez, but I can fix it in the built-out version of the app on GitHub. The lower version will work correctly for PostgreSQL 16+ users, but the SQL it generates is a little less efficient.

AshPostgres works out the current version of PostgreSQL using postgres -V, so it might not work well with Docker containers? We do show a notice to double-check the version after install as well.

8

There unfortunately isn’t really any way for us to show a better message I don’t think. The whole point of the config is that its the only way we know whether or not any_value is valid SQL, and the only way to know otherwise is to send it in a query.

1 Like