> > 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."
