Clojurians Log v2

Hm, the text on avoiding using e.g. _int4 in queries in DDL is a little light on the warning, and a little vague on exact directions, from what I read here: https://www.postgresql.org/docs/current/sql-createtype.html "It may be advisable to avoid using type and table names that begin with underscore. While the server will change generated array type names to avoid collisions with user-given names, there is still risk of confusion, particularly with old client software that may assume that type names beginning with underscores always represent arrays." When? Where? What if I align my usage of the names with "old client software" i.e. only use underscored names for internally generated array types? But all in all, I think I get the picture that scalar[] is just clearer/less ambiguous overall.

Yes, just not using _ and not even thinking in that direction is the best way forward. :)

Why the underscore?

idk, I'm a passer-by in this code

Maybe it's something we've set up

It seems to signify "array of". We also have examples of :_varchar

But that's an internal detail of PostgreSQL, AFAIK. Don't use it, use proper array types instead.

Yeah I don't understand why we use it, tbh.

I never saw it in documentation or stackoverflow/similar sites

only smallint[]

It is documented. But it's still not something a regular user should use. From the docs: > Writing code that depends on this convention is therefore deprecated.

Thanks 🙂

Who needs ChatGPT when there's @p-himik 😄

ahh... I guess because you can't do keywords with [] in them?

Maybe someone saw that as a limitation and thought :_varchar will be fine

What exactly is the query that uses _?

HoneySQL has some ways to create [], but maybe somehow they aren't suitable for your query.

The end query is something like

["insert into x (my_col) values (CAST(? AS _INT4))"
 "{1 2 3 4 5 6}"]

I can't use "smallint[]" directly, that makes honey confused

... but I can call (keyword "smallint[]")

let's see if honey likes that

Honey seems to. It becomes

["insert into x (my_col) values (CAST(? AS SMALLINT[]))"
 "{1 2 3 4 5 6}"]

but the query hasn't returned yet 😄

Is the "{1 2 3 4 5 6}" value only available as a string? Is it not available as a proper seq of numbers?

yeah it is

before creating the query

we have some code like [:cast (str "{" (str/join "," my-seq) "}") :_int4]

(That creates honey EDN syntax, before formatting to a prepared statement)

Wait, so you do have my-seq. Why not use it directly instead of creating a string?

(honey.sql/format {:insert-into [[:x :my-col]]
                   :values      [[[:array [1 2 3 4 5 6] :integer]]]})
=> ["INSERT INTO X(my_col) VALUES (ARRAY[?, ?, ?, ?, ?, ?]::INTEGER[])" 1 2 3 4 5 6]

I'll give it a go

If you want to avoid multiple parameters:

(honey.sql/format {:insert-into [[:x :my-col]]
                   :values      [[(into-array Integer/TYPE [1 2 3 4 5 6])]]})
=> ["INSERT INTO X(my_col) VALUES (?)" #object["[I" 0x14b9a1b2 "[I@14b9a1b2"]]

The :integer in the next to last query is needed to make sure you don't have just ARRAY[] when the seq is empty. For empty arrays, PostgreSQL must know the type of the array itself as it cannot be deduced from the values inside the array.

ok, my colleague returned and she had the idea to use :_int2 which seems to match smallint in the documentation, and that works. But I still want to try your thing

Yeah, I would definitely recommend to avoid :_int2 and other such things when there are alternatives. Especially when you have to manually construct a string only for PostgreSQL to destructure it later. What's even the point of using _ if there are all around better alternatives?

It works great with [[:array '(1 2 3 4) :integer] [:array '("hey" "my" "friend") :varchar]]! I'm a happy camper 😄

Also can use [...] instead of '(...).

I myself still prefer into-array, especially when there are many, many values. It's not fun to see a thousand ? in the logs where just one would do.

Oh, and it's amenable to caching, unlike plain seqs.

Ah, good point. I'll do some refactoring

there's the same amount of question marks

just (?) instead of (ARRAY[?]::VARCHAR[]) (so shorter, yes, but still 1-1 question marks)

how do you do (into-array STRING/TYPE ...?

ah, just "String"

No, there's not 1-1 question marks, there's 1-n question marks. I sent you two queries - one with six ?, the other with just one.

Integer is for boxed ints, Integer/TYPE is for primitive ints. Strings don't have that distinction.

Ah, but my attempt to do your code that produced a lot of ?'s didn't on my machine.

Can you send the exact code and the result?

lemme try

I stand corrected 🙂

I was confused. I had only one item in my seq, so of course there'd be only one ?.

Hm, but regarding using into-array as a means to have prettier logs, when I just print the formatted hsql, the numbers are unrecognizable. I mean, it probably looks fine in the db logs, but certainly not the prepared statement.

(-> (hsql-hlp/insert-into :x)
      (hsql-hlp/values [{:my-col [(into-array Integer/TYPE [1 2 3 4 5 6])]}])
      (hsql/format))
=> ["INSERT INTO x (my_col) VALUES ((?))" #object["[I" 0x1efeb66e "[I@1efeb66e"]]

In any case, thank you very much for the help, this was a day of Postgresql enlightenments for me 😄

Well, plain printing should probably not be used for logging. ;) I have a relatively complex query logging function that excises sensitive data, truncates arguments to avoid tens of thousands of items in logs, and expands arrays as well.