tanweerdev
I know ecto has Ecto.Adapters.SQL.to_sql(:all, repo, Post) which will give you raw sql for any query. But I want to inspect/debug an update which is giving me ** (Postgrex.Error) ERROR 21000 (cardinality_violation) more than one row returned by a subquery used as an expression . is there a way I can inspect changeset update inside test cases ie what query exactly was generated by ecto(why I wanna debug because inserting directly to postgres is working perfectly fine)
#Ecto.Changeset<
action: nil,
changes: %{
infection_rxs: [
#Ecto.Changeset<action: :replace, changes: %{}, errors: [],
data: #HaiData.InfectionRx<>, valid?: true>,
#Ecto.Changeset<
action: :insert,
changes: %{infection_id: 55, rx_id: 155},
errors: [],
data: #HaiData.InfectionRx<>,
valid?: true
>
]
},
errors: [],
data: #HaiData.Infection<>,
valid?: true
>
and by running Repo.update(changeset), I get
(Postgrex.Error) ERROR 21000 (cardinality_violation) more than one row returned by a subquery used as an expression
which is really strange for an update
Where Next?
Trending in Questions
Other Trending Topics
Latest on Elixir Forum
Sponsor Spotlight
Supporting innovation across the BEAM ecosystem.
Categories:
Sub Categories:
Forums
Popular Tags
- #ecto
- #liveview
- #troubleshooting
- #learning-elixir
- #library
- #deployment
- #erlang
- #testing
- #genserver
- #mix
- #absinthe
- #remote-other
- #otp
- #plug
- #how-to-question
- #macros
- #postgres
- #elixirconf
- #channels
- #exunit
- #discussion
- #code-sync
- #podcasts
- #javascript
- #onsite
- #dialyzer
- #docker
- #authentication
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #ecto-query
- #elixirconf-us
- #ai
- #blog-post
- #elixir-ls
- #phoenix_html
- #iex
- #graphql
- #genstage
- #websockets
- #supervisor
- #advent-of-code
- #distillery
- #processes
- #api
- #forms
- #hex
- #security
- #metaprogramming
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.
Practical resources that improve the lives of professional developers.
Bringing learning, work and compliance together since 1987.
Producing high quality Elixir screencasts since 2017.
The team behind Membrane, Popcorn, LiveDebugger. Available for hire.
Courses that'll move you from confusion to "Aha, now I get it!"
Showing Posts 1 to 10- Show Best Posts
- Show All (oldest first)
- Show All (newest first)
blatyo
You can set the log level for test to debug and it’ll print to your console all the SQL executed in your tests. You can run one test, by doing
mix test file:line, so that all you see is that one.mike_bianco
It looks like SQL logged isn’t immediately executable as a plain old SQL query:
You need to interpolate the array of arguments into the query in order to execute directly on the DB. I’d love an option to output raw SQL that can be copy/pasted directly into a Postgres GUI (like Postico). Makes for slick local debugging.
blatyo
I don’t disagree that it would be useful to have the values filled in to run. Just wanted to mention, that when Ecto sends the query, it sends it with the place holders in the query and the values separately. So, Ecto never actually builds a query that has the values in it.
LostKobrakai
I seem to recall recently seeing a project recently, which would do such interpolation for logs, but can’t find it anymore. But as @blatyo mentioned, ecto doesn’t interpolate into the query, it doesn’t even have code for doing that in the first place. Everything is sent separately to the db.
mike_bianco
Makes sense! I imagine some library that Ecto depends on ends up rendering the raw SQL, right? Is there a way to configure that library to output raw rendered SQL?
gregvaughn
I have not dug into the code to verify, but I believe this format is ultimately how the low level driver communicates with Postgres. This allows Postgres to create prepared statements where it has analyzed and determined a query plan and cached that so that future calls of the same SQL “template” are faster.
This blog is tangentially relevant to understanding a particular case where the prepared statements were causing negative performance characteristics in a rare situation. Elixir and Postgres: A Rarely Mentioned Problem | Lainblog
LostKobrakai
That’s not the case. The data is never interpolated. Query and parameters are sent to postgres as separate things. This is for performance reasons (preparing statements), but also for security reasons. When you don’t interpolate, then there’s no attack vector for sql injection attacks.
So the only thing possibly being able to know the query with inlined values would be postgres itself, but I’d even doubt the db will “merge” the query just to parse things out again.
mike_bianco
Oh, interesting! I didn’t know that PG allows you to send parameterized queries along. That’s really neat, very much makes sense why we can’t expose the raw SQL then
fuelen
Recently I posted a library which allows interpolating arguments EctoLog - utility for inlining parameters into a query
mike_bianco
Neat, that’s exactly what I’m looking for! Thank you.