honeysql 2023-03-21 | Slack Archive

slipset12:03:09

So at work, we do quite a bit of logging, and in that logging we add a trace-id so we can correlate log messages to the same request. This is not rocket science. It turns out however, that if we do

=> /* trace-id: 123456789 */ select * from foo;

from the psql console, that comment gets propagated to the logs as:

2023-03-21 11:47:23.146 UTC [29737] LOG:  duration: 36.834 ms  statement: /* trace-id: 123456789 */ select * from foo;

Which would then let us correlate (slow) queries to specific requests, which would again make it very much easier to debug performance problems. Because of how we’ve structured our code, this would be very easy to implement if honey-sql had a (hh/comment ) fn, so we could say something like:

(->> (hh/comment (str "trace-id: " id))
     (hh/select ..))

which would render out a comment before the actual statement. Is this already possible (and if not, would it be possible to add)?

seancorfield19:03:06

TBH, that sounds a bit more like something next.jdbc should be providing but it's an interesting question and certainly something that is easy to add to HoneySQL -- as you found with register-clause! 🙂 Is it (comments preceding SQL) an ANSI feature or something peculiar to PG?

slipset19:03:50

I don’t know if it’s ANSI or not.

slipset19:03:07

But, say I wanted to register :comments so that it is added before :insert , :update, :delete :with as well? do I just supply all those keywords as last args?

seancorfield19:03:42

Just pick the earliest one you need it to be before...

slipset19:03:57

So basically :with (and then honey will figure out that if :with is not present, it will go before whatever comes after :with)?

seancorfield19:03:02

(def ^:private default-clause-order
  "The (default) order for known clauses. Can have items added and removed."
  [;; DDL comes first (these don't really have a precedence):
   :alter-table :add-column :drop-column
   :alter-column :modify-column :rename-column
   :add-index :drop-index :rename-table
   :create-table :create-table-as :with-columns
   :create-view :create-materialized-view :create-extension
   :drop-table :drop-view :drop-materialized-view :drop-extension
   :refresh-materialized-view
   ;; then SQL clauses in priority order:
   :raw :nest :with :with-recursive :intersect :union :union-all :except :except-all
   :table
   :select :select-distinct :select-distinct-on :select-top :select-distinct-top
...

seancorfield19:03:01

Not sure whether you'd want this to be "before" raw/nest, but with sounds like a reasonable starting point?

slipset19:03:19

We’re not currently using :raw at top-level, and I don’t think we’re using :nest but we’re deffo using :with

slipset13:03:32

I guess something along the lines of

(honey.sql/register-clause! :comment
                      (fn [clause x]
                        [(str"/* " x " */")])
                      :select)

az16:03:38

Hi all. Wondering how I might achieve the following sql chunk:

ARRAY_AGG(DISTINCT (i.name)) FILTER (WHERE i.name IS NOT NULL)

I have this in honey, but can't figure out how to not use a aggregate function as explained in the docs

[[:array_agg 
  [:distinct :i.name] 
  [:filter {:where [:not= nil :i.name]}]]]

If I don't use something like :%count, the filter clause doesn't show up in the output. Any thoughts?

p-himik16:03:44

(sql/format {:select [[[:filter
                        [:array_agg [:distinct :i.name]]
                        {:where [:is-not :i.name nil]}]]]})
=> ["SELECT ARRAY_AGG(DISTINCT i.name) FILTER (WHERE i.name IS NOT NULL)"]

Documented at https://cljdoc.org/d/com.github.seancorfield/honeysql/2.4.980/doc/getting-started/sql-special-syntax-#filter-within-group

az16:03:12

Hi @U2FRKM4TW, thank you. I just don't see how to create a filter without an aggregate. My query doesn't work when I use Count

p-himik16:03:41

Not sure what you mean. What exactly do you want as an output?

az16:03:03

ARRAY_AGG(DISTINCT (i.name)) FILTER (WHERE i.name IS NOT NULL)

p-himik16:03:14

And how is it different from my code snippet?

az16:03:21

Not

ARRAY_AGG(DISTINCT (i.name)) COUNT(*) FILTER (WHERE i.name IS NOT NULL)

p-himik16:03:30

Read my code snippet again. :)

az16:03:55

I'm so sorry! I just saw the link to the docs

p-himik16:03:23

Yeah, :filter is a modifier on top of an aggregate. Not an argument.

az16:03:42

Thank you so much this is perfect!

👍 2

2023-03-21

Channels