Clojurians Log v2

Oh, this is exactly the issue that I'm dealing with: https://github.com/metabase/metabase/issues/16116 @igrishaev Have you had to deal with stringtype in PG2? Do you have any thoughts on why the default is VARCHAR in JDBC and not UNSPECIFIED which would be much closer to when you run queries with inline values?

Just remembered that I have had to deal with it before, just for a different reason. SELECT * WHERE some_enum = 'some_enum_val' works in psql just fine but fails and requires an explicit cast to the enum (or, I assume, stringtype=unspecified connection parameter) in JDBC.

Ugh. Found a downside of stringtype=unspecified. Queries that use jsonb_build_object($1 ...) will fail with ERROR: could not determine data type of parameter $1.

Hi! Actually, before this moment, I never heard about this setting (stringtype). It's a feature of JDBC by itself, Postgres knows nothing about it.

If briefly, when you pass data to Postgres, you encode them and specify the integer OID of a type. There is a special 0 value meaning "try to infer the type for me".

The case you shared in the beginning is quite weird because when you execute a prepared statement, Postgres parses it and returns exact types of all future columns and, what is important, types of input parameters. If the username column is of a type text or varchar , the client will have this information. So when you pass a string "" , it will be encoded according to the OID of a type gotten from the server

Indeed - that's the problem. An explicit TEXT type works differently from not providing a type - even in not prepared (static? I'll call them static) queries.

I see, there is a special citext type for email, let me check something

I haven't tried this extension, but will add it into a todo list. So far, according to the code, it won'g be an issue for Pg2. Namely, if your citext type has OID=100500, the client will use a special Unsupported encoder, which will send the string payload as-is with the OID=100500. Thus, Postgres won't throw an error

if your citext type has OID=100500, the client will use a special Unsupported encoder

Here is the source code: https://github.com/igrishaev/pg2/blob/master/pg-core/src/java/org/pg/processor/Unsupported.java You may see that for text encoding, any String value is passed as-is, so you only need to compose a proper text representation of a SQL value. For binary encoding, it might be either a string, a byte buffer, or a byte array

(-> (hh/select :username)
    (hh/from :users)
    (hh/where [:= :username "p1himik@gmail.COM"]))

["select username from users where username = ?" "p1himik@gmail.COM"]

{:prepared-statement-id "12345"
 :columns [[{:name "username" :oid 100500}]]
 :params [{:oid 100500}]}

<statement-id><OID><PAYLOAD><OID><PAYLOAD>...

Where <OID> is 100500, and <PAYLOAD> is a sequence of bytes. The client needs to know how to encode the string "" into a type 100500

In PG2, if the client doesn't know this, it will send something like this

<100500><some@email.com>

Which is kinda strange because usually Postgres returns errors when you pass a wrong type.

Right, that's straightforward. JDBC doesn't make workarounds - it's just that by default every string becomes TEXT and with stringtype=unspecified every string becomes, well, of unspecified type. But what about function arguments when there's an overload?

I may guess the following: you pass string which has upper-case characters. The OID is 100500 but jdbc sends it as VARCHAR (which is 25 or something like this). Postgres compares citext column value with the varchar value you passed. They are not equal due to the registry, and thus no result

In other words, a citext = text comparison is case-sensitive - yes, that was my assumption. And citext = unspecified is case-insensitive if unspecified can be turned into citext.

I believe it will work if you make query like

select ... from users where username = to_citext(?)

or even such

where username = (?::text)::citext

Of course, I can cast things. But that's beside the point, especially given that my initial intent is to make citext behave in a true case-insensitive manner, unless asked otherwise. And by default, JDBC "asks otherwise". Hence why stringtype=unspecified. But as I mentioned, it results in a different failure - unknown type error when there's a function overload.

Well, that's why I started pg2: type mapping between a database backend and Java is quite complex. And JDBC doesn't allow to tweak this.

So in PG, if I have SELECT f(?), f has overloads, and ? is bound to unsupported, what will happen? An exception?

> And JDBC doesn't allow to tweak this. next.jdbc does allow it, so at least there I'm content. :)

Perhaps it will return an error saying "I could not infer the proper type"

Right, so then in terms of at least built-in types and type conversions so far it seems that PG2 and JDBC with stringtype=unspecified behave identically and there's no magic that would make a prepared query behave like a static one.

but you can check it with pure psql :

prepare foo as select func(?);

Yes I believe. I've checked the docs and it looks like when it's unspecified , JDBC sends OID=0 and the string payload

and postgres tries to parse that text payload into the OID=100500

The difference with Pg2 is, it will send OID=100500 and the string

It does error out, yes:

=# prepare foo as select json_build_object($1, 1);
ERROR:  could not determine data type of parameter $1

which is slightly better for Postgres because it doesn't need to guess

Yes I know that JDBC has some tricks for this, for example, if you run this in JDBC

["select ? as foo", 1]

but PG2 won't do this. It completely relies on the server

So what will PG2 do in the case of that simple query? Error out?

no-no, it will work but the input type of param1 will be text

and if you pass 1, it will say "text is required"

Well yes, that's what I meant - when I'm passing 1 to something that implicitly expects TEXT. JDBC will also error out but only if the TEXT type is explicit. So SELECT ? will work with numbers, but SELECT upper(?) will fail.

With select UPPER($1) as foo , the type of $1 is resolved with no issues

Server returns typeOid=25 which is TEXT

But 1 is passed as $1?.. The type is resolved, yes. But the actual value has an incompatible type - both in JDBC and, I assume, in PG2.

Sorry, I didn't get it?

You set the context - ["select ? as foo", 1]. In that context, according to you, PG2 will say "text is required". I assume it means that there will be an exception. And given that in select UPPER($1) as foo the type is also text, the query with $1 bound to 1 will also fail with the same "text is required", right?

yes, true in both cases

in the first case, the $1 param is text because it cannot be inferred from SQL. In the second case, it was inferred from the UPPER function

But JDBC has a trick for the first case. It will coerce ? to the integer type

> the $1 param is text because it cannot be inferred from SQL. Why text specifically and not some unknown type? > But JDBC has a trick for the first case. It will coerce ? to the integer type Are you sure it's JDBC that does that? After all, I can PREPARE foo as SELECT $1; and then EXECUTE foo(1); in plain psql just fine.

Postgres API allows to override the default OIDs for parameters when parsing a prepared statement. So it's like "oh you passed an integer? Let param1 will be of OID=23" (int4)

It's a feature of Postgres: in select ? , the first param gets the TEXT type.

I haven't checked the source code but I believe it's a corner case or something.

> Are you sure it's JDBC that does that? Yes I am, because I was pretty surprised by this. And a guy who works on JDBC PG driver mentioned this once

Right, just checked in pg_prepared_statements. Surprising indeed. So it seems then that PG2 is even more strict with types, and more removed from static queries than JDBC...

If you have your REPL open, please try it:

(jdbc/execute! db ["select ? as foo", 42])

Of course, I know that. My overall concern is not about not being able to do something. It's about running an implicitly prepared query behaving differently from running a static query. So select * from t where x = 'a' can return different results than {:select :* :from :t :where [:= :x "a"]}, and that's far from obvious. And there doesn't seem to be a way to make it behave identically, regardless of the driver. Although it feels that it should be possible anyway, but maybe that would require changing PostgreSQL itself, dunno.

It always happens when you involve custom types, I mean those that come from extensions. Types are complex, and their mapping is even more complex...

But it's the case with the built-in types as well - I can't even prepare a statement with json_build_object.

The mapping of custom types might be complex, and yet PostgreSQL is able to figure it out based on a static query. In principle, a prepared query with parameters has the exact same info already. So there should be no reason for why an identical behavior must be unachievable. But of course there might be a pragmatic reason for why the identical behavior isn't implemented.

In any case, thanks for the discussion. :) Just in case, I wasn't trying to hint that any change in PG2 is necessary, I was just trying to understand how exactly things work and why. Well, and complain about the default stringtype value which makes things fail implicitly.