Thread

1. JPA + enum == Exception

   Marc G. Fournier <scrappy@hub.org> — 2012-12-17T23:01:41Z

   I'm trying to use enum's in a database, but the java guys are telling me that they are having problems with inserts … reading from the database isn't a problem, but there appears to be an issue with converting from string -> enum when saving it back again …
   
   they are using JPA2 / EJB + Eclipselink … on the jboss side, we are running the latest jdbc driver …
   
   considering postgresql has always supported enum and extending types, I think they might be missing something, but I can't think of what to suggest …
   
   Is there a doc online I can maybe point them to about doing this … ?
   
   
   
   
   

2. Re: JPA + enum == Exception

   Dave Cramer <pg@fastcrypt.com> — 2012-12-17T23:18:10Z

   Marc,
   
   Any chance you could get some example code ?
   
   Dave
   
   Dave Cramer
   
   dave.cramer(at)credativ(dot)ca
   http://www.credativ.ca
   
   
   
   On Mon, Dec 17, 2012 at 7:01 PM, Marc G. Fournier <scrappy@hub.org> wrote:
   
   >
   > I'm trying to use enum's in a database, but the java guys are telling me
   > that they are having problems with inserts … reading from the database
   > isn't a problem, but there appears to be an issue with converting from
   > string -> enum when saving it back again …
   >
   > they are using JPA2 / EJB + Eclipselink … on the jboss side, we are
   > running the latest jdbc driver …
   >
   > considering postgresql has always supported enum and extending types, I
   > think they might be missing something, but I can't think of what to suggest
   > …
   >
   > Is there a doc online I can maybe point them to about doing this … ?
   >
   >
   >
   >
   > --
   > Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
   > To make changes to your subscription:
   > http://www.postgresql.org/mailpref/pgsql-jdbc
   >
   

3. Re: JPA + enum == Exception

   Craig Ringer <craig@2ndquadrant.com> — 2012-12-18T00:20:43Z

   On 18/12/2012 7:01 AM, Marc G. Fournier wrote:
   > I'm trying to use enum's in a database, but the java guys are telling me that they are having problems with inserts … reading from the database isn't a problem, but there appears to be an issue with converting from string -> enum when saving it back again …
   >
   > they are using JPA2 / EJB + Eclipselink … on the jboss side, we are running the latest jdbc driver …
   You will have issues, and this is a real PITA. You need a custom type
   handler in EclipseLink to use the JDBC `setObject(...)` method to set
   the enum. If you use `setString(...)` as is the default when you use a
   string mapping, PgJDBC will send a parameter of type `text`, and
   PostgreSQL will complain that it cannot implicitly cast `text` to
   whatever your enum is.
   
   Creating an implicit cast from text to your enum type in the database
   will work around it.
   
   The root of the problem is that PostgreSQL is much too fussy about
   casting from `text` to other types.
   
   Please follow up with details if you want help:
   
   - Exact PgJDBC version, not just "the latest"
   - EclipseLink version
   - PostgreSQL version
   - EXACT TEXT OF THE ERROR MESSAGE YOU GET
   - The query/queries that resulted in the error mesage, as shown in the
   PostgreSQL logs. You might need to set `log_statement = 'all'` to
   capture them.
   
   -- 
   Craig Ringer http://www.2ndQuadrant.com/
   PostgreSQL Development, 24x7 Support, Training & Services
   
   
   
   

4. Re: JPA + enum == Exception

   Hudson, Derrick <dhudson@redcom.com> — 2012-12-18T14:25:50Z

   The project I am working on uses enums in PostgreSQL and JPA/Eclipselink.  Our entities use enums in the Java code as well.  We ran into type handling issues like you describe, and solved them by writing converters.  Eclipselink needs to be told how to convert between the PGobject instance used by the JDBC driver and the Java enum value (or String, if that's what you are using in the Java code).
   
   http://www.eclipse.org/eclipselink/api/2.4/org/eclipse/persistence/mappings/converters/Converter.html
   
   http://eclipse.org/eclipselink/documentation/2.4/jpa/extensions/a_converter.htm
   
   
   
   -----Original Message-----
   From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Marc G. Fournier
   Sent: Monday, December 17, 2012 6:02 PM
   To: pgsql-jdbc@postgresql.org
   Subject: [JDBC] JPA + enum == Exception
   
   
   I'm trying to use enum's in a database, but the java guys are telling me that they are having problems with inserts ... reading from the database isn't a problem, but there appears to be an issue with converting from string -> enum when saving it back again ...
   
   they are using JPA2 / EJB + Eclipselink ... on the jboss side, we are running the latest jdbc driver ...
   
   considering postgresql has always supported enum and extending types, I think they might be missing something, but I can't think of what to suggest ...
   
   Is there a doc online I can maybe point them to about doing this ... ?
   
   
   
   

5. Re: [JDBC] JPA + enum == Exception

   Tom Dunstan <pgsql@tomd.cc> — 2013-02-08T04:39:35Z

   > -----Original Message-----
   > From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Marc G. Fournier
   > I'm trying to use enum's in a database, but the java guys are telling me that they are having problems with inserts ...
   > reading from the database isn't a problem, but there appears to be an issue with converting from string -> enum when saving it back again ...
   
   This is interesting, it seems to be a difference between executing the
   sql directly and using a prepared statement:
   
   tomtest=# create type mood as enum ('happy', 'meh', 'sad');
   CREATE TYPE
   tomtest=# create table enumcast  (current_mood mood);
   CREATE TABLE
   tomtest=# insert into enumcast values ('sad');
   INSERT 0 1
   tomtest=# select * from enumcast ;
    current_mood
   --------------
    sad
   (1 row)
   
   
   That works ok, but when attempting to use a prepared statement:
   
       ps = con.prepareStatement("insert into enumcast values (?)");
       ps.setString(1, "meh");
       ps.executeUpdate();
   
   we get a
   
   org.postgresql.util.PSQLException: ERROR: column "current_mood" is of
   type mood but expression is of type character varying
     Hint: You will need to rewrite or cast the expression.
   
   Cue sad trombone. You can fix this with implicit casts using CREATE
   CAST, or an explicit cast in the query, but this shouldn't really be
   necessary for what is a basic use case for enums. In any case ORMs
   won't know how to do that without writing custom converters, which
   makes me sad. I had intended that ORMs could just treat enum fields as
   text fields basically and not have to care about the underlying
   implementation.
   
   Cc'ing hackers - why the difference here? I presume that the input
   function is getting triggered when the value is inline in the SQL, but
   not so when the statement is prepared. Should we consider creating an
   implicit cast from text to enums when we create an enum? Or is there
   some other way to get the expected behaviour here?
   
   Cheers
   
   Tom
   
   
   

6. Re: [HACKERS] JPA + enum == Exception

   Tom Lane <tgl@sss.pgh.pa.us> — 2013-02-08T04:55:22Z

   Tom Dunstan <pgsql@tomd.cc> writes:
   > ... That works ok, but when attempting to use a prepared statement:
   
   >     ps = con.prepareStatement("insert into enumcast values (?)");
   >     ps.setString(1, "meh");
   >     ps.executeUpdate();
   
   > we get a
   
   > org.postgresql.util.PSQLException: ERROR: column "current_mood" is of
   > type mood but expression is of type character varying
   >   Hint: You will need to rewrite or cast the expression.
   
   AFAIK this is just business as usual with JDBC: setString() implies that
   the parameter is of a string type.  It'll fall over if the type actually
   required is anything but a string.  (I'm no Java expert, but I seem to
   recall that using setObject instead is the standard workaround.)
   
   Enums are not suffering any special hardship here, and I'd be against
   weakening the type system to give them a special pass.
   
   			regards, tom lane
   
   
   

7. Re: [HACKERS] JPA + enum == Exception

   Tom Dunstan <tom@tomd.cc> — 2013-02-08T05:45:32Z

   Hi Tom!
   
   On 8 February 2013 15:25, Tom Lane <tgl@sss.pgh.pa.us> wrote:
   
   > AFAIK this is just business as usual with JDBC: setString() implies that
   > the parameter is of a string type.  It'll fall over if the type actually
   > required is anything but a string.  (I'm no Java expert, but I seem to
   > recall that using setObject instead is the standard workaround.)
   >
   > Enums are not suffering any special hardship here, and I'd be against
   > weakening the type system to give them a special pass.
   
   Yes, you can use setObject(1, "enumval", Types.OTHER). I was hoping
   that setString might work, as mapping java enum values to strings in
   the database is a very common ORM technique that is built into
   basically all major ORMs including all that support the JPA standard,
   and it leads to people using varchars instead of typesafe enums in
   their dbs. If setString worked, people could migrate their schemas to
   the typesafe versions without touching any code. Using setObject
   people need to write a custom converter in most of those systems, and
   configure its use for each enum that they have. This then also makes
   swapping database backends difficult (for people who care about that
   sort of thing), since the jdbc calls are now different for postgresql
   vs anything else.
   
   Anyway, if there's no nice way to do it in the backend without adding
   implicit casts, and you're not happy with the costs of that as a
   solution, I guess that's that. I guess I was hoping that a text value
   parameter to a prepared statement could be treated as input when the
   type didn't match, but I don't know if that's feasible, and I guess
   it's probably opening the door to confusing error messages when
   someone provides the wrong type accidentally.
   
   Thanks
   
   Tom
   
   
   

8. Re: [HACKERS] JPA + enum == Exception

   Dave Cramer <pg@fastcrypt.com> — 2013-02-08T11:33:15Z

   Tom,
   
   How would setString know that the enum is actually an enum ? setString only
   takes a string ?
   
   Dave Cramer
   
   dave.cramer(at)credativ(dot)ca
   http://www.credativ.ca
   
   
   On Fri, Feb 8, 2013 at 12:45 AM, Tom Dunstan <tom@tomd.cc> wrote:
   
   > Hi Tom!
   >
   > On 8 February 2013 15:25, Tom Lane <tgl@sss.pgh.pa.us> wrote:
   >
   > > AFAIK this is just business as usual with JDBC: setString() implies that
   > > the parameter is of a string type.  It'll fall over if the type actually
   > > required is anything but a string.  (I'm no Java expert, but I seem to
   > > recall that using setObject instead is the standard workaround.)
   > >
   > > Enums are not suffering any special hardship here, and I'd be against
   > > weakening the type system to give them a special pass.
   >
   > Yes, you can use setObject(1, "enumval", Types.OTHER). I was hoping
   > that setString might work, as mapping java enum values to strings in
   > the database is a very common ORM technique that is built into
   > basically all major ORMs including all that support the JPA standard,
   > and it leads to people using varchars instead of typesafe enums in
   > their dbs. If setString worked, people could migrate their schemas to
   > the typesafe versions without touching any code. Using setObject
   > people need to write a custom converter in most of those systems, and
   > configure its use for each enum that they have. This then also makes
   > swapping database backends difficult (for people who care about that
   > sort of thing), since the jdbc calls are now different for postgresql
   > vs anything else.
   >
   > Anyway, if there's no nice way to do it in the backend without adding
   > implicit casts, and you're not happy with the costs of that as a
   > solution, I guess that's that. I guess I was hoping that a text value
   > parameter to a prepared statement could be treated as input when the
   > type didn't match, but I don't know if that's feasible, and I guess
   > it's probably opening the door to confusing error messages when
   > someone provides the wrong type accidentally.
   >
   > Thanks
   >
   > Tom
   >
   >
   > --
   > Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
   > To make changes to your subscription:
   > http://www.postgresql.org/mailpref/pgsql-jdbc
   >
   

9. Re: [JDBC] JPA + enum == Exception

   Kevin Grittner <kgrittn@ymail.com> — 2013-02-08T14:21:38Z

   Tom Lane <tgl@sss.pgh.pa.us> wrote:
   
   >> when attempting to use a prepared statement:
   >
   >>     ps = con.prepareStatement("insert into enumcast values (?)");
   >>     ps.setString(1, "meh");
   >>     ps.executeUpdate();
   >
   >> we get a
   >
   >> org.postgresql.util.PSQLException: ERROR: column "current_mood"
   >> is of type mood but expression is of type character varying
   >>   Hint: You will need to rewrite or cast the expression.
   >
   > AFAIK this is just business as usual with JDBC: setString() implies that
   > the parameter is of a string type.  It'll fall over if the type actually
   > required is anything but a string.  (I'm no Java expert, but I seem to
   > recall that using setObject instead is the standard workaround.)
   
   Right.  It is spelled out pretty specifically in the JDBC spec:
   
   http://download.oracle.com/otn-pub/jcp/jdbc-4_1-mrel-spec/jdbc4.1-fr-spec.pdf
   
   Table B-2 governs setting PreparedStatement parameters with
   setString and similar methods, while tables B-4 and B-5 cover the
   setObject methods.  It is clearly not unusual for other vendors to
   extend the JDBC specification to make life easier for those writing
   ORMs, etc.; but the behavior of the current PostgreSQL JDBC driver
   is doing all that is required by the spec.
   
   > Enums are not suffering any special hardship here, and I'd be against
   > weakening the type system to give them a special pass.
   
   This is not entirely unrelated to the discussions about allowing
   broader use of automatic casting server-side.  It seems to me that
   on one side of the argument is the idea that strict typing reduces
   bugs and doesn't lead to problems with ambiguity, especially as
   things change; and on the other side the argument is that where no
   ambiguity exists we would make life easier for developers of
   applications or access tools if we relexed things beyond what the
   related specifications require, and that not doing so discourages
   adoption.  I think that all the same arguments apply here with
   equal force, on both sides of the issue.
   
   The problem with this debate has always been that both sides are
   completely right.  Those are always the toughest to resolve.  It
   comes down to which evils we tolerate to garner which benefits.  It
   seems that in such cases inertia tends to win.  I'm not so sure
   that it should.  An ideal solution would find some way to address
   the concerns of both sides, but so far that has eluded us when it
   comes to the type system.
   
   -Kevin
   
   
   

10. Re: [HACKERS] JPA + enum == Exception

    Kris Jurka <books@ejurka.com> — 2013-02-08T16:26:27Z

    
    On Fri, 8 Feb 2013, Tom Dunstan wrote:
    
    > On 8 February 2013 15:25, Tom Lane <tgl@sss.pgh.pa.us> wrote:
    > 
    > > AFAIK this is just business as usual with JDBC: setString() implies that
    > > the parameter is of a string type.  It'll fall over if the type actually
    > > required is anything but a string.  (I'm no Java expert, but I seem to
    > > recall that using setObject instead is the standard workaround.)
    > >
    > 
    > Yes, you can use setObject(1, "enumval", Types.OTHER). I was hoping
    > that setString might work, as mapping java enum values to strings in
    > the database is a very common ORM technique that is built into
    > basically all major ORMs including all that support the JPA standard,
    > and it leads to people using varchars instead of typesafe enums in
    > their dbs.
    
    The other workaround is to use the url parameter stringtype=unspecified to 
    have setString always bind to unknown instead of varchar, which then 
    shouldn't require any code changes.
    
    Kris Jurka
    
    
    
    

11. Re: [JDBC] JPA + enum == Exception

    Віталій Тимчишин <tivv00@gmail.com> — 2013-02-09T19:24:57Z

    > This is not entirely unrelated to the discussions about allowing
    > broader use of automatic casting server-side.  It seems to me that
    > on one side of the argument is the idea that strict typing reduces
    > bugs and doesn't lead to problems with ambiguity, especially as
    > things change; and on the other side the argument is that where no
    > ambiguity exists we would make life easier for developers of
    > applications or access tools if we relexed things beyond what the
    > related specifications require, and that not doing so discourages
    > adoption.  I think that all the same arguments apply here with
    > equal force, on both sides of the issue.
    >
    > The problem with this debate has always been that both sides are
    > completely right.  Those are always the toughest to resolve.  It
    > comes down to which evils we tolerate to garner which benefits.  It
    > seems that in such cases inertia tends to win.  I'm not so sure
    > that it should.  An ideal solution would find some way to address
    > the concerns of both sides, but so far that has eluded us when it
    > comes to the type system.
    >
    >
    As for me, "right way" would be to allow exactly same casting as when using
    literals. Because now there are a lot of complaints like "It's driver
    problem because it works in psql".
    
    Best regards, Vitalii Tymchyshyn
    

12. Re: [JDBC] JPA + enum == Exception

    Craig Ringer <craig@2ndquadrant.com> — 2013-02-12T07:36:23Z

    On 02/08/2013 12:55 PM, Tom Lane wrote:
    > AFAIK this is just business as usual with JDBC: setString() implies that
    > the parameter is of a string type.
    Well, it means that it's a type compatible with a java.lang.String .
    JDBC doesn't say much about the database-side type.
    
    It's my strong view that we should accept setString(...) for any
    string-like type, like xml, json, domains of text, and so on - or at
    least provide an easy, no-superuser-required way to tell Pg to do so.
    
    I've pushed a testcase to:
    
       
    https://github.com/ringerc/scrapcode/tree/master/testcases/postgresql/jdbc
    
    that you can run if you want to see/experiment with the JDBC behaviour.
    I'll attach a tgz of it to a follow-up mail.
    
    Notably, you'll see that setObject(position, "value") is insufficient;
    you need to explicitly invoke setObject with Types.OTHER.
    
    >   It'll fall over if the type actually
    > required is anything but a string.  (I'm no Java expert, but I seem to
    > recall that using setObject instead is the standard workaround.)
    Unfortunately, setObject(...) isn't a complete workaround; it doesn't
    know about PostgreSQL-specific types, and setObject(1, "value") won't
    work, you have to explicitly specify Types.OTHER, eg setObject(1,
    "{\"key\" : \"value\"}", Types.OTHER);
    
    More importantly, for many Java users there are layers on top of JDBC
    that expect the database to be moderately lenient about accepting
    java.lang.String arguments. The extreme strictness Pg imposes makes
    things hard because most of these tools don't provide ways to punch
    through the abstraction and specify database types - they haven't needed
    to, because other DBs let you implicitly cast to/from things that look
    like strings.
    
    PgJDBC could work around this by treating setString(...) and the
    two-argument setObject(...) as if they were setObject(..., Types.Other).
    However, this would risk breaking queries that currently rely on the
    explicit text type to resolve functions that would otherwise be
    ambiguous. The same issue may apply to making conversions for text-like
    types more lenient in the server, and it wouldn't help people who want to:
    
    @Entity
    public class MyJPAEntity {
        //....
    
        @Column
        private String jsonField;
    
    }
    
    ... in their JPA mappings backed by Hibernate/EclipseLink/etc. They
    really need a session-level, DB-level or user-level (ie: GUC) way to say
    "Let me cast implicitly between json/xml/etc and text".
    
    -- 
     Craig Ringer                   http://www.2ndQuadrant.com/
     PostgreSQL Development, 24x7 Support, Training & Services
    
    

13. Re: [HACKERS] JPA + enum == Exception

    Tom Lane <tgl@sss.pgh.pa.us> — 2013-02-12T15:50:44Z

    Craig Ringer <craig@2ndquadrant.com> writes:
    > On 02/08/2013 12:55 PM, Tom Lane wrote:
    >> AFAIK this is just business as usual with JDBC: setString() implies that
    >> the parameter is of a string type.
    
    > Well, it means that it's a type compatible with a java.lang.String .
    > JDBC doesn't say much about the database-side type.
    
    > It's my strong view that we should accept setString(...) for any
    > string-like type, like xml, json, domains of text, and so on - or at
    > least provide an easy, no-superuser-required way to tell Pg to do so.
    
    The difficulty I've got with that is that there are only two kinds of
    literal in SQL, namely numbers and strings, so that "it looks like a
    string" applies to absolutely every non-numeric type.  If we go down
    this road we'll end up allowing implicit casts from text to (at least)
    every non-numeric type, which will be pretty much fatal for type
    checking purposes.
    
    IIRC, there's already a hack to tell the JDBC driver to mark setString
    parameters as "unknown" rather than "text", which seems to me to be
    a much less dangerous way of getting the lenient behavior when you want
    it.
    
    			regards, tom lane
    
    
    

14. Re: [HACKERS] JPA + enum == Exception

    Craig Ringer <craig@2ndquadrant.com> — 2013-02-13T03:12:09Z

    On 02/12/2013 11:50 PM, Tom Lane wrote:
    > Craig Ringer <craig@2ndquadrant.com> writes:
    >
    >> It's my strong view that we should accept setString(...) for any
    >> string-like type, like xml, json, domains of text, and so on - or at
    >> least provide an easy, no-superuser-required way to tell Pg to do so.
    > 
    > The difficulty I've got with that is that there are only two kinds of
    > literal in SQL, namely numbers and strings, so that "it looks like a
    > string" applies to absolutely every non-numeric type.
    
    I absolutely agree with that part - we don't want to go converting
    *everything* from 'text' to whatever we feel like implicitly. The
    removal of those implicit casts was by and large a good thing.
    
    I'm focusing specifically on data types that make sense to handle as
    strings in client applications - types where there may be no universal,
    core, built-in data type for them or where handling them as strings in
    the client may be dramatically more efficient - like "json" and "xml".
    
    I'm *not* proposing a blanket implicit conversion. Those conversions
    were removed for good reasons. I just think the removal went a little
    too far and that a couple of them need to be added back in. I'm *not*
    arguing that we should implicitly convert "text" to anything and everything.
    
    Here's the rationale:
    
    Because of the growing trend toward declarative, type-based data
    mapping, it is no longer easy for many users to separate low-level
    database interaction from the higher level definitions of how the data
    model is represented and worked with in the application. Irrespective of
    whether these high level data mapping tools are a good thing or not, in
    practice the effect is that it's not easy to say "I'll store this JSON
    as a string in my app, and tell the DB it's an unknown type literal when
    doing database I/O with it so it knows it can cast it to its internal
    JSON type". You largely lose access to the JDBC layer, and while most
    JPA implementations and other tools offer ways to pierce the abstraction
    they can be clumsy, hard to find out about, poorly documented, and
    difficult. That's OK if you're doing something weird and special - but I
    don't think storing and retrieving json and xml values as strings rather
    than rich data types in the client falls into that category.
    
    Yes, I'm saying we should work around client issues where it has a low
    cost to us.
    
    We could say "Use a client that doesn't suck". That's fine; we're not
    selling database systems, so when the user says "no thanks, I'll use a
    database that doesn't suck instead" it doesn't directly hurt us.
    However, I'd prefer not to force that choice when we can fix the problem
    on our end with no negative impact on ourselves or users.
    
    I *really* don't want users to have to use memory- and cpu-hungry types
    from some XML or JSON support library when mapping database entities
    into the application model just to work around a type handling quirk
    from the interaction of the client library, Pg and PgJDBC. Particularly
    when the app (or this layer of it) in question might be just an
    intermediary that doesn't really care what's in the textlike field.
    
    We work around less-than-lovely quirks in operating systems, the SQL
    standard, other DBs, etc all the time. I'm proposing that we work around
    one in widely used clients like Hibernate and EclipseLink since it
    doesn't hurt us and it makes users' lives easier.
    
    > If we go down
    > this road we'll end up allowing implicit casts from text to (at least)
    > every non-numeric type, which will be pretty much fatal for type
    > checking purposes.
    
    I see what you're getting at and agree that this would be bad, but it's
    not what I'm arguing for.
    
    I specifically think that "json" and "xml" should be implicitly castable
    to/from text. Permitting this doesn't require changes to how Pg
    interprets literals, nor hacks in the JDBC driver.
    
    Hacking the JDBC driver to send all java.lang.String values as unknown
    is exactly what I want to avoid.
    
    I'd just tell people to:
    
      CREATE CAST (text AS xml) WITH FUNCTION xml(text) AS IMPLICIT;
      CREATE CAST (text AS json) WITHOUT FUNCTION AS IMPLICIT;
    
    but (a) the "xml" one won't work because a cast already exists; (b) it's
    superuser-only; and (c) it relies on implementation details that may change.
    
    In practice you need to, as superuser:
    
      UPDATE pg_catalog.pg_cast
      SET castcontext = 'i'
      WHERE casttarget = 'xml'::regtype
      AND (castsource = 'text'::regtype OR castsource = 'varchar'::regtype);
    
    then query pg_cast to see if a text to json cast already exists, update
    it if it does exist, and use the above CREATE CAST to create it if it
    doesn't.
    
    In other words, "blech".
    
    > IIRC, there's already a hack to tell the JDBC driver to mark setString
    > parameters as "unknown" rather than "text", which seems to me to be
    > a much less dangerous way of getting the lenient behavior when you want
    > it.
    
    I'll take a look. I never noticed one in the docs, but back when I was
    wrestling with this problem I don't think I dug into the PgJDBC sources.
    
    -- 
     Craig Ringer                   http://www.2ndQuadrant.com/
     PostgreSQL Development, 24x7 Support, Training & Services
    
    
    

15. Re: [HACKERS] JPA + enum == Exception

    Tom Dunstan <pgsql@tomd.cc> — 2013-07-05T07:51:03Z

    On 9 February 2013 02:56, Kris Jurka <books@ejurka.com> wrote:
    
    > The other workaround is to use the url parameter stringtype=unspecified to
    > have setString always bind to unknown instead of varchar, which then
    > shouldn't require any code changes.
    
    
    I just tried this with a new project using hibernate and postgres with an
    enum type . Unfortunately, the hibernate (3.6) enum type calls
    setObject(pos, value, Types.VARCHAR) rather than calling setString(pos,
    value), and that doesn't respect the stringtype property.
    
    What's the feeling here - should setObject(Types.VARCHAR) respect
    stringtype=unspecified? I don't know whether there are semantic differences
    between setString() and setObject(Types.VARCHAR) to know if that's naughty
    or not. It seems like the only way for me to use this version of hibernate
    with pgsql enums is to either change the driver or implement a custom user
    type and configure it everywhere. :(
    
    Happy to whip up a patch if there's consensus to change the driver.
    
    Cheers
    
    Tom
    

16. Re: [HACKERS] JPA + enum == Exception

    Andreas Joseph Krogh <andreak@officenet.no> — 2013-07-05T09:21:17Z

    <div>På fredag 05. juli 2013 kl. 09:51:03, skrev Tom Dunstan &lt;<a href="mailto:pgsql@tomd.cc" target="_blank">pgsql@tomd.cc</a>&gt;:</div>
    
    <blockquote style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
    <div dir="ltr">On 9 February 2013 02:56, Kris Jurka <span dir="ltr">&lt;<a href="mailto:books@ejurka.com" target="_blank">books@ejurka.com</a>&gt;</span> wrote:
    
    <div class="gmail_extra">
    <div class="gmail_quote">
    <blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
    <div class="im"><span style="color:rgb(34,34,34)">The other workaround is to use the url parameter stringtype=unspecified to</span></div>
    have setString always bind to unknown instead of varchar, which then<br>
    shouldn't require any code changes.</blockquote>
    
    <div> </div>
    
    <div style="">I just tried this with a new project using hibernate and postgres with an enum type . Unfortunately, the hibernate (3.6) enum type calls setObject(pos, value, Types.VARCHAR) rather than calling setString(pos, value), and that doesn't respect the stringtype property.</div>
    
    <div style=""> </div>
    
    <div style="">What's the feeling here - should setObject(Types.VARCHAR) respect stringtype=unspecified? I don't know whether there are semantic differences between setString() and setObject(Types.VARCHAR) to know if that's naughty or not. It seems like the only way for me to use this version of hibernate with pgsql enums is to either change the driver or implement a custom user type and configure it everywhere. :(</div>
    
    <div style=""> </div>
    
    <div style="">Happy to whip up a patch if there's consensus to change the driver.</div>
    
    <div style=""> </div>
    
    <div style="">Cheers</div>
    
    <div style=""> </div>
    
    <div style="">Tom</div>
    </div>
    </div>
    </div>
    </blockquote>
    
    <div> </div>
    
    <div>I've been using Hibernate for years with mapping Enums to varchar-columns which works fine. Remember to map them with</div>
    
    <div> </div>
    
    <div>@Column(name = &quot;column_name&quot;)</div>
    
    <div>@Enumerated(EnumType.STRING)</div>
    
    <div>private MyEnum enum = &lt;stuff&gt;</div>
    
    <div> </div>
    
    <div class="origo-email-signature">--<br>
    Andreas Joseph Krogh &lt;andreak@officenet.no&gt;      mob: +47 909 56 963<br>
    Senior Software Developer / CTO - OfficeNet AS - http://www.officenet.no<br>
    Public key: http://home.officenet.no/~andreak/public_key.asc</div>
    
    <div> </div>
    

17. Re: [HACKERS] JPA + enum == Exception

    Tom Dunstan <pgsql@tomd.cc> — 2013-07-05T09:47:22Z

    Hi Andreas
    
    On 5 July 2013 18:51, Andreas Joseph Krogh <andreak@officenet.no> wrote:
    
    > I've been using Hibernate for years with mapping Enums to varchar-columns
    > which works fine. Remember to map them with
    >
    
    I'm not trying to map them to varchar columns - I'm trying to map them to
    columns with postgresql enum types as created by CREATE TYPE, see e.g.
    http://www.postgresql.org/docs/9.2/static/datatype-enum.html. The built-in
    enum types are designed for that purpose, and perform better and more
    safely than using varchars.
    
    Mapping java enums to a typesafe and efficient version in the db was the
    main reason I did the work to add native enums to postgresql in the first
    place - I haven't had a chance to use it recently, and I'm a bit
    disappointed that it doesn't work out of the box. So I want to fix up
    whatever parts of the stack are in the way of making that work.
    
    Cheers
    
    Tom
    

18. Re: [HACKERS] JPA + enum == Exception

    Andreas Joseph Krogh <andreak@officenet.no> — 2013-07-05T09:55:57Z

    <div>På fredag 05. juli 2013 kl. 11:47:22, skrev Tom Dunstan &lt;<a href="mailto:pgsql@tomd.cc" target="_blank">pgsql@tomd.cc</a>&gt;:</div>
    
    <blockquote style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
    <div dir="ltr">Hi Andreas
    <div class="gmail_extra"> 
    <div class="gmail_quote">On 5 July 2013 18:51, Andreas Joseph Krogh <span dir="ltr">&lt;<a href="mailto:andreak@officenet.no" target="_blank">andreak@officenet.no</a>&gt;</span> wrote:
    
    <blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
    <div>I've been using Hibernate for years with mapping Enums to varchar-columns which works fine. Remember to map them with</div>
    
    <div> </div>
    </blockquote>
    
    <div> </div>
    
    <div style="">I'm not trying to map them to varchar columns - I'm trying to map them to columns with postgresql enum types as created by CREATE TYPE, see e.g. <a href="http://www.postgresql.org/docs/9.2/static/datatype-enum.html" target="_blank">http://www.postgresql.org/docs/9.2/static/datatype-enum.html</a>. The built-in enum types are designed for that purpose, and perform better and more safely than using varchars.</div>
    
    <div style=""> </div>
    
    <div style="">Mapping java enums to a typesafe and efficient version in the db was the main reason I did the work to add native enums to postgresql in the first place - I haven't had a chance to use it recently, and I'm a bit disappointed that it doesn't work out of the box. So I want to fix up whatever parts of the stack are in the way of making that work.</div>
    </div>
    </div>
    </div>
    </blockquote>
    
    <div> </div>
    
    <div>I'm sorry, I missread your post.</div>
    
    <div> </div>
    
    <div class="origo-email-signature">--<br>
    Andreas Joseph Krogh &lt;andreak@officenet.no&gt;      mob: +47 909 56 963<br>
    Senior Software Developer / CTO - OfficeNet AS - http://www.officenet.no<br>
    Public key: http://home.officenet.no/~andreak/public_key.asc</div>
    
    <div> </div>
    

19. Re: [HACKERS] JPA + enum == Exception

    Dave Cramer <pg@fastcrypt.com> — 2013-07-05T09:57:42Z

    I would think setObject should respect stringtype=unspecified. So I would
    be willing to accept this as a solution
    
    Dave Cramer
    
    dave.cramer(at)credativ(dot)ca
    http://www.credativ.ca
    
    
    On Fri, Jul 5, 2013 at 5:47 AM, Tom Dunstan <pgsql@tomd.cc> wrote:
    
    > Hi Andreas
    >
    > On 5 July 2013 18:51, Andreas Joseph Krogh <andreak@officenet.no> wrote:
    >
    >> I've been using Hibernate for years with mapping Enums to varchar-columns
    >> which works fine. Remember to map them with
    >>
    >
    > I'm not trying to map them to varchar columns - I'm trying to map them to
    > columns with postgresql enum types as created by CREATE TYPE, see e.g.
    > http://www.postgresql.org/docs/9.2/static/datatype-enum.html. The
    > built-in enum types are designed for that purpose, and perform better and
    > more safely than using varchars.
    >
    > Mapping java enums to a typesafe and efficient version in the db was the
    > main reason I did the work to add native enums to postgresql in the first
    > place - I haven't had a chance to use it recently, and I'm a bit
    > disappointed that it doesn't work out of the box. So I want to fix up
    > whatever parts of the stack are in the way of making that work.
    >
    > Cheers
    >
    > Tom
    >
    

20. Re: [HACKERS] JPA + enum == Exception

    Tom Dunstan <pgsql@tomd.cc> — 2013-07-07T04:09:16Z

    On 5 July 2013 19:27, Dave Cramer <pg@fastcrypt.com> wrote:
    
    > I would think setObject should respect stringtype=unspecified. So I would
    > be willing to accept this as a solution
    >
    
    OK, here's a pull request with a unit test to cover expected behaviour with
    different combinations of setObject and setString, for inserts and queries.
    
    https://github.com/pgjdbc/pgjdbc/pull/68
    
    Interestingly, when I first tried this I tried using point as the type to
    test rather than an enum (so that I didn't have to create the enum type in
    the test), but it would fail when trying to select a row out in a query
    like "select * from thetable where p = ?" saying "operator does not exist:
    point = unknown". I presume that this due to multiple = operators for the
    point type at the db level, so the backend can't decide which type to
    create. I guess there's not much that we can do about that - if things are
    ambiguous then you need to be more specific, and of course we have a
    PGpoint java object anyway.
    
    Cheers
    
    Tom
    

21. Re: [HACKERS] JPA + enum == Exception

    Dave Cramer <pg@fastcrypt.com> — 2013-07-08T10:23:45Z

    Tom,
    
    Thanks!
    
    Dave Cramer
    
    dave.cramer(at)credativ(dot)ca
    http://www.credativ.ca
    
    
    On Sun, Jul 7, 2013 at 12:09 AM, Tom Dunstan <pgsql@tomd.cc> wrote:
    
    > On 5 July 2013 19:27, Dave Cramer <pg@fastcrypt.com> wrote:
    >
    >> I would think setObject should respect stringtype=unspecified. So I would
    >> be willing to accept this as a solution
    >>
    >
    > OK, here's a pull request with a unit test to cover expected behaviour
    > with different combinations of setObject and setString, for inserts and
    > queries.
    >
    > https://github.com/pgjdbc/pgjdbc/pull/68
    >
    > Interestingly, when I first tried this I tried using point as the type to
    > test rather than an enum (so that I didn't have to create the enum type in
    > the test), but it would fail when trying to select a row out in a query
    > like "select * from thetable where p = ?" saying "operator does not exist:
    > point = unknown". I presume that this due to multiple = operators for the
    > point type at the db level, so the backend can't decide which type to
    > create. I guess there's not much that we can do about that - if things are
    > ambiguous then you need to be more specific, and of course we have a
    > PGpoint java object anyway.
    >
    > Cheers
    >
    > Tom
    >