Postgrex.Extension no function clause matching error in (Custom.PostgrexTypes)

Hi elixir fam! :wave:

I’m running into some interesting errors with trying to implement a custom Postgrex Type Extension and I’m really hoping folks can help shed some light on it.

I’ve been trying really hard to add native support for ULID style UUIDs in my elixir application as it would be perfect for my team’s use case. I’ve made really great progress in about a week, but have been struggling to overcome a really pernicious issue for a few days now.

The particular postgresql extension I’m trying to make work is this one:
[ github: andrielfn / pg-ulid ]

Which adds native ULID support to postgresql. It’s fast, efficient, and it even adds a unique ulid field type which is great! It’s an awesome extension and the author himself is big into Elixir!


The issue that I’m running into seems to stem from some kind of incompatibility or bug within Ecto or Postgrex for my particular use case. I’m trying to use the :ulid field type added by the extention as the primary_key identifier on all of my tables.

I’m not an expert in this area of Ecto/Postgrex, but I’ve managed to get almost full compatibility. I’m able to perform all of the basic CRUD functions so far. all while using ULIDs as the primary key, but certain kinds of actions just don’t work somehow.

For example Repo.reload/1 and a number internal tests handlers are broken and I can’t seem to figure out the root cause.

iex(1)> UlidTest.Accounts.create_user(%{name: "Hello world!"})
%UlidTest.Accounts.User{
  __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
  id: "01JBCJYKBPXEMPD56NE290SETW",
  name: "Hello world!",
  inserted_at: ~N[2024-10-29 16:50:08],
  updated_at: ~N[2024-10-29 16:50:08]
}

iex(2)> UlidTest.Accounts.get_user("01JBCJYKBPXEMPD56NE290SETW")
%UlidTest.Accounts.User{
  __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
  id: "01JBCJYKBPXEMPD56NE290SETW",
  name: "Hello world!",
  inserted_at: ~N[2024-10-29 16:50:08],
  updated_at: ~N[2024-10-29 16:50:08]
}

iex(3)> UlidTest.Accounts.get_user("01JBCJYKBPXEMPD56NE290SETW") |> UlidTest.Repo.reload()

** (FunctionClauseError) no function clause matching in UlidTest.PostgrexTypes."-inlined-Elixir.ULID.Extension/1-"/1

    The following arguments were given to UlidTest.PostgrexTypes."-inlined-Elixir.ULID.Extension/1-"/1:

        # 1
        ["01JBCJYKBPXEMPD56NE290SETW"]

    (ulid_test 0.1.0) UlidTest.PostgrexTypes."-inlined-Elixir.ULID.Extension/1-"/1
    (postgrex 0.19.2) lib/postgrex/query.ex:75: DBConnection.Query.Postgrex.Query.encode/3
    (db_connection 2.7.0) lib/db_connection.ex:1449: DBConnection.encode/5
    (db_connection 2.7.0) lib/db_connection.ex:1549: DBConnection.run_prepare_execute/5
    (db_connection 2.7.0) lib/db_connection.ex:1653: DBConnection.run/6
    (db_connection 2.7.0) lib/db_connection.ex:772: DBConnection.parsed_prepare_execute/5
    (db_connection 2.7.0) lib/db_connection.ex:764: DBConnection.prepare_execute/4
    (ecto_sql 3.12.1) lib/ecto/adapters/postgres/connection.ex:104: Ecto.Adapters.Postgres.Connection.prepare_execute/5
    (ecto_sql 3.12.1) lib/ecto/adapters/sql.ex:1002: Ecto.Adapters.SQL.execute!/5
    (ecto_sql 3.12.1) lib/ecto/adapters/sql.ex:994: Ecto.Adapters.SQL.execute/6
    (ecto 3.12.4) lib/ecto/repo/queryable.ex:232: Ecto.Repo.Queryable.execute/4
    (ecto 3.12.4) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3
    (ecto 3.12.4) lib/ecto/repo/queryable.ex:154: Ecto.Repo.Queryable.one/3
    iex:4: (file)

I'm really not sure what to make of this error, and I've spent several days throwing everything I have at it. It seems like Postgrex is trying to pass the ULID value toward the ULID.Extension contained within the Postgrex.Types definition, but failing to route it properly...

I’m also confused as to why it’s happening in that particular use-case because like was said before, all of the CRUD functions are working just fine, so why wouldn’t those functions exhibit the same issue?

I have put together a barebones sandbox app that showcases the problem that I’m encountering which can be found here:

[Ecto ULID Sandbox]

Simply run the commands listed in the above example to make it happen.
(There is also some additional info in the README.md)

Some notable files changed from the phx.new app:

  • lib/ulid_test/config/config.exs (settings to change primary_key → :ulid)
  • lib/ulid_test/postgrex/types.ex (Custom Postgrex.Types.define/3)
  • lib/ulid_test/postgrex/ulid.ex (ULID Postgrex.Extension)
  • lib/ulid_test/ulid.ex (Custom Ecto.Type module)

Some additional information:

  • elixir - 1.17.3-otp-27
  • erlang - 27.1.2
  • ecto - 3.12.4
  • postgrex - 0.19.2
  • apple M2 macbook (hardware)

If anyone more knowledgeable than myself can help me get unstuck on this I would be hugely grateful. Thank you guys in advance!

1 Like

So in trying to explore this issue more, I put together another branch on the [ Ecto ULID Sandbox ] repo that showcases the same issue being present when trying to use a similar Postgrex Type Extension as the primary_key.

The other extension is the [ Cube Ecto ] extension that adds support for cube data type through postgres.

branch_name: ecto_cube_primary_id

The branch showcases that using the cube field as the primary_key identifier (an absurd scenario, of course; that data type is not intended for that use case), but it showcases the issue happening there too. Currently my best guess would be that having a custom Postgrex.Ext being the primary key is likely causing this issue somehow.

1 Like

Alright, so my understanding of the root issue has changed quite a bit today.

This error message was very confusing to me because I’m not super familiar with the meta-programming concepts in Elixir. No function clause matching seems straightforward enough, but just I couldn’t wrap my head around the function that was being refferenced.

** (FunctionClauseError) no function clause matching in UlidTest.PostgrexTypes."-inlined-Elixir.ULID.Extension/1-"/1

    The following arguments were given to UlidTest.PostgrexTypes."-inlined-Elixir.ULID.Extension/1-"/1:

        # 1
        ["01JBCJYKBPXEMPD56NE290SETW"]

I ended up delving back into the @docs to explore and found this section in the Postgrex.Types module where it mentions “inlining” the Extensions encode/decode on compile. (alternatively h Postgrex.Types.define)

The "-inlined-Elixir.ULID.Extension/1-"/1 part was totally fine, and I finally understood that my ULID.Extension was the problem due to not having an encode/1 function that supported the incoming parameter.

Thinking about the code a bit more, I finally figured that the brackets around the ULID bit-string was likely the culprit for that particular error.

Here’s the encode function as it was before:

lib/ulid_test/postgrex/ulid.ex
 
 @impl true
  def encode(_) do
    quote do
      bin when is_binary(bin) ->
        [<<byte_size(bin)::signed-size(32)>> | bin]
    end
  end


And here is after some changes to better support the surrounding brackets:
lib/ulid_test/postgrex/ulid.ex
 
  @impl true
  def encode(_) do
    quote do
      ulid when is_binary(ulid) and byte_size(ulid) == 26 ->
        [<<byte_size(ulid)::signed-size(32)>> | ulid]

      [inner] = list when is_list(list) and is_binary(inner) and byte_size(inner) == 26 ->
        [ulid] = list
        [<<byte_size(ulid)::signed-size(32)>> | ulid]

      other ->
        raise DBConnection.EncodeError, Postgrex.Utils.encode_msg(other, "a string of 26 bytes")
    end
  end


This change seemed to get me past the troublesome error! Good news! However, I was promptly staring at yet another error. **sighhhh**...
UlidTest.Accounts.get_user("01JBFX6J7TDTQEC1VH959N3D5A") |> UlidTest.Repo.reload()

[debug] QUERY OK source="users" db=6.5ms decode=2.3ms queue=1.8ms idle=1213.8ms
SELECT u0."id", u0."name", u0."inserted_at", u0."updated_at" FROM "users" AS u0 WHERE (u0."id" = $1) ["01JBFX6J7TDTQEC1VH959N3D5A"]

[debug] QUERY ERROR source="users" db=13.5ms queue=1.9ms idle=1646.1ms
SELECT u0."id", u0."name", u0."inserted_at", u0."updated_at" FROM "users" AS u0 WHERE (u0."id" = ANY($1)) [["01JBFX6J7TDTQEC1VH959N3D5A"]]

** (Postgrex.Error) ERROR 22P02 (invalid_text_representation) malformed array literal: "01JBFX6J7TDTQEC1VH959N3D5A". If you are trying to query a JSON field, the parameter may need to be interpolated. Instead of

    p.json["field"] != "value"

do

    p.json["field"] != ^"value"


Array value must start with "{" or dimension information.
    (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.4) lib/ecto/repo/queryable.ex:233: Ecto.Repo.Queryable.execute/4
    (ecto 3.12.4) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3
    (ecto 3.12.4) lib/ecto/repo/queryable.ex:155: Ecto.Repo.Queryable.one/3
    (elixir 1.15.7) src/elixir.erl:396: :elixir.eval_external_handler/3

I found it strange that none of the other Postgrex.Type extensions that I have come across needed to have a safeguard of that nature within their Extension's `encode/1` definitions.

Not sure what to make of this new error message…

1 Like

OK, got some more time to spend on this and figured out might have got this completely working as I was wanted it. :raised_hands:

The new error is not a Postgrex error, per-se. It’s a PostgreSQL error being returned from the database when trying to accomplish this particular query structure.

Failing Query (PSQL)

ulid_test_dev=# SELECT u0."id", u0."name", u0."inserted_at", u0."updated_at" FROM "users" AS u0 WHERE (u0."id" = ANY('01JBFX6J7TDTQEC1VH959N3D5A'));
ERROR:  malformed array literal: "01JBFX6J7TDTQEC1VH959N3D5A"
LINE 1: ...dated_at" FROM "users" AS u0 WHERE (u0."id" = ANY('01JBFX6J7...
                                                             ^
DETAIL:  Array value must start with "{" or dimension information.

Turns out the Repo.reload/1 function can take in multiple structs (which I wasn’t aware of) and thus passes a list of primary_key identifiers to the database in a lookup call.

The issue was that the PSQL ANY( ) clause was not receiving the list in the format that it needed to work. e.g. SELECT * FROM users WHERE id = ANY ('{1, 2, 3}');

Working Query (PSQL)

ulid_test_dev=# SELECT u0."id", u0."name", u0."inserted_at", u0."updated_at" FROM "users" AS u0 WHERE (u0."id" = ANY('{01JBFX6J7TDTQEC1VH959N3D5A}'));
             id             |     name     |     inserted_at     |     updated_at
----------------------------+--------------+---------------------+---------------------
 01JBFX6J7TDTQEC1VH959N3D5A | Hello world! | 2024-10-30 23:46:58 | 2024-10-30 23:46:58
(1 row)

In the end, this was happening due to a gap in the Postgrex logic around TEXT formatted Postgrex.Extention based values being used as primary key identifiers.

I was able to totally resolve these issues by just applying the necessary formatting within the ULID.Extension.encode/1 function.

If someone finds this thread via search engine. You can checkout the sandbox repo to see issues resolved, and full compatibility with [ github: andrielfn / pg-ulid ] and Ecto! :tada:

1 Like