Re: How to convert ByteA to Large Objects - Mailing list pgsql-general
| From | Jayadevan M |
|---|---|
| Subject | Re: How to convert ByteA to Large Objects |
| Date | |
| Msg-id | OF48985D42.999463CB-ON6525790D.002F66C8-6525790D.003062C9@ibsplc.com Whole thread Raw |
| In response to | Re: How to convert ByteA to Large Objects (John R Pierce <pierce@hogranch.com>) |
| Responses |
Re: How to convert ByteA to Large Objects
(Alban Hertroys <haramrae@gmail.com>)
|
| List | pgsql-general |
> > 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."
> > 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."
pgsql-general by date: