> > Thank you. We are working on an Oracle to PostgreSQL migration
project.
> > BLOB columns got converted to BYTEA in PostgreSQL and we ran into
problems.
> > We used this to convert the data type to OID. Thank you.
>
> you probably should detail the problems you ran into. large objects
> normally aren't used unless you need to store over 1GB objects in the
> database, and at least speaking for myself, I'd rather not have objects
> that large in my database at all, I'd as soon use files for things that
big.
>
Well, we are storing scanned images of boarding passes. We are using
Hibernate and the insert statement generated threw this error.
"Caused by: org.hibernate.exception.SQLGrammarException: could not insert:
at
org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at
org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at
org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2202)
at
org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2595)
at
org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:51)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:248)
at
org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:232)
at
org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:139)
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
com.ibsplc.xibase.server.framework.persistence.hibernate.HibernateJTATxWrapper.commit(HibernateJTATxWrapper.java:93)
... 51 more
Caused by: org.postgresql.util.PSQLException: ERROR: column "docdta" is of
type bytea but expression is of type oid
Hint: You will need to rewrite or cast the expression."
If we try rewriting, that would mean code changes in a few other places.
We changed the data type and now 'some' data has been inserted. Once we
fix the retrieval screen, we will know it is getting processed correctly.
select data from pg_largeobject where loid=<id> gave us a couple of
hundred records, so we assume data has been inserted.
Switching to file storage will mean quite a bit of changes at the code
level, and a lot of testing of the products that runs fine on Oracle now.
Regards,
Jayadevan
DISCLAIMER:
"The information in this e-mail and any attachment is intended only for
the person to whom it is addressed and may contain confidential and/or
privileged material. If you have received this e-mail in error, kindly
contact the sender and destroy all copies of the original communication.
IBS makes no warranty, express or implied, nor guarantees the accuracy,
adequacy or completeness of the information contained in this email or any
attachment and is not liable for any errors, defects, omissions, viruses
or for resultant loss or damage, if any, direct or indirect."
