BUG #4586: Supporting of Binary instead Bytea for Primary Keys

Miroslav Nachev wrote:

Most of the popular databases (Oracle, MySQL, MS SQL, etc.) support binary
column type which can be used as primary key. For example UUID algorithm
need of exact 16 bytes and is very useful for Primary Key. Of course it can
be presented with characters in Hex format but in that case this will take
32 bytes which is 2 times bigger.
It will be very helpful if in PostgreSQL binary/varbinary type is supported
and can be used for indexes and primary keys.

There's a built-in UUID datatype that you might be interested in. It's
16 bytes, fixed-length.

Also, what's wrong with bytea?

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Dear Heikki,

Thank you very much. How can I map PostgreSQL UUID to JPA Hibernate with
annotations? Serializable or BigInteger or byte[] or java.util.UUID?

The big disadvantage of bytea is that it is not possible to use that type in
indexes and primary keys. Probably this restriction is because is very slow
column type because instead to keep the data this column keeps pointer to
the data.

Miro.

On Wed, Dec 17, 2008 at 10:17 AM, Heikki Linnakangas <
heikki.linnakangas@enterprisedb.com> wrote:

(please keep the mailing list cc'd. (or take this to a more relevant
list, actually)).

Miroslav Nachev wrote:

Thank you very much. How can I map PostgreSQL UUID to JPA Hibernate with
annotations? Serializable or BigInteger or byte[] or java.util.UUID?

No idea.

The big disadvantage of bytea is that it is not possible to use that type in
indexes and primary keys. Probably this restriction is because is very slow
column type because instead to keep the data this column keeps pointer to
the data.

Huh? Of course it's possible:

postgres=# CREATE TABLE foo (id bytea PRIMARY KEY);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"foo_pkey" for table "foo"
CREATE TABLE
postgres=# CREATE INDEX i_foo ON foo (id);
CREATE INDEX
postgres=#

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

And how to specify that bytea MUST be exact 16 bytes? What about the speed
because this is very important?
1st of all when the length is variable then the speed is low.
2nd when the column is with bigger length which require pointer to the data
instead data, then the speed will be extremely low.

Miro.

On Wed, Dec 17, 2008 at 11:19 AM, Heikki Linnakangas <
heikki.linnakangas@enterprisedb.com> wrote:

Miroslav Nachev wrote:

And how to specify that bytea MUST be exact 16 bytes?

Well, you could put a constraint on the column, or create a domain with
such a constraint. But if you're dealing with uuids, you really want to
use the UUID data type anyway.

What about the speed
because this is very important?
1st of all when the length is variable then the speed is low.

The overhead is small enough that you'd never notice.

2nd when the column is with bigger length which require pointer to the data
instead data, then the speed will be extremely low.

Again you're just assuming some overhead that's completely insignificant
in the big scheme of things.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Wed, 17 Dec 2008, Miroslav Nachev wrote:

Thank you very much. How can I map PostgreSQL UUID to JPA Hibernate with
annotations? Serializable or BigInteger or byte[] or java.util.UUID?

The server uuid type is already mapped to java.util.UUID in recent JDBC
driver versions:

http://archives.postgresql.org/pgsql-jdbc/2008-09/msg00102.php

Kris Jurka

Hi,

This is very good. Can you give me some idea how to use that feature
together with Hibernate JPA? Is there some special annotation or ?

Regards,
Miro.

Kris Jurka wrote:

Miroslav Nachev wrote:

I try to use it but I have the following exception:

java.lang.IllegalArgumentException: Unknown entity: java.util.UUID
at
org.hibernate.ejb.AbstractEntityManagerImpl.persist(AbstractEntityManagerImpl.java:223)
at psqluuidtest.Main.persist(Main.java:33)
at psqluuidtest.Main.main(Main..java:25)

Surely you want to persist a TestTable1 instance, not the uuid itself.

Kris Jurka

Yes, you are right. With the correct code:
public static void main(String[] args) {
UUID uuid = UUID.randomUUID();
TestTable1 testTable = new TestTable1(uuid);
persist(testTable);
}

the exception is:
Hibernate: insert into test_db.public.test_table_1 (description, my_id)
values (?, ?)
2008-12-18 0:58:39 org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 0, SQLState: null
2008-12-18 0:58:39 org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: Batch entry 0 insert into test_db.public.test_table_1
(description, my_id) values (NULL, '<stream of 80 bytes>') was aborted.
Call getNextException to see the cause.
2008-12-18 0:58:39 org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 0, SQLState: 42804
2008-12-18 0:58:39 org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: ERROR: column "my_id" is of type uuid but expression is of type
bytea
Hint: You will need to rewrite or cast the expression.
Position: 55
2008-12-18 0:58:39 org.hibernate.event.def.AbstractFlushingEventListener
performExecutions
SEVERE: Could not synchronize database state with session
org.hibernate.exception.SQLGrammarException: Could not execute JDBC
batch update
at
org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at
org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at
org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:253)
at
org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:237)
at
org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:141)
at
org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
at
org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)
at
org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
at org.hibernate.ejb.TransactionImpl.commit(TransactionImpl.java:54)
at psqluuidtest.Main.persist(Main.java:34)
at psqluuidtest.Main.main(Main.java:25)
Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into
test_db.public.test_table_1 (description, my_id) values (NULL, '<stream
of 80 bytes>') was aborted. Call getNextException to see the cause.
at
org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2556)
at
org.postgresql.core.v3.QueryExecutorImpl$1.handleError(QueryExecutorImpl.java:395)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1348)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:343)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2693)
at
org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
at
org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:246)
... 10 more
javax.persistence.RollbackException: Error while commiting the transaction
at org.hibernate.ejb.TransactionImpl.commit(TransactionImpl.java:71)
at psqluuidtest.Main.persist(Main.java:34)
at psqluuidtest.Main.main(Main.java:25)
Caused by: org.hibernate.exception.SQLGrammarException: Could not
execute JDBC batch update
at
org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at
org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at
org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:253)
at
org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:237)
at
org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:141)
at
org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
at
org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)
at
org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
at org.hibernate.ejb.TransactionImpl.commit(TransactionImpl.java:54)
... 2 more
Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into
test_db.public.test_table_1 (description, my_id) values (NULL, '<stream
of 80 bytes>') was aborted. Call getNextException to see the cause.
at
org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2556)
at
org.postgresql.core.v3.QueryExecutorImpl$1.handleError(QueryExecutorImpl.java:395)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1348)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:343)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2693)
at
org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
at
org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:246)
... 10 more
Exception in thread "main" java.lang.IllegalStateException: Transaction
not active
at
org.hibernate.ejb.TransactionImpl.rollback(TransactionImpl.java:82)
at psqluuidtest.Main.persist(Main.java:37)
at psqluuidtest.Main.main(Main.java:25)

Miro.

Kris Jurka wrote:

Hi,

What if I convert to/from byte array with (see the attached java number
utility class):
NumberUtils.toByteArray(UUID uuid)
NumberUtils.toUUID(byte[] value)

Miro.

Miroslav Nachev wrote: