How to log ecto repo update query

Questions / Help
, ,
1

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

1 Like
2

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.

2 Likes
3

It looks like SQL logged isn’t immediately executable as a plain old SQL query:

SELECT TRUE FROM "places" AS p0 WHERE (p0."slug" = $1) LIMIT 1 ["the-slug"]: erl_level=debug ansi_color=cyan application=ecto_sql domain=elixir file=lib

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.

1 Like
4

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.

1 Like
5

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.

6

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?

7

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. https://blog.soykaf.com/post/postgresql-elixir-troubles/

8

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.

9

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 :slight_smile:

10

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.

Recently I posted a library which allows interpolating arguments EctoLog - utility for inlining parameters into a query

2 Likes
11

Neat, that’s exactly what I’m looking for! Thank you.