user20230119
Upsert on Postgres without migrating an identiy?
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",
Trending in Questions
Other Trending Topics
Latest Ash Threads
Latest on Elixir Forum
Sponsor Spotlight
Error tracking, logs, APM, uptime and cron monitoring in one dev-friendly platform. Find and fix problems before your users notice.
Categories:
Sub Categories:
Forums
Popular Tags
- #ecto
- #liveview
- #troubleshooting
- #learning-elixir
- #deployment
- #library
- #erlang
- #testing
- #genserver
- #mix
- #absinthe
- #remote-other
- #otp
- #plug
- #how-to-question
- #macros
- #postgres
- #channels
- #elixirconf
- #exunit
- #discussion
- #code-sync
- #javascript
- #podcasts
- #onsite
- #dialyzer
- #docker
- #authentication
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #ecto-query
- #elixir-ls
- #phoenix_html
- #iex
- #blog-post
- #graphql
- #genstage
- #ai
- #elixirconf-us
- #websockets
- #supervisor
- #advent-of-code
- #distillery
- #processes
- #api
- #forms
- #metaprogramming
- #hex
- #performance
Our Sponsors
Build Elixir applications with speed and confidence.
Supporting innovation across the BEAM ecosystem.
We build reliable cloud platforms for business-critical systems.
Catch errors, track performance, monitor hosts and more.
Error tracking, logs, uptime, and Just Enough APM. Built for developers.
Develop your skills with books, videos, and courses.
Bringing learning, work and compliance together since 1987.
Enabling companies to succeed by building software people love.
The team behind Membrane, Popcorn, LiveDebugger. Available for hire.
Courses that'll move you from confusion to "Aha, now I get it!"
First 4 of 4 Posts
zachdaniel
It isn’t possible
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.
garrison
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?
zachdaniel
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
garrison
I remember!
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.