I’m trying to delete an association from a record by setting its value to
nil in my Changeset. This all seems to work properly, but I get an error during the transaction and it rolls everything back:
belongs_to(:schedule, Ido.Calendar.Schedule, on_replace: :delete)
[debug] QUERY OK db=6.5ms
DELETE FROM "schedules" WHERE "id" = $1 ["1be15071-1aeb-4b2e-8a09-0582b5823912"]
[debug] QUERY OK db=0.1ms
UPDATE "elements" SET "schedule_id" = $1, "updated_at" = $2 WHERE "id" = $3
[nil, ~N[2023-02-04 11:55:01], "2a0031bd-d028-4e9e-9978-75b494038406"]
[debug] QUERY OK db=0.4ms
[error] GenServer #PID<0.4449.0> terminating
** (Ecto.StaleEntryError) attempted to update a stale struct:
Why is this a stale entry? This works great for creating the Location, but deletes and updates both cause the error.
Did you preload the existing record?
Yes, the current association is preloaded (I think Changeset will also error if you try updating that association without preloading it first).
That error happens when an
UPDATE updates zero rows when at least one was expected.
That doesn’t make this any less confusing, though. The only way I could see that
UPDATE "elements" statement doing that would be if the target
elements row stopped existing…
Just from the sql logs, my bet (and i’m not sure), you’re regenerating the id from the association, so what’s happening is
- you had record with one id
- you try to update it, but then also regenerate od
- on commit, because the id is missing, it does a delete
- then it tries to do an update on the record with the different id (it has an id, so it thinks it needs to be an update) and fails, because the record doesn’t exist
Not sure how well explained that is, but hopefully it helps and doesn’t send you on the wrong trail.
Hrm, so is there a different way I should handle this update? I was hoping the association’s
on_replace: delete would handle the heavy-lifting for me. I know I could switch to doing this more manually- if the association is new,
nil it out, if it’s existing, set the
action: delete on the child data.
Try not generating a new id for the replaced record. Either keep the same, so it will be updated, or set it to nil.
This is really off the top of my head, but if the id is nil it might see it as a delete + create and get it in that way.
That would work for replacements, but I have this same error for deletes.
To me the cleanest way to update this association is to nil it out, then possibly put something new in later. That lets my form be very dumb: it just looks to see if there’s a value. The other solutions (eg
action: delete) will force me to handle the edge cases in my heex, handle_event, and changeset.
What you’ve shown looks like it should work
Can you check the configuration of the
elements table, in particular any foreign keys? The behavior you’re observing could be caused by an
ON DELETE CASCADE.
My migration to add schedule to element is this:
alter table(:elements) do
add(:schedule_id, references(:schedules, type: :uuid, on_delete: :delete_all))
My thought was if the element is deleted, it should also delete any related schedules (schedules aren’t shared across multiple items).
A foreign key constraint on
ON DELETE CASCADE means that the rows in
elements will be deleted when the corresponding row in
schedules is deleted, not the other way around.
This explains the error you’re seeing: the DB is deleting the
Element out from under you!
A more-typical way to write this association (especially if the element <-> schedule relation might include multiple schedules someday) is with the
belongs_to on the
Schedule side and a
That was it! I thought that
:delete_all worked the other way. I changed it to
:nothing but then started getting a foreign key constraint error.
:nilify_all was what I actually wanted.