Re: 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(at)selestial(dot)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(at)selestial(dot)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."

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

Looks like that table has a column of type BYTEA, while the code expects it
to be a LOB. Perhaps the easiest fix is to change the table to store a LOB
instead?
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.