[Proposal] Upstream a shared query option for injecting SQL comments

[Billy Ceskavich]

### Background

I work at Knock, where we use Elixir (and of course Ecto) extensively. 

We recently found ourselves deep in the weeds debugging transaction performance bottlenecks in our our AWS Aurora Postgres DB. During this, we observed that Aurora's query insights dashboard would lump all transactions under the `COMMIT` statement used to execute the transaction. What we really wanted were granular insights into performance for each query within the transaction. We couldn't make that work. 

So instead, we turned to a fairly simple hack. We started prepending the `COMMIT` statements with SQL comments, and we built out a simple macro that generates a comment based on the caller location. Doing this forced Aurora's query insights to enumerate performance by transaction origin.

Making this work was actually dead simple, thanks to Ecto's and Postgrex's consistent API design. We just passed a custom option down the call stack and pulled it out at the layer in Postgrex that actually issues the `COMMIT` statements.

You can see the short diff with the change on our fork here: https://github.com/elixir-ecto/postgrex/compare/v0.17.4...knocklabs:postgrex:knock-transaction-commit-comment-injection

We also have just published a blog post explaining our journey here in more detail: https://knock.app/blog/hacking-postgresql-driver.

### Proposal

We have found this little hack immensely useful, so much so that we'd like to keep it around in our codebase forever. But, we don't love that we're now on a tiny fork of Postgrex to make this work.

So, I'm here to propose upstreaming this change. 

I realize there are a lot of open questions we didn't need to consider with our little hack (e.g. sanitizing input, how widely this option should be made available). 

Lastly, if the team here thinks this behavior doesn't belong within Postgrex/Ecto as a first-class option, we may instead consider formalizing this in a package akin to Google's sqlcommenter SDK extensions.

Thanks!

Billy

[José Valim]

I am fine with adding a commit_comment option to Postgrex, as a way to get started. :+1: PR welcome! Thanks for sharing your journey.

--
You received this message because you are subscribed to the Google Groups "elixir-ecto" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elixir-ecto...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/7fc6dce9-c238-430f-9803-3a4a74e5f172n%40googlegroups.com.