Upsert on Postgres without migrating an identiy?

Ash Forum Ash Questions / Help
,
1

How can I upsert without creating an identity on Postgres? I want to check if a record exists with a combination of parent_id and name for some records, but I do not want to enforce it with Postgres.

  postgres do
    table "entities"
    repo MyApp.Repo

    references do
      reference :children, index?: true, on_delete: :delete
      reference :parent, index?: true, on_delete: :delete
    end

    # skip_identities [:parent_id_child_name]
  end

  identities do
    identity :parent_id_child_name, [:parent_id, :name] do
      # eager_check? true
      # pre_check? true
    end
  end

  actions do
    defaults [:read]

    create :upsert_data do
      accept [:parent_id, :name, :data]
      upsert? true
      upsert_identity :parent_id_child_name
    end
  end

skip_identities cannot compile.

error: undefined function skip_identities/1 (there is no such import)`

eager_check? and pre_check? doesn’t update.

%Ash.Error.Changes.InvalidChanges{
       fields: [:parent_id, :name],
       message: "has already been taken",
2

It isn’t possible :slight_smile: Postgres requires a unique constraint to do an upsert. So you’d have to roll your own with application logic if you don’t want to have said unique constraint.

3 Likes
3

And you probably don’t want to do that either because IIRC the sort of “get or insert” semantics you would need to implement an upsert are literally impossible to achieve in Postgres outside of SERIALIZABLE. And even then I don’t believe it would be atomic (some transactions would just have to be retried).

(You can materialize the conflict onto another row/lock. I find this approach rather cursed, but I’ve had to do it in some cases - not upserts though, why fight the platform)

Is there any particular reason you are avoiding a constraint?

1 Like
4

Correct, you can also use an advisory lock to avoid having to lock some specific row as well. We had roughly the same conversation a while back :laughing:

1 Like
5

I remember! :slight_smile: Mostly just trying to warn the OP that this is not a path you want to head down unless you know what you’re doing because it’s fraught with footguns and using a unique constraint is so much easier.

1 Like