Re: UUID datatype

Hi,

I'm currently running:

PostgreSQL v8.3
JDBC4 v8.3-603

I'm wondering about the timeline or plans if any for JDBC driver support
of the recently added UUID datatype in PostgreSQL?

I haven't been able to check the JDBC v4 spec as the Sun server hosting
it was not available at the time I last checked, but I doubt that it is
part of the specification, as it is not in the JDBC v3 spec and it is
not mentioned in the differences between the two, so I assume any such
support would be as a custom datatype similar to the PGcircle datatype.
Additionally, I was not able to find anything in the JDBC driver
documentation regarding UUID support or in the driver API, and all that
I have been able to find on the Internet is work arounds, such as custom
methods on the DB end to convert string UUID representations into UUID
datatypes, as there is no implicit conversion by the DB.

As an increasing number of DB's support UUID's, or in the case of
Microsoft, their GUID, I thought I would pose the question. I searched
the mail archives and couldn't find anything matching, so hopefully this
is not a repeated question, as I have just arrived on this list.

Thanks,

Andy

On Sun, 17 Feb 2008, Andrew wrote:

> I'm wondering about the timeline or plans if any for JDBC driver support of
> the recently added UUID datatype in PostgreSQL?

There aren't any exact plans, but I was thinking we would map the pg uuid
type onto the java.util.UUID type. The only problem with that
is java.util.UUID only appeared in the 1.5 JDK, so we'd only be
able to support it for some driver versions. Do people think it's better
to always map it to a PGuuid class?

Kris Jurka

java.util.UUID and UUID in PG they are different things.
it's better to map THAT to a PGuuid class!

On Sat, Feb 16, 2008 at 11:52 PM, Kris Jurka <books(at)ejurka(dot)com> wrote:

>
>
> On Sun, 17 Feb 2008, Andrew wrote:
>
> > I'm wondering about the timeline or plans if any for JDBC driver support
> of
> > the recently added UUID datatype in PostgreSQL?
>
> There aren't any exact plans, but I was thinking we would map the pg uuid
> type onto the java.util.UUID type. The only problem with that
> is java.util.UUID only appeared in the 1.5 JDK, so we'd only be
> able to support it for some driver versions. Do people think it's better
> to always map it to a PGuuid class?
>
> Kris Jurka
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

Kris Jurka <books(at)ejurka(dot)com> writes:
> There aren't any exact plans, but I was thinking we would map the pg uuid
> type onto the java.util.UUID type. The only problem with that
> is java.util.UUID only appeared in the 1.5 JDK, so we'd only be
> able to support it for some driver versions. Do people think it's better
> to always map it to a PGuuid class?

Given that UUID is only in the latest PG version, it doesn't seem
unreasonable to support it only in newer JDKs too. Five years down
the road, not having mapped it to java.util.UUID will definitely
seem to have been a mistake, no?

regards, tom lane

On Sun, 17 Feb 2008, ntr wrote:

> java.util.UUID and UUID in PG they are different things.
> it's better to map THAT to a PGuuid class!
>

Could you be more clear on what the differences are? While the
java.util.UUID class specifically mentions the Leach-Salz variant, it
seems like it supports other versions as well.

Kris Jurka

Hi All,

This is probably noise to most people by now, so apologies if it is.

I have done some digging around this area, and the following are my
findings:

Using the JDBC 4 Driver via plain java.sql.* libraries, there is no
problem at all with the UUID datatype. I tested with both
java.sql.Statement and java.sql.PreparedStatement objects and String
UUID representations.

I also created a PGuuid object within the PG JDBC Driver to see what it
entailed, and that worked fine as well. However, in my mind, it is
totally superfluous. I can provide the source code if anyone sees value
in it, but I'm of the opinion that there really isn't any work to do in
the driver to support the datatype. Particularly while the UUID
datatype is missing from java.sql.Types, as any solution would be tied
to accessing this datatype via the Driver API rather than the java.sql.*
API, unless anyone can enlighten me on a solution.

The main reason I raised this in the first place was that I wasn't able
to get this to work in Hibernate. So with Hibernate, and various forums
I'd read, I had assumed that the UUID datatype wasn't working at all
through the Driver. However, as I have just described, I was incorrect
with this assumption.

To get Hibernate to work I needed to create a custom Hibernate type.
Additionally, the default Hibernate uuid.hex generator by Gavin King is
not correctly formatted, including when using the separator attribute,
so the DB rejects this format, which is understandable. So I also
created a custom uuid generator that uses Java's random uuid creator.
If there is interest and anyone is struggling with this, I can provide
further details. I see these issues as specific to Hibernate rather
than a problem with the JDBC Driver.

Now that I have worked through the solution, I have no issues if this
request is withdrawn from the wish list. Just cranky with myself that
it has taken two whole days to figure out the solution, three if I
include the initial question and waiting on the response...

Thanks, and you're doing a brilliant job,

Andy

On Sat, 16 Feb 2008, Kris Jurka wrote:

> On Sun, 17 Feb 2008, Andrew wrote:
>
>> I'm wondering about the timeline or plans if any for JDBC driver support of
>> the recently added UUID datatype in PostgreSQL?
>
> There aren't any exact plans, but I was thinking we would map the pg uuid
> type onto the java.util.UUID type. The only problem with that is
> java.util.UUID only appeared in the 1.5 JDK, so we'd only be able to support
> it for some driver versions.

I've committed code to CVS to support the UUID type. This is now the
first time there's a real distinction between the 1.4 and 1.5 JDK builds.
Previously I've been doing all JDBC3 releases with a 1.4 JDK which will
not support java.util.UUID. I don't plan on publishing the separate
JDBC3g (g stands for the generics JDK1.5 needs) build that is generated
from a 1.5 build because we already have enough release versions. So
unless there are serious objections, if people want UUID support they'll
need to use the JDBC 4 release or build from source.

Kris Jurka

Thanks Kris,

I'm appreciative of you having acted on this. I'm currently mapping it
to the sql OTHER datatype, overriding the JDBC driver and doing some
hocus-pocus magic with Hibernate for it to work in an ORM. I have no
immediate need of this update at present, having the workaround.
However, when the next release is published, I will play around with it
and see how JPA takes to it.

Cheers,

Andy

Kris Jurka wrote:
>
>
> On Sat, 16 Feb 2008, Kris Jurka wrote:
>
>> On Sun, 17 Feb 2008, Andrew wrote:
>>
>>> I'm wondering about the timeline or plans if any for JDBC driver
>>> support of the recently added UUID datatype in PostgreSQL?
>>
>> There aren't any exact plans, but I was thinking we would map the pg
>> uuid type onto the java.util.UUID type. The only problem with that
>> is java.util.UUID only appeared in the 1.5 JDK, so we'd only be able
>> to support it for some driver versions.
>
> I've committed code to CVS to support the UUID type. This is now the
> first time there's a real distinction between the 1.4 and 1.5 JDK
> builds. Previously I've been doing all JDBC3 releases with a 1.4 JDK
> which will not support java.util.UUID. I don't plan on publishing the
> separate JDBC3g (g stands for the generics JDK1.5 needs) build that is
> generated from a 1.5 build because we already have enough release
> versions. So unless there are serious objections, if people want UUID
> support they'll need to use the JDBC 4 release or build from source.
>
> Kris Jurka
> ------------------------------------------------------------------------
>
>
> No virus found in this incoming message.
> Checked by AVG - http://www.avg.com
> Version: 8.0.173 / Virus Database: 270.7.5/1698 - Release Date: 9/29/2008 7:25 PM
>
>

Where can I get your committed code?
>From hibernate CVS, postgresql connector CVS or ?

Kris Jurka wrote:
>
>
>
> On Sat, 16 Feb 2008, Kris Jurka wrote:
>
>> On Sun, 17 Feb 2008, Andrew wrote:
>>
>>> I'm wondering about the timeline or plans if any for JDBC driver support
>>> of
>>> the recently added UUID datatype in PostgreSQL?
>>
>> There aren't any exact plans, but I was thinking we would map the pg uuid
>> type onto the java.util.UUID type. The only problem with that is
>> java.util.UUID only appeared in the 1.5 JDK, so we'd only be able to
>> support
>> it for some driver versions.
>
> I've committed code to CVS to support the UUID type. This is now the
> first time there's a real distinction between the 1.4 and 1.5 JDK builds.
> Previously I've been doing all JDBC3 releases with a 1.4 JDK which will
> not support java.util.UUID. I don't plan on publishing the separate
> JDBC3g (g stands for the generics JDK1.5 needs) build that is generated
> from a 1.5 build because we already have enough release versions. So
> unless there are serious objections, if people want UUID support they'll
> need to use the JDBC 4 release or build from source.
>
> Kris Jurka
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>
>

--
View this message in context: http://www.nabble.com/UUID-datatype-tp15518545p22115729.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

On Thu, 19 Feb 2009, aznmedia wrote:

>
> Where can I get your committed code?

The UUID code was released in the 8.4dev-700 release available here:

http://jdbc.postgresql.org/download.html

The CVS repository is here:

http://jdbc.postgresql.org/development/cvs.html

Kris Jurka