Re: Allowing implicit 'text' -> xml|json|jsonb (was: PL/pgSQL 2)

On 09/05/2014 12:04 AM, Pavel Stehule wrote:
>
> But some missing casts are well - I found lot performance issues based
> on using wrong data types - integers, dates in text column.

Of course! That's why the default implicit casts were removed, and for
good reason. I'm only talking about a narrow class of a few specific types.

I think maybe a _few_ types need to be implicitly convertable from text,
but that's about it.

text -> jsonb
text -> json
text -> xml
text -> hstore
text -> uuid
text -> (user defined enum)

... mainly because otherwise app devs need frustrating workarounds (or
giving up on the PostgreSQL native types and just using 'text' columns),
and because in all these cases PostgreSQL will validate the input.

I've raised this before in other threads:

http://www.postgresql.org/message-id/EDDA5C6D-77E3-4C56-B33B-277E7FB32A12@hub.org

http://www.postgresql.org/message-id/CACTajFZ8+hg_kom6QiVBa94Kx9L3XUqZ99RdUsHBFkSb1MoCPQ@mail.gmail.com

... even from ages ago:

http://www.postgresql.org/message-id/4CFDAEE0.10106@postnewspapers.com.au

It's easy to object to this on type-purist grounds, but from a pragmatic
real-users point of view what we currently do is outright painful, and
unless we can go and fix every language binding, every query generator,
every ORM, etc to handle things just how PostgreSQL expects, some
compromise may be warranted.

It's easy to dismiss the problem by saying "pass 'unknown' typed
literals via your language binding". That even works if you're willing
to jump through some hoops and are using raw JDBC. Good luck doing that
via EclipseLink, Hibernate, ActiveRecord, SQLAlchemy, MyBatis, Django
ORM, or any of the things people use to talk to PostgreSQL on a day to
day basis though.

Right now it's really painful to use some of PostgreSQL's best features
without hacking around the type system by manually creating implicit
casts. Another option is to work around it by completely removing the
benefit of the strict casting even when it's obviously right (e.g.
refusing to cast text to date) with the JDBC connection parameter
stringtype=unknown .

I'd like to get rid of the need for users to add possibly-buggy custom
casts or bypass type checking of text types, by relaxing the casts where
appropriate.

Here's a partial collection of real world user complaints I've seen
about this issue, in addition to the links above.

http://stackoverflow.com/q/20339580/398670
http://stackoverflow.com/q/15974474/398670
http://stackoverflow.com/q/17310219/398670
http://stackoverflow.com/q/14858783/398670

Here's an example of someone working around it by passing all strings as
'unknown':

http://stackoverflow.com/q/12050945/398670

A workaround someone had to do with an ETL tool:

http://stackoverflow.com/q/24038287/398670

For uuid:

http://stackoverflow.com/q/13346089/398670

Someone trying to handle it portably:

http://stackoverflow.com/q/22242630/398670

The kind of work you need to work around PostgreSQL's strictness with enums:

http://stackoverflow.com/q/7603500/398670
http://stackoverflow.com/q/851758/398670
http://stackoverflow.com/q/10898369/398670
http://stackoverflow.com/q/14884955/398670
http://stackoverflow.com/q/10898369/398670

... and that's just what I can find in a few minutes' searching on one site.

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