 | |
#41
 
| |||
| |||
|
Re: PG 8.3 RC1: UUID column and JDBC PreparedStatement column type -
01-15-2008
, 10:34 AM
|
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. |
that is usually after the SQL statement is completed.
Quote:
|
Will this work on Windows as well? |
Yes, it will work, but you'd need to get a build environment for
Windows, which is quite a hassle.
Quote:
|
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? |
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
#42
| |||
| |||
|
|
Silvio Bierman <sbierman (AT) jambo-software (DOT) com> 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 |
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
#43
| |||
| |||
|
|
Silvio Bierman <sbierman (AT) jambo-software (DOT) com> 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 |
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
#44
| |||
| |||
|
|
Silvio Bierman <sbierman (AT) jambo-software (DOT) com> 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 |
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
#45
| |||
| |||
|
|
Silvio Bierman <sbierman (AT) jambo-software (DOT) com> 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 |
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
#46
| |||
| |||
|
|
Silvio Bierman <sbierman (AT) jambo-software (DOT) com> 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 |
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
#47
| |||
| |||
|
|
Silvio Bierman <sbierman (AT) jambo-software (DOT) com> 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 |
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
#48
| |||
| |||
|
|
Silvio Bierman <sbierman (AT) jambo-software (DOT) com> 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 |
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
#49
| |||
| |||
|
|
Silvio Bierman <sbierman (AT) jambo-software (DOT) com> 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 |
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
#50
| |||
| |||
|
|
Silvio Bierman <sbierman (AT) jambo-software (DOT) com> 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 |
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
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.