Postgres UUID difficulties in Lucee

dev
1

Greetings,

My dev team is migrating our database for our Lucee site from Microsoft SQL Server to PostgresSQL, and we’ve been hit by a rather large change in behavior with how UUIDs handled versus uniqueidentifiers in SQL Server.

Postgres returns java.util.UUID objects in each column value as opposed to a string with SQL Server. Having a complex object instead of a simple string is a definitely not ideal.

My best ideas are:

  • rewrite all queries with explicit casts to varchar
  • call the toString() method on each returned value before use
  • make a minor tweak to the JDBC driver to cast to string in the resultset

Has anyone come up with a clean way to work around this sort of situation or have any ideas?

2

That’s a “feature” of the Postgres JDBC Driver.

I would think that if you can do the 3rd option then that would be the easiest migration solution. It’d be great if you could share it with others.

BTW – other types also have this issue, e.g. IP addresses.

3

Option #3 was fairly trivial to implement (just adding toString() to the calls to getUUID() in PgResultSet.java), but, though it should not come up often, I am not in love with needing to maintain that change. Maybe we’ll make a fork of it if others would like it.

Thanks for the tip on the IP address type. We do not currently make use of it, but it is something we’ll have to keep an eye on.

4

Your simplest solution, IMO though, is to use char(36) or text type for that column.

5

I was trying to avoid that solution due to the large number of records that use a UUID as an indexed value. Some of these tables are in the tens of millions of records and I feel that will lead to some gigantic indexes. It also complicates cases where the default value for some columns is a new UUID that is generated on insert.

I think that for now we’re going to start with the tweaked driver and see if we can revise the code to remove the need for it post migration.

Thanks for the help.