PG 8.3 RC1: UUID column and JDBC PreparedStatement column type

Silvio Bierman wrote:
> I am trying to convert an older PG database where we stored GUIDs by
> explicitly converting them to byte arrays before storing them in the DB
> to a database where we use the new UUID type supported by PG 8.3.
>
> I am having trouble setting such a value through a JDBC
> PreparedStatement because any stmt.setXXX(i,...) I have tried results in
> an error about a mismatch between type UUID and XXX.
>
> How can I get my PreparedStatement to work correctly?

Since there are no type casts for uuid, the only way I can see is to
use a string and explicitly cast this string (type "unknown") to uuid.

java.sql.PreparedStatement stmt =
conn.prepareStatement("UPDATE tab SET uid = ?::uuid WHERE ...");
stmt.setString(1, "a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11");
stmt.executeUpdate();

You would somehow have to convert your GUIDs to the format above.

Yours,
Laurenz Albe

Laurenz Albe wrote:
> Silvio Bierman wrote:
>> I am trying to convert an older PG database where we stored GUIDs by
>> explicitly converting them to byte arrays before storing them in the DB
>> to a database where we use the new UUID type supported by PG 8.3.
>>
>> I am having trouble setting such a value through a JDBC
>> PreparedStatement because any stmt.setXXX(i,...) I have tried results in
>> an error about a mismatch between type UUID and XXX.
>>
>> How can I get my PreparedStatement to work correctly?
>
> Since there are no type casts for uuid, the only way I can see is to
> use a string and explicitly cast this string (type "unknown") to uuid.
>
> java.sql.PreparedStatement stmt =
> conn.prepareStatement("UPDATE tab SET uid = ?::uuid WHERE ...");
> stmt.setString(1, "a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11");
> stmt.executeUpdate();
>
> You would somehow have to convert your GUIDs to the format above.
>
> Yours,
> Laurenz Albe

Hello Laurens,

Thanks for the reply. I was hoping that one of the external JDBC types
could automatically be mapped/cast to GUID.

Unfortunately, changing the SQL is not an option since the application
runs on multiple database backends including Oracle, SQLServer, MySQL
and PostgreSQL. The only back-end specific twist I can do is on the
generic PreparedStatement since for each applicable type all DB code
goes througgh an adapter call like

adapter.setXXX(PreparedStatement stmt,int idx,XXX value)

which in this case actually looks like

adapter.setGUID(PreparedStatement stmt,int idx,String value)

since we represent GUID values as Strings internally.

Can I define an implicit cast in PostgreSQL?

Kind regards,

Silvio Bierman

Silvio Bierman wrote:
> Can I define an implicit cast in PostgreSQL?

Sure, with the CREATE CAST statement.

You'd have to define the function in C, e.g. as follows:

--- begin strtouuid.c ---
#include "postgres.h"
#include "fmgr.h"
#include "utils/builtins.h"

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

PG_FUNCTION_INFO_V1(strtouuid);

extern Datum strtouuid(PG_FUNCTION_ARGS);

Datum strtouuid(PG_FUNCTION_ARGS) {
VarChar *arg;
char *str;

arg = PG_GETARG_TEXT_PP(0);

str = palloc(VARSIZE(arg) - VARHDRSZ + 1);
memcpy(str, VARDATA(arg), VARSIZE(arg) - VARHDRSZ);
str[VARSIZE(arg) - VARHDRSZ] = '\0';

PG_RETURN_DATUM(DirectFunctionCall1(uuid_in, (Datum)str));
}
--- end strtouuid.c ---

The source for the SQL file that will create your cast is:

--- begin strtouuid.sql.in ---
CREATE OR REPLACE FUNCTION
public.strtouuid(character varying) RETURNS uuid
AS 'MODULE_PATHNAME', 'strtouuid'
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT;

DROP CAST IF EXISTS (character varying AS uuid);

CREATE CAST (character varying AS uuid)
WITH FUNCTION public.strtouuid(character varying)
AS IMPLICIT;
--- end strtouuid.sql.in ---

You can use the following Makefile:

--- begin Makefile ---
MODULES=strtouuid
DATA_built=strtouuid.sql
CPPFLAGS=-O2

PGXS := $(shell pg_config --pgxs)
include $(PGXS)
--- end Makefile ---

Yours,
Laurenz Albe

Laurenz Albe wrote:
> Silvio Bierman wrote:
>> Can I define an implicit cast in PostgreSQL?
>
> Sure, with the CREATE CAST statement.
>
> You'd have to define the function in C, e.g. as follows:
>
> --- begin strtouuid.c ---
> #include "postgres.h"
> #include "fmgr.h"
> #include "utils/builtins.h"
>
> #ifdef PG_MODULE_MAGIC
> PG_MODULE_MAGIC;
> #endif
>
> PG_FUNCTION_INFO_V1(strtouuid);
>
> extern Datum strtouuid(PG_FUNCTION_ARGS);
>
> Datum strtouuid(PG_FUNCTION_ARGS) {
> VarChar *arg;
> char *str;
>
> arg = PG_GETARG_TEXT_PP(0);
>
> str = palloc(VARSIZE(arg) - VARHDRSZ + 1);
> memcpy(str, VARDATA(arg), VARSIZE(arg) - VARHDRSZ);
> str[VARSIZE(arg) - VARHDRSZ] = '\0';
>
> PG_RETURN_DATUM(DirectFunctionCall1(uuid_in, (Datum)str));
> }
> --- end strtouuid.c ---
>
> The source for the SQL file that will create your cast is:
>
> --- begin strtouuid.sql.in ---
> CREATE OR REPLACE FUNCTION
> public.strtouuid(character varying) RETURNS uuid
> AS 'MODULE_PATHNAME', 'strtouuid'
> LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT;
>
> DROP CAST IF EXISTS (character varying AS uuid);
>
> CREATE CAST (character varying AS uuid)
> WITH FUNCTION public.strtouuid(character varying)
> AS IMPLICIT;
> --- end strtouuid.sql.in ---
>
> You can use the following Makefile:
>
> --- begin Makefile ---
> MODULES=strtouuid
> DATA_built=strtouuid.sql
> CPPFLAGS=-O2
>
> PGXS := $(shell pg_config --pgxs)
> include $(PGXS)
> --- end Makefile ---
>
> Yours,
> Laurenz Albe

Hello Laurenz,

Thanks a lot, I tried defining an implicit cast in terms of a select
that does the conversion but ended up with an infinite recursion, off
course :-(

This looks great but I have never added external C code to PG and am
afraid to compromise server stability. For instance, I see a memory
allocation call 'str = palloc(...)' and it is not immediately clear to
me where that memory will be freed.

Will this work on Windows as well?

And finally: since Postgresql supports implicit string to UUID
conversions internally and only the JDBC driver does not seem to be
aware of this, does that cast not already exist?

Kind regards,

Silvio Bierman

Silvio Bierman wrote:
> Thanks a lot, I tried defining an implicit cast in terms of a select
> that does the conversion but ended up with an infinite recursion, off
> course :-(
>
> This looks great but I have never added external C code to PG and am
> afraid to compromise server stability. For instance, I see a memory
> allocation call 'str = palloc(...)' and it is not immediately clear to
> me where that memory will be freed.

Memory allocated by palloc() will be freed when the transaction ends,
that is usually after the SQL statement is completed.

> Will this work on Windows as well?

Ugh, Windows.
Yes, it will work, but you'd need to get a build environment for
Windows, which is quite a hassle.

> And finally: since Postgresql supports implicit string to UUID
> conversions internally and only the JDBC driver does not seem to be
> aware of this, does that cast not already exist?

The error messages are not from JDBC, but from the database server.
There is no typecast from any data type to uuid.

All that PostgreSQL can do is convert string literals to uuid.

You can make use of this by using the data type
org.postgresql.util.PGobject, which is a general class used to
represent data types unknown to JDBC.

You can define a helper class:

public class UUID extends org.postgresql.util.PGobject {
public static final long serialVersionUID = 668353936136517917L;
public UUID(String s) throws java.sql.SQLException {
super();
this.setType("uuid");
this.setValue(s);
}
}

Then the following piece of code will succeed:

java.sql.PreparedStatement stmt =
conn.prepareStatement("UPDATE t SET uid = ? WHERE id = 1");
stmt.setObject(1, new UUID("a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11"));
stmt.executeUpdate();

Will that help you?

Yours,
Laurenz Albe

Laurenz Albe wrote:
> Silvio Bierman wrote:
>> Thanks a lot, I tried defining an implicit cast in terms of a select
>> that does the conversion but ended up with an infinite recursion, off
>> course :-(
>>
>> This looks great but I have never added external C code to PG and am
>> afraid to compromise server stability. For instance, I see a memory
>> allocation call 'str = palloc(...)' and it is not immediately clear to
>> me where that memory will be freed.
>
> Memory allocated by palloc() will be freed when the transaction ends,
> that is usually after the SQL statement is completed.
>
>> Will this work on Windows as well?
>
> Ugh, Windows.
> Yes, it will work, but you'd need to get a build environment for
> Windows, which is quite a hassle.
>
>> And finally: since Postgresql supports implicit string to UUID
>> conversions internally and only the JDBC driver does not seem to be
>> aware of this, does that cast not already exist?
>
> The error messages are not from JDBC, but from the database server.
> There is no typecast from any data type to uuid.
>
> All that PostgreSQL can do is convert string literals to uuid.
>
> You can make use of this by using the data type
> org.postgresql.util.PGobject, which is a general class used to
> represent data types unknown to JDBC.
>
> You can define a helper class:
>
> public class UUID extends org.postgresql.util.PGobject {
> public static final long serialVersionUID = 668353936136517917L;
> public UUID(String s) throws java.sql.SQLException {
> super();
> this.setType("uuid");
> this.setValue(s);
> }
> }
>
> Then the following piece of code will succeed:
>
> java.sql.PreparedStatement stmt =
> conn.prepareStatement("UPDATE t SET uid = ? WHERE id = 1");
> stmt.setObject(1, new UUID("a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11"));
> stmt.executeUpdate();
>
> Will that help you?
>
> Yours,
> Laurenz Albe

Hello Laurenz,

Yes, that is great! I will try that immediately. Thanks a bundle,
somehow I think this is not anywhere in the documentation.

About Windows: our production (ASP) environment is a bunch of Linux
boxes running PG but we have customers running separate installations on
private servers, some of them running PG on Windows.
Apart from that we mainly develop on laptops running Vista (and some of
us dual-boot systems running Ubuntu as well).

Thanks again, this is exactly what I was looking for. The driver may add
more straightforward support later but having this I am fine.

Kind regards,

Silvio Bierman

Laurenz Albe wrote:
> Silvio Bierman wrote:
>> Thanks a lot, I tried defining an implicit cast in terms of a select
>> that does the conversion but ended up with an infinite recursion, off
>> course :-(
>>
>> This looks great but I have never added external C code to PG and am
>> afraid to compromise server stability. For instance, I see a memory
>> allocation call 'str = palloc(...)' and it is not immediately clear to
>> me where that memory will be freed.
>
> Memory allocated by palloc() will be freed when the transaction ends,
> that is usually after the SQL statement is completed.
>
>> Will this work on Windows as well?
>
> Ugh, Windows.
> Yes, it will work, but you'd need to get a build environment for
> Windows, which is quite a hassle.
>
>> And finally: since Postgresql supports implicit string to UUID
>> conversions internally and only the JDBC driver does not seem to be
>> aware of this, does that cast not already exist?
>
> The error messages are not from JDBC, but from the database server.
> There is no typecast from any data type to uuid.
>
> All that PostgreSQL can do is convert string literals to uuid.
>
> You can make use of this by using the data type
> org.postgresql.util.PGobject, which is a general class used to
> represent data types unknown to JDBC.
>
> You can define a helper class:
>
> public class UUID extends org.postgresql.util.PGobject {
> public static final long serialVersionUID = 668353936136517917L;
> public UUID(String s) throws java.sql.SQLException {
> super();
> this.setType("uuid");
> this.setValue(s);
> }
> }
>
> Then the following piece of code will succeed:
>
> java.sql.PreparedStatement stmt =
> conn.prepareStatement("UPDATE t SET uid = ? WHERE id = 1");
> stmt.setObject(1, new UUID("a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11"));
> stmt.executeUpdate();
>
> Will that help you?
>
> Yours,
> Laurenz Albe

Hello all,

I would like to report back my first findings using this solution. It
works like a charm. We have a generic piece of JDBC code that converts
databases from one backend to the other, based on an XML descriptor of
our database structure and some backend specific parameters like
connection strings etc. I worked this code into a new PG-based mode and
did a conversion of one of our test databases.

The results are quite promising. The first very informal application
level performance benchmarks indicate somewhere between 10 and 20
percent speedup in comparison with using bytea and packing GUIDs in 16
bytes ourselves.
But the drop in database size from ~800Mb to ~600Mb for this relatively
small database is very welcome (and will probably have something to do
with the speedup as well)!

Thanks again for this information.

Kind regards,

Silvio Bierman

Silvio Bierman wrote:
> I would like to report back my first findings using this solution. It
> works like a charm.

Good to hear; I'm glad we could find a solution.

You could try and implement a PGuuid type for the JDBC driver if you have
the time, I'm sure the JDBC driver developers would welcome it.

Yours,
Laurenz Albe

Laurenz Albe wrote:
> Silvio Bierman wrote:
>> I would like to report back my first findings using this solution. It
>> works like a charm.
>
> Good to hear; I'm glad we could find a solution.
>
> You could try and implement a PGuuid type for the JDBC driver if you have
> the time, I'm sure the JDBC driver developers would welcome it.
>
> Yours,
> Laurenz Albe

Hello Laurenz,

I am not sure I understand what a PGuuid type would do. Care to elaborate?

Kind regards,

Silvio Bierman