sql 2024-04-01 | Slack Archive

igrishaev13:04:30

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-himik14:04:22

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

isak14:04:35

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}

igrishaev14:04:54

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

😨 1

p-himik14:04:57

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-himik15:04:03

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

igrishaev15:04:43

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-himik15:04:57

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.

igrishaev15:04:45

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-himik15:04:22

"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.

2024-04-01

Channels