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

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.

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

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

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

Not sure what to make of this new error message…

OK, got some more time to spend on this and it seems this is now working completely as desired.

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!