Large TOASTable rows lead to crash loop

[KyleAMathews]

getting odd crash. I have a boolean column and I'm trying to update it from false to true and it's triggering some sort of crash, start, crash loop

await db.jobs.update({
                  data: { read: true },
  where: { job_id: job.job_id },
})

Screenshot from browser

From electric:

shannon-job-finder-electric-1 | ** (CaseClauseError) no case clause matching: :unchanged_toast
shannon-job-finder-electric-1 | (electric 0.9.4) lib/electric/satellite/serialization.ex
:233: anonymous fn/4 in Electric.Satellite.Serialization.map_to_row/3
shannon-job-finder-electric-1 | (elixir 1.16.1) lib/enum.ex:1826: Enum."-map_reduce/3-li
sts^mapfoldl/2-0-"/3
shannon-job-finder-electric-1 | (electric 0.9.4) lib/electric/satellite/serialization.ex
:230: Electric.Satellite.Serialization.map_to_row/3
shannon-job-finder-electric-1 | (electric 0.9.4) lib/electric/satellite/serialization.ex
:200: Electric.Satellite.Serialization.mk_trans_op/3
shannon-job-finder-electric-1 | (electric 0.9.4) lib/electric/satellite/serialization.ex
:164: Electric.Satellite.Serialization.serialize_change/2
shannon-job-finder-electric-1 | (elixir 1.16.1) lib/enum.ex:2528: Enum."-reduce/3-lists^
foldl/2-0-"/3
shannon-job-finder-electric-1 | (electric 0.9.4) lib/electric/satellite/serialization.ex
:45: Electric.Satellite.Serialization.serialize_trans/3
shannon-job-finder-electric-1 | (electric 0.9.4) lib/electric/satellite/protocol.ex:797:
 Electric.Satellite.Protocol.handle_out_trans/2
shannon-job-finder-electric-1 | Last message: {:"$gen_consumer", {#PID<0.5034.0>, #Reference
<0.3138072826.3718250497.27809>}, [{%Electric.Replication.Changes.Transaction{xid: 1025, chan
ges: [], commit_timestamp: ~U[2024-03-19 21:27:15.915778Z], origin: "postgres_1", publication
: "electric_publication", lsn: #Lsn<0/26334D0>, ack_fn: nil, origin_type: :postgresql}, 40056
016}, {%Electric.Replication.Changes.Transaction{xid: 1026, changes: [%Electric.Replication.C
hanges.UpdatedRecord{relation: {"public", "jobs"}, old_record: %{"apply_options" => "[{\"link
\": \"https://pangian.com/job/senior-product-designer-remote-1573/?utm_campaign=google_jobs_a
pply&utm_source=google_jobs_apply&utm_medium=organic\", \"title\": \"Apply directly on Pangia
n\"}, {\"link\": \"https://jobright.ai/jobs/info/65f23cd7d1ce6430a6f038c2?utm_campaign=google
_jobs_apply&utm_source=google_jobs_apply&utm_medium=organic\", \"title\": \"Apply on Jobright
 AI\"}]", "company_name" => "byte®", "cons" => "[\"The company's industry focus on orthodonti
cs is not explicitly listed as one of Shannon's interests\", \"Potential for a fast-paced env
ironment, which may not align with Shannon's preference for a balanced lifestyle\", \"Lack of
 information on the company's size and stage, which is important to determine if it's a growt
h-stage startup\"]", "created_at" => nil, "description" => "100% remote job\n\nSenior Product
 Designer | byte® | United States...\n\nAbout Byte\n\nWe are an ambitious team that’s transfo
rming the way people can achieve a beautiful, more confident smile. No longer do you have to
step inside the orthodontist office to create the smile of your dreams. Our doctor-directed,
at-home system makes it easy, safe and affordable for everyone. We also believe that a job sh
ould do more than pay the bills. It should fill you with a sense of purpose. The kind you fee
l when you’re a part of something BIG.\n\nInterested? Let’s talk.\n\nByte is looking for an e
xperienced Senior Product Designer who is passionate about building products, experiences, an
d services that improve our core customer journey as well as help define new opportunities th
at take us onto new platforms and into new business contexts. You will collaborate with a cro
ss-disciplinary team to research, prototype, design and deliver connected products and servic
es that have measurable business impact and provide customers with great outcomes and best-in
-class experiences.\n\nPlease include a link or attachment with your portfolio when you apply
. Resumes are helpful to understand your experience but we would like to see work samples as
well. If you aren’t able to share work publicly, just note that in your cover letter so we kn
ow why it’s missing from your application.\n\nKey Responsibilities\n• Work through design pro
blems from beginning to end, from translating insights from research and analytics into conce
pts that form the foundations of new products and features.\n• Partner with product managers
to define and evolve product roadmaps.\n• Steward a holistic, empathetic vision of our custom
er journey and service experiences across touchpoints, over the course of multiple projects,
optimizations and initiatives that will result in best-in-class work.\n• Able to ideate for d
irectional and executional purposes — from sketching and prototyping ideas to designing or le
ading others through the fine-grained details of components, interactions, and transitions.\n
• Strong ability to concept and envision design solutions, systems, patterns and arguments an
d communicate them clearly to others.\n• Conduct user research to uncover insights and valida
te hypothesis for new and existing experiences and collaborate with your product and engineer
 team members to uncover and define opportunities, features, and rapidly iterate on solutions
.\n• Lead other designers across the organization to raise the visual and experience bar by c
ontinuously improving our design processes, principles, UI systems and tooling.\n\nRequiremen
ts and Qualifications\n• 8+ years experience designing digital products and systems. You have
 helped ship products that solve real problems, and that people use.\n• Experience working wi
th the entire product development process: from user research, problem-solving and system mod
eling, and detailed visual and interaction design through to development.\n• Excellent and se
asoned visual, graphic, and typographic skills.\n• Capable of leading and inspiring others to
 do great work.\n• Manage through moments of ambiguity and find a way to well designed clarit
y.\n• Ability to design polished, high-fidelity mockups of your solutions using modern design
 tools such as Figma.\n• Ability to create interactive prototypes using tools like Figma or F
ramer.\n• Familiarity with Scrum practices and Jira\n• Strong verbal and written communicatio
n skills to help people understand the rationale behind your proposals, and the complex nuanc
es of design problems.\n• Motivated self-starter who can deeply collaborate with other discip
lines including product management, engineering, research, and analytics.\n• A growth mindset
. Willingness to own problems. Comfort with ambiguity. A desire to make things better. A bala
nced sense of optimism and humility.\n\nYour experience should ideally focus on designing sof
tware products, and your portfolio should clearly outline the problems you’ve taken on with y
our design work. It should touch on the process you applied to find solutions and overcome ch
allenge" <> ..., "detected_extensions" => "{\"posted_at\": \"5 days ago\", \"schedule_type\":
 \"Full-time\", \"work_from_home\": true}", "extensions" => "[\"5 days ago\", \"Work from hom
e\", \"Full-time\", \"No degree mentioned\", \"Health insurance\", \"Dental insurance\", \"Pa
id time off\"]", "google_jobs_listing_url" => "https://www.google.com/search?q=Senior+Product
+Designer+%28Remote%29&ibp=htl;jobs&uule=w+CAIQICINVW5pdGVkIFN0YXRlcw#htidocid=en5U93ThGPhLfX
QqAAAAAA==", "job_highlights" => "[{\"items\": [\"8+ years experience designing digital produ
cts and systems\", \"Experience working with the entire product development process: from use
r research, problem-solving and system modeling, and detailed visual and interaction design t
hrough to development\", \"Excellent and seasoned visual, graphic, and typographic skills\",
\"Capable of leading and inspiring others to do great work\", \"Ability to create interactive
 prototypes using tools like Figma or Framer\", \"Familiarity with Scrum practices and Jira\"
, \"Strong verbal and written communication skills to help people understand the rationale be
hind your proposals, and the complex nuances of design problems\", \"Motivated self-starter w
ho can deeply collaborate with other disciplines including product management, engineering, r
esearch, and analytics\", \"A growth mindset\", \"Willingness to own problems\", \"Comfort wi
th ambiguity\", \"A desire to make things better\", \"A balanced sense of optimism and humili
ty\", \"Your experience should ideally focus on designing software products, and your portfol
io should clearly outline the problems you’ve taken on (truncated)
shannon-job-finder-electric-1 | 21:27:32.859 pid=<0.5036.0> client_id=e6c1ad25-a056-4a80-862
8-7b7ce82dabd4 instance_id=72c95d51-e05c-43c6-ad32-4a8c45d3a752 user_id=595e5e88-b227-430b-b3
7a-d766d82a09ae [info] Successfully authenticated the client
shannon-job-finder-electric-1 | 21:27:32.859 pid=<0.5036.0> client_id=e6c1ad25-a056-4a80-862
8-7b7ce82dabd4 instance_id=72c95d51-e05c-43c6-ad32-4a8c45d3a752 user_id=595e5e88-b227-430b-b3
7a-d766d82a09ae [info] Electric.Replication.Postgres.Client.with_conn(%{database: ~c"shannon-
job-finder", host: ~c"postgres", ip_addr: ~c"192.168.176.2", ipv6: true, nulls: [nil, :null,
:undefined], password: ~c"******", port: 5432, ssl: true, ssl_opts: [server_name_indication:
~c"postgres"], timeout: 5000, username: ~c"postgres"})
 

[linear]

VAX-1746 Crash loop when updating a boolean in table `unchanged_toast`

[alco]

@KyleAMathews The problem is not the boolean column but the large text value you have there. Postgres stores that value separately from the row itself (it's called TOAST). When Postgres streams changes on the logical replication stream to Electric, it does not include "toasted" values if they are unchanged. Electric is not prepared to deal with that because it uses full row data for conflict resolution, e.g. when a row gets deleted in one place but needs to be resurrected in response to an update.

I can't think of any workaround for this right now.

[KyleAMathews]

Hmm gotcha. I guess I'll split the read info to another table then for now.

[KyleAMathews]

Informative thread on TOAST https://twitter.com/gwenshap/status/1770209083225981418

[msfstef]

@KyleAMathews might be worth renaming the issue to something like "Large TOASTable rows lead to crash loop" for easier lookup as we're probably not closing this soon.

@alco would setting the column STORAGE mode to PLAIN or MAIN to prevent out-of-line storage be a valid workaround for this issue? if so, should we add it as a warning in docs until we have a solution?