Unexpected encoding of a boolean value in a table referenced by an FK

[alco]

The setup is simple:

• create two tables: items and subitems
• subitems has an FK reference to items via the item_id column
• electrify both tables
• on the client, subscribe to subitems and include items
• in postgres, insert a new row into items, populating its boolean column with either true or false
• in postgres, insert a new row into subitems that references the above row from items
• observe that the row from items has its boolean value encoded as a full word, "true" or "false", which results in a decoding error

This doesn't occur during the initial sync and only occurs for rows of a referenced table that are streamed to the client during regular replication (after the initial sync has finished).

Postgres schema:

CREATE TABLE IF NOT EXISTS items (
  id TEXT PRIMARY KEY,
  value TEXT NOT NULL,
  is_open BOOL
);
 
CREATE TABLE subitems (
  id TEXT PRIMARY KEY,
  item_id TEXT REFERENCES items(id),
  is_open BOOL
);
 
ALTER TABLE items ENABLE ELECTRIC;
ALTER TABLE subitems ENABLE ELECTRIC;

Client subscription:

const shape = await db.subitems.sync({ include: { items: true } })

Inserts in Postgres:

[localhost] postgres:electric=# insert into items values ('5', 'five', true);
INSERT 0 1
[localhost] postgres:electric=# insert into subitems values ('5', '5', false);
INSERT 0 1

Client log:

[proto] recv: #SatRelation{
    for: public.items, 
    as: 16829, 
    cols: [id: text PK, value: text, is_open: bool]
}
[proto] recv: #SatRelation{
    for: public.subitems, 
    as: 16836, 
    cols: [id: text PK, item_id: text, is_open: bool]
}
[proto] recv: #SatOpLog{
    ops: [
        #Begin{lsn: MjYxODIxNjA=, ts: 1713867028529, isMigration: false},
        #Insert{for: 16836, tags: [postgres_1@1713867028529], new: ["5", "5", "f"]}, 
        #Insert{for: 16829, tags: [postgres_1@1713867014785], new: ["5", "five", "true"]}, 
        #Commit{lsn: MjYxODIxNjA=}
    ]
}

[linear[bot]]

VAX-1806 Unexpected encoding of a boolean value in a table referenced by an FK

[alco]

I have traced the origin of the "true" value to this place in LogicalReplicationProducer module's implementation:

  defp process_message(
         %Message{transactional?: true, prefix: "electric.fk_chain_touch", content: content},
         state
       ) do
    received = Jason.decode!(content)

The JSON-encoded content is decoded into

%{
  "data" => %{"id" => "5", "is_open" => "true", "value" => "five"},
  "pk" => ["5"],
  "schema" => "public",
  "table" => "items",
  "tags" => "{\"(\\\"2024-04-23 10:31:43.066+00\\\",)\"}"
}

And "data" later ends up passed verbatim to the serialization function in the Serialization module to encode it as part of a SatOpLog message for the client. The problem lies in the fact that the source encoding of values in this "data" map is different from what we get when querying Postgres directly with epgsql functions or when receiving regular logical replication messages.

[KyleAMathews]

👋 we've been working the last month on a rebuild of the Electric server over at a temporary repo https://github.com/electric-sql/electric-next/

You can read more about why we made the decision at https://next.electric-sql.com/about

We're really excited about all the new possibilities the new server brings and we hope you'll check it out soon and give us your feedback.

We're now moving the temporary repo back here. As part of that migration we're closing all the old issues and PRs. We really appreciate you taking the time to investigate and report this issue!