2024-04-01 sql | Clojure Slack Archive

sql

igrishaev 2024-04-01T13:25:30.389369Z

I'm using Next.JDBC with Postgres. Every time I query something that contains JSONb, I see some extra queries in the console:

SELECT n.nspname = ANY(current_schemas(true)), n.nspname, t.typname FROM pg_catalog.pg_type t JOIN pg_catalog.pg_namespace n ON t.typnamespace = n.oid WHERE t.oid = $1
parameters: $1 = '3802'

SELECT typinput='pg_catalog.array_in'::regproc as is_array, typtype, typname, pg_type.oid   FROM pg_catalog.pg_type   LEFT JOIN (select ns.oid as nspoid, ns.nspname, r.r           from pg_namespace as ns           join ( select s.r, (current_schemas(false))[s.r] as nspname                    from generate_series(1, array_upper(current_schemas(false), 1)) as s(r) ) as r          using ( nspname )        ) as sp     ON sp.nspoid = typnamespace  WHERE pg_type.oid = $1  ORDER BY sp.r, pg_type.oid DESC
parameters: $1 = '3802'

These are quite annoying. It looks like the driver tries to find some information about JSON. The queries return the following:

-[ RECORD 1 ]--------
?column? | t
nspname  | pg_catalog
typname  | jsonb

-[ RECORD 1 ]---
is_array | f
typtype  | b
typname  | jsonb
oid      | 3802

is there is a way to tweak the driver such that it's aware of this data?

p-himik 2024-04-01T14:16:22.579759Z

I've added a bunch of comments to your https://stackoverflow.com/questions/78255869/redundand-queries-to-the-pg-catalog-pg-type-table.

isak 2024-04-01T14:26:35.680729Z

If you pass this in for options (last argument), does it still do it?

{:builder-fn  next.jdbc.result-set/as-unqualified-maps
 :return-keys false}

igrishaev 2024-04-01T14:27:54.904019Z

I already use as-unqualified-maps, and yes, even with return-keys false, these queries are still there

😨 1

p-himik 2024-04-01T14:54:57.128439Z

I cannot reproduce the behavior at all. I only see those queries when I myself explicitly request metadata. Any other query doesn't do any extra work. Are you sure that it isn't something in your dev env that calls nav on something? If yes, can you create a minimal reproducible example?

p-himik 2024-04-01T15:01:03.091819Z

Never mind - that table with JSONB data that I tried querying had its data cached. I can see the behavior now.

igrishaev 2024-04-01T15:02:43.931859Z

oh, that's great you've managed to reproduce it. Meanwhile, I'm checking settings in one of the previous projects. We had a lot of json blobs in the db, but we didn't have such queries

p-himik 2024-04-01T15:04:57.234419Z

Ah, wait. I'm speaking too soon today. I did reproduce that behavior - exactly once. After that, the type information has been cached in the connection object.

igrishaev 2024-04-01T15:08:45.683419Z

That's right. But it means, it will be fetched again and again every time a new connection is opened. This is a bit annoying

p-himik 2024-04-01T15:10:22.242099Z

"A bit annoying" is hardly a reason to make significant changes to the driver. :) Also, one of the reasons I couldn't immediately see the behavior is that I'm using a connection pool, Hikari. That should alleviate that annoyance.

Clojurians Log v2

sql