Thread: How to convert ByteA to Large Objects
Hi All, Is there some nice SQL I can use to convert a BYTEA field into large objects? I am having some memory problems handling bytea parameters in Npgsql - getting out of memory errors. From reading various posts on this I see that bytea's can consume much more memory than the size of the object so I am going to convert these bytea's to large objects so I can chunk the data. Thanks, Howard Cole www.selestial.com
On Fri, Jan 23, 2009 at 10:08 AM, Howard Cole <howardnews@selestial.com> wrote: > Hi All, > Hi, Howard! > Is there some nice SQL I can use to convert a BYTEA field into large > objects? > > I am having some memory problems handling bytea parameters in Npgsql - > getting out of memory errors. From reading various posts on this I see that > bytea's can consume much more memory than the size of the object so I am > going to convert these bytea's to large objects so I can chunk the data. > About the memory issue of Npgsql, did you try to use a prepared statement for this? We have some performance improvements when handling bytea values but only when the NpgsqlCommand.Prepare() method is called. I hope it helps. -- Regards, Francisco Figueiredo Jr. Npgsql Lead Developer http://fxjr.blogspot.com http://www.npgsql.org
Francisco Figueiredo Jr. wrote: > About the memory issue of Npgsql, did you try to use a prepared > statement for this? We have some performance improvements when > handling bytea values but only when the NpgsqlCommand.Prepare() method > is called. > > I hope it helps. > > Hi Francisco, Yes it did help. Thank you very much. (P.S. Npgsql is great!) Thanks. Howard Cole www.selestial.com
On Fri, Jan 23, 2009 at 12:08:02PM +0000, Howard Cole wrote:
> Is there some nice SQL I can use to convert a BYTEA field into large
> objects?
You may be able to do something like:
SELECT oid, lowrite(lo_open(oid, 131072), (SELECT byteafield FROM tbl WHERE x)
FROM lo_create(NULL) o(oid);
You could turn this around to maybe do:
UPDATE tbl t SET newoid = (
SELECT oid FROM (
SELECT oid, lowrite(lo_open(oid, 131072), t.byteafield)
FROM lo_create(0) o(oid)) x);
--
Sam http://samason.me.uk/
On Fri, Jan 23, 2009 at 11:25 AM, Howard Cole <howardnews@selestial.com> wrote: > Francisco Figueiredo Jr. wrote: >> >> About the memory issue of Npgsql, did you try to use a prepared >> statement for this? We have some performance improvements when >> handling bytea values but only when the NpgsqlCommand.Prepare() method >> is called. >> >> I hope it helps. >> >> > > Hi Francisco, > Hi, Howard! > Yes it did help. Thank you very much. (P.S. Npgsql is great!) > Great! I'm glad to hear it. And thank you for using Npgsql! > Thanks. > You are welcome! -- Regards, Francisco Figueiredo Jr. Npgsql Lead Developer http://fxjr.blogspot.com http://www.npgsql.org
Brilliant Sam! I'm migrating a Firebird legacy app into Postgres, and your SQL works well. Question - the number of bytes is not specified in the write - could this be an issue? lowrite appears undocumented (as opposed to lo_write). -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-convert-ByteA-to-Large-Objects-tp1914180p3425725.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
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. -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-convert-ByteA-to-Large-Objects-tp1914180p4809638.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 09/15/11 11:23 PM, Jayadevan wrote: > 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. -- john r pierce N 37, W 122 santa cruz ca mid-left coast
> > 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."
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."
One of the benefits of doing that is that you can "stream" LOB's (you access it like a file; opening, reading, seeking), whereas AFAIK you have to retrieve BYTEA data as a whole before you can start sending it to the client.