Unexpected behavior with upsert?: true setting

Ash Forum Ash Questions / Help
, ,
1

I want to create an upsert action that will create a new record if no id is contained in the request and update the record if there is an id in the request. I thought that this might be the default behavior without upsert_identity or upset_fields options being set. However, none of these configurations works:

Action definition

    create :save do
      upsert? true
      upsert_identity :unique_id
      upsert_fields {:replace_all_except, [:id]}
    end

Code Interface definition
define :save, action: :save

  identities do
    identity :unique_id, :id
  end

However when I call:

instance = Resource.save!(%{name: "Name"}, tenant: "tenant")
instance2 = Resource.save!(%{name: "Name2", id: instance.id}, tenant: "tenant")

a second record is created in the database.

2

Hmm…that does look like the right way to do it. Granted, you don’t need the unique_id constraint if id is the primary key. So it should have the behavior you said by default, meaning just upsert? true would be necessary. What SQL does the insert produce?

3

I was thinking that was the default, but when it didn’t work I tried adding the additional configuration. I’ll get that SQL for you.

4 
iex(1)> inst = Register.save!(%{location: "Back Counter", dejavoo_register_id: "22", dejavoo_auth_key: "puts", printer_id: "yex", client_group_id: "1065b90b-618f-404a-bd16-6cad6e949d82"}, tenant: "dreambean")
[debug] QUERY OK db=1.5ms idle=1302.3ms
begin []
↳ anonymous fn/3 in Ash.Changeset.with_hooks/3, at: lib/ash/changeset/changeset.ex:2488
[debug] QUERY OK source="registers" db=5.1ms
INSERT INTO "dreambean"."registers" AS r0 ("id","location","dejavoo_register_id","dejavoo_auth_key","printer_id","is_locked","created_at","updated_at","client_group_id") VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9) ON CONFLICT ("id") DO UPDATE SET "location" = EXCLUDED."location", "dejavoo_register_id" = EXCLUDED."dejavoo_register_id", "dejavoo_auth_key" = EXCLUDED."dejavoo_auth_key", "printer_id" = EXCLUDED."printer_id", "client_group_id" = EXCLUDED."client_group_id", "updated_at" = COALESCE(EXCLUDED."updated_at", $10) RETURNING "client_group_id","updated_at","created_at","is_locked","printer_id","dejavoo_auth_key","dejavoo_register_id","location","id" ["1b3cf4b1-9459-4fef-a392-539f1d634670", "Back Counter", "22", "puts", "yex", true, ~U[2024-03-19 19:27:32.324905Z], ~U[2024-03-19 19:27:32.324905Z], "1065b90b-618f-404a-bd16-6cad6e949d82", ~U[2024-03-19 19:27:32.336228Z]]
↳ AshPostgres.DataLayer.bulk_create/3, at: lib/data_layer.ex:1412
[debug] QUERY OK db=0.5ms
commit []
↳ anonymous fn/3 in Ash.Changeset.with_hooks/3, at: lib/ash/changeset/changeset.ex:2488
#PointOfSale.Stores.Register<
  tills: #Ash.NotLoaded<:relationship, field: :tills>,
  active_order: #Ash.NotLoaded<:relationship, field: :active_order>,
  client_group: #Ash.NotLoaded<:relationship, field: :client_group>,
  __meta__: #Ecto.Schema.Metadata<:loaded, "dreambean", "registers">,
  id: "1b3cf4b1-9459-4fef-a392-539f1d634670",
  location: "Back Counter",
  dejavoo_register_id: "22",
  dejavoo_auth_key: "puts",
  printer_id: "yex",
  is_locked: true,
  created_at: ~U[2024-03-19 19:27:32.324905Z],
  updated_at: ~U[2024-03-19 19:27:32.324905Z],
  client_group_id: "1065b90b-618f-404a-bd16-6cad6e949d82",
  aggregates: %{},
  calculations: %{},
  ...
>
iex(2)> Register.save!(%{location: "Back Counter", dejavoo_register_id: "88", dejavoo_auth_key: "puts", printer_id: "yex", client_group_id: "1065b90b-618f-404a-bd16-6cad6e949d82", id: inst.id}, tenant: "dreambean")
[debug] QUERY OK db=0.8ms idle=1266.9ms
begin []
↳ anonymous fn/3 in Ash.Changeset.with_hooks/3, at: lib/ash/changeset/changeset.ex:2488
[debug] QUERY OK source="registers" db=5.2ms
INSERT INTO "dreambean"."registers" AS r0 ("id","location","dejavoo_register_id","dejavoo_auth_key","printer_id","is_locked","created_at","updated_at","client_group_id") VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9) ON CONFLICT ("id") DO UPDATE SET "location" = EXCLUDED."location", "dejavoo_register_id" = EXCLUDED."dejavoo_register_id", "dejavoo_auth_key" = EXCLUDED."dejavoo_auth_key", "printer_id" = EXCLUDED."printer_id", "client_group_id" = EXCLUDED."client_group_id", "updated_at" = COALESCE(EXCLUDED."updated_at", $10) RETURNING "client_group_id","updated_at","created_at","is_locked","printer_id","dejavoo_auth_key","dejavoo_register_id","location","id" ["a3bbbcce-93d6-4683-9db7-8ac440f5fc91", "Back Counter", "88", "puts", "yex", true, ~U[2024-03-19 19:27:46.293846Z], ~U[2024-03-19 19:27:46.293846Z], "1065b90b-618f-404a-bd16-6cad6e949d82", ~U[2024-03-19 19:27:46.295117Z]]
↳ AshPostgres.DataLayer.bulk_create/3, at: lib/data_layer.ex:1412
[debug] QUERY OK db=1.6ms
commit []
↳ anonymous fn/3 in Ash.Changeset.with_hooks/3, at: lib/ash/changeset/changeset.ex:2488
#PointOfSale.Stores.Register<
  tills: #Ash.NotLoaded<:relationship, field: :tills>,
  active_order: #Ash.NotLoaded<:relationship, field: :active_order>,
  client_group: #Ash.NotLoaded<:relationship, field: :client_group>,
  __meta__: #Ecto.Schema.Metadata<:loaded, "dreambean", "registers">,
  id: "a3bbbcce-93d6-4683-9db7-8ac440f5fc91",
  location: "Back Counter",
  dejavoo_register_id: "88",
  dejavoo_auth_key: "puts",
  printer_id: "yex",
  is_locked: true,
  created_at: ~U[2024-03-19 19:27:46.293846Z],
  updated_at: ~U[2024-03-19 19:27:46.293846Z],
  client_group_id: "1065b90b-618f-404a-bd16-6cad6e949d82",
  aggregates: %{},
  calculations: %{},
  ...
5

Hrm, the id from the first isn’t showing up in the second…

6

FYI, this output is going back to just setting upsert?: true and removing the other configuration

7

Ah, okay right. So the issue is that the id isn’t writable by your save action. You can do this:

uuid_primary_key :id, writable?: true

to make it writable.

1 Like
8

That did it. Thanks for the help!

1 Like