+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14

PG 8.3 RC1: UUID column and JDBC PreparedStatement column type

  1. PG 8.3 RC1: UUID column and JDBC PreparedStatement column type

    Hello all,

    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?

    Kind regards,

    Silvio Bierman

  2. Re: 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

  3. Re: PG 8.3 RC1: UUID column and JDBC PreparedStatement column type

    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

  4. Re: PG 8.3 RC1: UUID column and JDBC PreparedStatement column type

    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

  5. Re: PG 8.3 RC1: UUID column and JDBC PreparedStatement column type

    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

  6. Re: PG 8.3 RC1: UUID column and JDBC PreparedStatement column type

    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

  7. Re: PG 8.3 RC1: UUID column and JDBC PreparedStatement column type

    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

  8. Re: PG 8.3 RC1: UUID column and JDBC PreparedStatement column type

    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

  9. Re: PG 8.3 RC1: UUID column and JDBC PreparedStatement column type

    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

  10. Re: PG 8.3 RC1: UUID column and JDBC PreparedStatement column type

    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

+ Reply to Thread
Page 1 of 2 1 2 LastLast