Can't Store Array of Domain Over Composite Type

[dcuddeback]

Elixir version

Erlang/OTP 28 [erts-16.1.1] [source] [64-bit] [smp:10:10] [ds:10:10:10] [async-threads:1] [jit]

Database and Version

postgres (PostgreSQL) 18.0

Postgrex Version

postgrex 0.22.0 (Hex package) (mix)

Current behavior

1. Create a composite type and a domain over that composite type to enforce some constraints. Create an example table using the domain type:

demodule CreateExampleTable do
  use Ecto.Migration

  def up do
    execute("""
      CREATE TYPE composite_example AS (
          a text,
          b integer
      )
    """)

    execute("""
      CREATE DOMAIN non_null_composite AS composite_example
      CHECK (
          (VALUE).a IS NOT NULL
      AND (VALUE).b IS NOT NULL)
    """)

    create table("examples") do
      add :single, :non_null_composite
      add :multiple, {:array, :non_null_composite}
    end
  end

  def down do
    drop table("examples")

    execute("DROP DOMAIN non_null_composite")
    execute("DROP TYPE composite_example")
  end
end

2. Define custom Ecto.Type:

defmodule CompositeExample do
  use Ecto.Type

  defstruct [:a, :b]

  @impl true
  def type(), do: :non_null_composite

  @impl true
  def cast(%__MODULE__{} = value) do
    {:ok, value}
  end
  def cast({a, b}) do
    {:ok, %__MODULE__{a: a, b: b}}
  end
  def cast(_) do
    :error
  end

  @impl true
  def dump(%__MODULE__{a: a, b: b}) do
    {:ok, {a, b}}
  end

  @impl true
  def load({a, b}) do
    {:ok, %__MODULE__{a: a, b: b}}
  end
end

3. Define schema:

defmodule Example do
  use Ecto.Schema

  @primary_key false

  schema "examples" do
    field :single, CompositeExample
    field :multiple, {:array, CompositeExample}
  end
end

4. Insert records into the schema:

Repo.insert!(%Example{single: %CompositeExample{a: "a", b: 1}})
Repo.insert!(%Example{multiple: [%CompositeExample{a: "a", b: 1}, %CompositeExample{a: "b", b: 2}]})

5. The first insert is successful (I can use the domain type as a column type).

[debug] QUERY ERROR source="examples" db=4.4ms queue=0.3ms idle=42.5ms
INSERT INTO "examples" ("single") VALUES ($1) [%CompositeExample{a: "a", b: 1}]

The second insert (using the domain type in an array) raises an error about encoding an anonymous tuple:

↳ :elixir_compiler_10.__FILE__/1, at: demo.exs:3
** (DBConnection.EncodeError) cannot encode anonymous tuple {"a", 1}. Please define a custom Postgrex extension that matches on its underlying type:

    use Postgrex.BinaryExtension, type: "typeinthedb"

    (postgrex 0.22.0) lib/postgrex/type_module.ex:194: Postgrex.DefaultTypes.encode_tuple/3
    (postgrex 0.22.0) lib/postgrex/type_module.ex:1084: Postgrex.DefaultTypes.encode_params/3
    (postgrex 0.22.0) lib/postgrex/query.ex:73: DBConnection.Query.Postgrex.Query.encode/3
    (db_connection 2.9.0) lib/db_connection.ex:1440: DBConnection.encode/5
    (db_connection 2.9.0) lib/db_connection.ex:1530: DBConnection.run_prepare_execute/5
    (db_connection 2.9.0) lib/db_connection.ex:1587: DBConnection.run_with_retries/5
    (db_connection 2.9.0) lib/db_connection.ex:791: DBConnection.parsed_prepare_execute/5
    (db_connection 2.9.0) lib/db_connection.ex:783: DBConnection.prepare_execute/4
    (postgrex 0.22.0) lib/postgrex.ex:298: Postgrex.query/4
    (ecto_sql 3.13.4) lib/ecto/adapters/sql.ex:1181: Ecto.Adapters.SQL.struct/10
    (ecto 3.13.5) lib/ecto/repo/schema.ex:1000: Ecto.Repo.Schema.apply/4
    (ecto 3.13.5) lib/ecto/repo/schema.ex:500: anonymous fn/15 in Ecto.Repo.Schema.do_insert/4
    (ecto 3.13.5) lib/ecto/repo/schema.ex:381: Ecto.Repo.Schema.insert!/4

Expected behavior

I wanted both inserts to succeed. This example actually works if I use the composite type in the array instead of the domain type. It's only when switching to the domain type inside the array that I get this error.

At first, I thought maybe domain types aren't supported by Postgrex, but I found this merged PR from a long time ago that added support specifically for domain types inside of arrays: #337. Maybe a regression of #337, or an edge-case of how domain types, composite types, and arrays interact with each other?

[josevalim]

I believe it is a duplicate of #353 (the oldest issue in our tracker).

[dcuddeback]

I read #353 when I was trying to figure this out. I interpreted that as having to do with redefining types, but I don't understand #353 well enough to know how that ties in. I thought #528 sounded pretty close to this one. The symptoms (works fine as a singular column, but fails in an array) sound similar.

[josevalim]

#353 says there are basically inherent problems to supporting composite types in Postgrex, because updates would change their representation, and we would not know, leading to subtle crashes. For this reason, we don't support them. You either need to support them individually (using custom decoders) or emit text queries.

[dcuddeback]

Composite types aren't supported at all? It's listed under "Data Representation" in the README, so I was under the impression that they would be supported.

I don't mind making a custom decoder or text queries. Do you know if I can emit the text representation directly from Ecto.Type dump/1? Or do I need to dig deeper into Postgrex?

[josevalim]

There is a footnote:

**** Anonymous composite types are decoded (read) as tuples but they cannot be encoded (written) to the database

I believe you would need a custom type in Postgrex. The whole Postgrex.Types.define(MyApp.PostgrexTypes, [MyApp.MyTypeExtension]).

If you do so, a PR to Postgrex.Extension that explains how to build one for composite types would be fantastic.

[dcuddeback]

I see. I'll have to dig deeper to understand the distinction between composite and anonymous composite types. Thanks for pointing me in the right direction. I appreciate you taking your time to explain what's going on. It could be fun to dig into the binary protocol. If I figure out something that can be generalized, I'll share it with you.

In the mean time, it seems to be working to use the composite type instead of domain type. I just lose the ability to put constraints on the composite type.

[dcuddeback]

I made some progress yesterday. After tracing my failing insert through Postgrex and DBConnection, I found that the OIDs from my database weren't being returned by Postgrex.Types.fetch/2, which is returning results directly from an ETS table (https://github.com/elixir-ecto/postgrex/blob/v0.22.0/lib/postgrex/types.ex#L355). So I looked into how the ETS table is built and zeroed in on the bootstrap query (https://github.com/elixir-ecto/postgrex/blob/v0.22.0/lib/postgrex/types.ex#L47).

When I run the generated query against my database, the results are missing my composite base types. The domain types are there, but they don't have their attributes in pg_attribute. Those have to be resolved to their base types to get their attributes. I wrote a replacement bootstrap query using WITH RECURSIVE to resolve domain types and it worked! However, my query doesn't currently support range types. A few of those tests fail when running Postgrex's test suite with my bootstrap query. So that's something I have to work on.

I'll be busy with family obligations for the next couple of days, so thought I'd leave this update for now.

I have one question that could help me direct my focus when I return to this. Does Postgrex do any recursion when building the types table or resolving OIDs? I didn't see it when I was looking at the code yesterday, but for some of my test cases to have worked, it seems like it must do that somewhere. If Postgrex resolves OIDs recursively somewhere, then I should be able to simplify my bootstrap query to not use WITH RECURSIVE.

[josevalim]

@dcuddeback you are going to eventually land of the limitation of #353. Even if we eventually fetch the OIDs for composite types, when you run a migration updating the type, then the OID will change, and we won't know, leading to subtle breakage. That's why we don't fetch them in the first place!

[dcuddeback]

@josevalim That makes sense in the context of a web app. And after looking into how OIDs are loaded by Postgrex, I think I better understand what you mean about #353.

My current use case is a hobby app that someone would run on a Raspberry Pi or personal computer. Migrations would be part of a software update where I make sure the app is shut down during migrations.

That's why we don't fetch them in the first place!

That's different from what I've observed on my machine. I did notice a config parameter for that (disable_composite_types) in the code (https://github.com/elixir-ecto/postgrex/blob/v0.22.0/lib/postgrex/types.ex#L88-L100):

    comp_oids =
      if s.disable_composite_types do
        "null"
      else
        """
        ARRAY (
          SELECT a.atttypid
          FROM pg_attribute AS a
          WHERE a.attrelid = t.typrelid AND a.attnum > 0 AND NOT a.attisdropped
          ORDER BY a.attnum
        )
        """
      end

But I've observed a few things that are perhaps not as intended:

1. disable_composite_types doesn't appear to be set by default (I see the ARRAY(...) clause in my logs without manually setting that config).

2. When I say that composite types are being excluded from the bootstrap query, it's actually caused by this clause (https://github.com/elixir-ecto/postgrex/blob/v0.22.0/lib/postgrex/types.ex#L50-L57):

   WHERE (t.typrelid = 0)
   AND (t.typelem = 0 OR NOT EXISTS (SELECT 1 FROM pg_catalog.pg_type s WHERE s.typrelid != 0 AND s.oid = t.typelem))

   which isn't guarded by the disable_composite_types flag. When I was working on this the other day, I found a more reliable way to exclude tables (the intent expressed by the comment in the code) was to use the pg_class.relkind column, at least on Postgres 18.

3. Even though the composite types are excluded by the bootstrap query, it still works to encode/decode them in some situations. I suspect that the OIDs for these are being loaded later, because I'll see something like this in the logs:

   [debug] %Postgrex.Query{...} uses unknown oid(s) 38880forcing us to reload type information from the database. This is expected behaviour whenever you migrate your database.
   

   To be clear, in my situation, that isn't triggered by a migration. I see that in my logs every time I run a one-off .exs script that utilizes a composite type.

It seems to me that composite type information might be sneaking into the type server when it's not intended. Based on what you've said about #353, it sounds like there's hesitancy to support composite types by default, because the subtle failures would probably result in a lot of support issues being filed, which I totally I understand. If that's accurate, I think a path forward could be:

1. Set disable_composite_types to true by default.
2. Make sure disable_composite_types fully blocks composite types in both the bootstrap and reload queries.
3. Update documentation to reflect above changes.
4. Make any changes to improve the support for composite types behind disable_composite_types=false.

That way, in order to get composite type support, people have to manually enable the config, which should expose them to a disclaimer about the limitations. (Or they don't discover the config var and end up writing custom extensions for their types.) I think that matches the intent that I'm gleaning from your comments.

Does that sound reasonable? If so, I can start working in that direction.

[josevalim]

I beleive there is one last point of confusion here: I believe there is more than one type of composite types in PostgreSQL and we support some of them. So we need to be careful when we say we will disable composite types by default, as that would certainly break existing apps. I believe the limitations we are discussing here are about "custom composite types", and perhaps we should refer to them as such to avoid further confusion?

[dcuddeback]

@josevalim Yeah, I just realized a few minutes ago that changing the default config would probably break existing uses of Ecto. I'm all for clearing up terminology. I think there are:

1. Custom composite types (e.g., CREATE TYPE complex AS (r float8, i float8)).

2. Table rows: "Whenever you create a table, a composite type is also automatically created, with the same name as the table, to represent the table's row type." (https://www.postgresql.org/docs/current/rowtypes.html)

3. Anything constructed with ROW():

   => SELECT ROW(1,2,3);
      row
   ---------
    (1,2,3)
   (1 row)

Some of the built-in types (point, box, etc) might be composites under the hood but they have their own send/recv functions and don't show up in pg_class.

When I run SELECT * FROM pg_class WHERE relkind = 'c' in psql, I only see my custom composites.

When I run SELECT * FROM pg_type WHERE typsend::text = 'record_send' OR typreceive::text = 'record_recv', I see my custom composites and some PostgreSQL internal types.

Since we're discussing encoding/decoding types in the wire protocol, I suggest that we consider "composite types" to be all types using record_send/record_recv and "custom composite types" to be the subset that are created by the user.

My interest here is in custom composite types. I could see someone wanting to use the PG internal types to write an admin tool. So I think all composite types (defined as using record_send/record_recv) could be in play for encoding/decoding issues. However, in the context of #353, I agree that we're probably only discussing custom composite types, since nobody should be running migrations on PG's internal types.

As for a path forward, I'll have to think about it some more since realizing that changing the default config might break existing apps. That's tricky, since even custom composite types work out of the box in some contexts, disabling only custom composites would also break some apps.

I have to get back to work on some family stuff, but this will be on my mind.

[josevalim]

Right. And the issues in that thread are about “custom composite types with mutable definitions”. If they never change, we are fine.