Re: PostgreSQL specific datatypes very confusing for beginners who use wrappers around JDBC

On 01/29/2014 05:20 AM, Tom Lane wrote:
> John R Pierce <pierce@hogranch.com> writes:
>> On 1/28/2014 12:11 PM, Merlin Göttlinger wrote:
>>> I don't know if this is just a beginner problem but at least in my
>>> opinion it is rather complicated to use the postgres specific types
>>> and features from JDBC and its wrappers.
>
>> these issues are entirely created by the wrappers you're using. like
>> most such, they are undoubtedly designed for a lowest common
>> denominator, and won't have support for database implementation specific
>> data types, just common stuff like strings, numbers.
>
> I think you can fix it by explicitly casting your placeholders, eg
> "?::macaddr".

You're both missing the point here, because you're thinking about it
like these people are hand-writing SQL with string literals, and can
just edit their SQL to insert some casts.

They aren't, and they can't. They're using generated SQL - and I
strongly suspect the vast majority of Pg users are, with Hibernate,
ActiveRecord, etc etc etc.

They're also not using literals. They're using bind parameters, because
they're sensible or because and their tools encourage or force them to
do so. When using bind params you don't have PostgreSQL's convenient
'unknown' literal pseudo-type. This works:

    CREATE TABLE mac(addr macaddr);

    INSERT INTO mac(addr) VALUES ('00:AB:CD:EF:01:23')

but what most frameworks and client drivers will do is the
protocol-level equivalent of:

    PREPARE insert_stmt(text) AS INSERT INTO mac(addr) VALUES ($1);

    EXECUTE insert_stmt('00:AB:CD:EF:01:23');

which will bail out with:

regress=> PREPARE insert_stmt(text) AS
          INSERT INTO mac(addr) VALUES ($1);

ERROR:  column "addr" is of type macaddr but expression is of type text

This is only a problem in the first place because PostgreSQL is much,
much stricter than anything else about typing of inputs while also
having lots of handy types nothing else has. Users want to be able to
use these types, but find it difficult and painful because PostgreSQL
rejects inputs for them because it's being painfully pedantic about a
few details.

I just want us to allow, by default, implicit casts FROM text (not TO
text) using the input function for all PostgreSQL's validated
non-standard types (and XML due to limited deployment of SQL/XML support
in client drivers).

So you should be able to write:

    INSERT INTO mac(addr) VALUES ('00:AB:CD:EF:01:23'::text)

I know that's not beautiful, pure typing behaviour, and it'd be nice if
we could just complain or refuse it. But really, the _in function will
parse and reject invalid input, and if it's valid input, why are we
complaining?


We should allow implicit casts, using input functions, for at least:

* ENUMs
* xml, json, hstore
* macaddr, cidr, inet
* point, circle, ...
* uuid

as these are all types that are subject to validation on input, and
aren't going to have direct client driver support.


Here are "solutions" that *won't* work:


"Just bind string parameters as 'unknown'"
----

You can bind params as 'unknown' and let the planner figure it out:

    PREPARE insert_stmt(unknown) AS INSERT INTO mac(addr) VALUES ($1);
    EXECUTE insert_stmt('00:AB:CD:EF:01:23');

but if a driver sends all string-type data from the client language as
'unknown', that will cause overloaded functions that accept 'text' or
'varchar' to fail when they used to succeed with a concrete text-typed
bind parameter.



"Just fix the framework":
----

You *can't* "just cast the input" when you're working via pretty much
anything except directly written SQL. I prefer to do that, but I'm in
the minority - more and more people are using query generators and ORMs.
To use json, xml, macaddr, inet, etc users need to write extensions to
their tools to teach them about those types and map them to language types.

Sometimes the framework is hidden behind more layers. For example,
PgJDBC is often used by Hibernate or EclipseLink, *via the JPA2 API*,
another layer that makes it even harder to implement custom type mappings.



"Create the casts"
----

Alternately, users have to figure out how to CREATE CAST - and that it's
possible. Users can CREATE CAST a weaker cast for the type, but:

* If there's already a cast for the type in the catalogs you may have to
hack the catalogs instead, as CREATE CAST will fail; and

* It's a convoluted process requiring wrapper functions for most simple
inputs - because you can't:
    CREATE CAST (text AS json) WITH FUNCTION json_in(cstring)
Instead you need a wrapper function that takes "text", even though text
is implicitly castable to cstring.

More importantly, you need to know about CREATE CAST, and figure all
this out. Which sucks for people who just want to use "json".


--
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services