Backfill crash-loops when a table's primary key includes an enum column

[Catskan]

What happens

I set up a sink with an initial backfill on a table whose primary key includes a Postgres enum column. The backfill never makes progress — the TableReaderServer for that consumer keeps crashing (roughly once a second) and the backfill stays stuck in active.

The crash:

[error] [TableReaderServer] ID fetch task failed backfill_id=...
** (MatchError) no match of right hand side value:
   {:error,
    %Postgrex.Error{
      postgres: %{
        code: :invalid_text_representation,
        message: "invalid input value for enum mood: \"\"",
        routine: "enum_recv",
        where: "unnamed portal parameter $2",
        pg_code: "22P02"
      }
    }}
    (sequin 0.1.0) lib/sequin/runtime/table_reader.ex:177: Sequin.Runtime.TableReader.fetch_batch_pks/4

Running v0.14.6, but the relevant code looks the same on main.

Why

The initial keyset cursor uses an empty string for any column whose type isn't uuid / number / date / time:

# lib/sequin/runtime/keyset_cursor.ex
def min_for_type(_), do: ""

casted_cursor_values/2 passes that through untouched for an enum column (cast_value(_, val), do: val), so the batch query ends up binding $2 = "". Postgres refuses "" as an enum value (enum_recv, SQLSTATE 22P02). And because fetch_batch_pks/4 matches the result with

{:ok, %Postgrex.Result{} = result} = Postgres.query(db_or_conn, sql, params, timeout: timeout)

the {:error, _} isn't handled, the task raises, and it retries forever.

Repro

CREATE TYPE mood AS ENUM ('calm', 'happy', 'sad');
CREATE TABLE t (
  id uuid DEFAULT gen_random_uuid(),
  m  mood,
  PRIMARY KEY (m, id)
);
INSERT INTO t (m) VALUES ('calm'), ('happy'), ('sad');

Point a sink at public.t with an initial backfill and it starts crash-looping right away.

Related

Same root cause, quieter symptom: with a character(n) / bpchar primary key the empty-string cursor doesn't raise, but the backfill finishes having read 0 rows — it ends up completed with rows_processed_count = 0. uuid / text / timestamp keys work fine.

Suggestion

min_for_type/1 returning "" isn't a valid minimum for a lot of column types. For an enum it could use the first label (ORDER BY enumsortorder LIMIT 1); more generally the backfill could seed the cursor from SELECT min(<sort_col>) instead of a synthetic value. And fetch_batch_pks/4 probably shouldn't hard-match the query result — right now any Postgres error there turns into a crash loop.

Can put together a PR if that'd help.

[dosubot]

Hi @Catskan! I'm Dosu and I'm helping the Sequin team.

Great bug report — the analysis checks out. I can confirm what you're seeing in the code:

1. min_for_type/1 has a catch-all that returns "" for any type not explicitly handled (uuid, numeric, date/time), so enums hit that fallback [1].

2. cast_value/2 similarly passes enum values through unchanged, so the empty string reaches Postgres as-is [2].

3. fetch_batch_pks/4 uses an unsafe pattern match on line 177 — {:ok, %Postgrex.Result{} = result} = Postgres.query(...) — which raises a MatchError on any {:error, _} return, causing the crash loop [3]. Notably, the sibling function fetch_batch/6 in the same module already handles errors gracefully with a case statement [4].

There are no existing PRs or issues addressing enum PKs specifically. The closest prior work was fixing Postgrex.Interval serialization crashes during backfills [5] and relaxing UUID validation [6] — same pattern of type-specific gaps.

Your suggestion of a PR would definitely be welcome based on the patterns I see here. The two-pronged fix you outlined (better min_for_type for enums + graceful error handling in fetch_batch_pks) aligns well with how similar issues have been resolved in the past.

To reply, just mention @dosu.

---

Share context across your team and agents. Try Dosu.

[Catskan]

Dug into the character(n)/bpchar case a bit more since it's biting us harder than the enum one.

It's not just the empty-string min cursor — a backfill keyed on a bpchar column reads 0 rows regardless of the cursor value. I tried a partial backfill with sort_column set to the char(9) PK and a valid non-empty start_position well below every key; the exact query returns all rows in psql:

PREPARE q(character(9)) AS SELECT count(*) FROM t WHERE (code) >= ($1);
EXECUTE q('0000-0000');  -- 3111

but the backfill still completes with rows_processed_count = 0 (the first fetch_batch_pks comes back empty, so pagination "completes" immediately). A text PK on another table backfills fine with the same empty-string cursor, and uuid / enum (with the first label as start) work too — only bpchar returns nothing. So it looks like the batch query built for a bpchar cursor column doesn't match rows when executed through Postgrex (binary params), even though the equivalent SQL does in a text-protocol client.

Net effect: for a table whose PK is character(n), there's no config-side workaround — the PK is always in the keyset cursor. Happy to help repro if useful.

[dosubot]

@Catskan — good find on the bpchar case. The root cause is a type mismatch in the binary protocol.

Sequin passes cursor parameters to Postgrex as a plain list without explicit type annotations [1]. Postgrex infers the parameter type as text (OID 25). The keyset WHERE clause is built as a bare tuple comparison — (code) >= ($1) — with no cast on the parameter [2] [3].

In psql (text protocol), Postgres coerces the parameter to match the column type implicitly, so the comparison works. In Postgrex (binary protocol), the parameter arrives with an explicit text OID against a bpchar column. Postgres won't implicitly cross-type compare in binary mode the same way, so the tuple comparison yields no matches and the backfill "completes" immediately with 0 rows.

A fix would involve adding an explicit cast in the generated SQL when the column is character(n):

-- instead of:
WHERE ("code") >= ($1)
-- emit:
WHERE ("code") >= ($1::character(9))

Sequin already uses SQL-level :: casts elsewhere when it needs type control [4], so this would be consistent with the existing approach. The column type info (including character_maximum_length) is available on the PostgresDatabaseTable.Column struct, so where_sql/2 or fetch_batch_query/3 could emit the cast for bpchar columns.

This is separate from the enum crash-loop fix but the same area of code — a PR could address both together.

To reply, just mention @dosu.

---

Docs are dead. Just use Dosu.