Re: JDBC Streaming large objects - Mailing list pgsql-jdbc

If the values are too large, have you tried storing them as
large objects? You can then use Postgres own Large Object API
that allows you to stream binary data to the backend without
having to keep a copy of the data in memory.

I had the same problem/question just some few days ago. See
"Difference between Blob and Large Object API".


> -----Ursprüngliche Nachricht-----
> Von: Kevin Schroeder [mailto:kschroeder@mirageworks.com]
> Gesendet: Samstag, 13. September 2003 13:55
> An: pgsql-jdbc@postgresql.org
> Betreff: Re: [JDBC] JDBC Streaming large objects
>
>
> It is the values themselves that are too large.  The query is
> inserting an
> email into the database.  99.999999% percent of the time it's
> not an issue
> since most emails aren't that large, but if someone sends a
> large email
> (20+MB) you have  copy of it in memory and then when you add
> it to the SQL
> statement you have it there again, so it's using 40MB worth of memory
> (roughly, of course).  I was able to free up half of the
> memory by swapping
> the email to the hard drive and using the setXXX() function,
> but there will
> still be cases where Java will run out of memory.  I can
> increase the heap
> size, which solves the problem for me, but most people
> probably won't think
> of memory issues when they install the software or they may have other
> applications that require high memory usage on the same machine.
>
> Since I posted my first message I have put in a workaround.
> It doesn't do
> what I'd like, but at least it notifies people that there was
> a problem by
> catching the OutOfMemoryError exception.
>
> If the current JDBC spec won't be able to handle the kind of
> query I'm doing
> perhaps someone can point me to the code in the PostgreSQL
> driver that I
> could use to build a custom interface that would create the
> SQL statement on
> the fly, pulling the data from the hard drive while it's sending it to
> PostgreSQL.  And perhaps at some point in the future it could
> be beneficial
> to add that feature, or something like it, to the JDBC driver.
>
> Kevin
>
> ----- Original Message -----
> From: "Barry Lind" <blind@xythos.com>
> To: "Kevin Schroeder" <kschroeder@mirageworks.com>
> Cc: <pgsql-jdbc@postgresql.org>
> Sent: Friday, September 12, 2003 8:39 PM
> Subject: Re: [JDBC] JDBC Streaming large objects
>
>
> > Kevin,
> >
> > Can you give an example of what you are trying to do?  Is
> it the text of
> > the query that is too large to hold in memory, or is the
> the values that
> > are being bound that make it too large?
> >
> > I am not sure that the jdbc spec works well for you.  Since
> even if you
> > are using PreparedStatements, you need to have all the
> values in memory
> > when you call setXXX(), since until execute() is called the
> SQL can't be
> > sent to the server, and these values can't be freed and
> garbage collected.
> >
> > thanks,
> > --Barry
> >
> >
> >
> >
> > Kevin Schroeder wrote:
> > > Hello,
> > >     I was wondering if the PostgreSQL JDBC driver has the
> ability to
> stream
> > > an SQL query to PostgreSQL.  Looking at the archive it
> seems as though
> that
> > > functionality is not there.  I'm writing a program that
> needs to have
> the
> > > ability to generate the SQL query on the fly because there will be
> occasions
> > > where the INSERT statements will be larger than the
> available memory.
> If
> > > this functionality is not yet available it means that
> I'll have to make
> some
> > > modifications to the JDBC driver but I'd rather not do
> that if there is
> a
> > > method of streaming the query already out there.  I also
> probably don't
> know
> > > what I'm getting into if I were to try rewriting portions
> of the driver.
> > >
> > >     So, if anyone knows if there is a way to stream data
> via an INSERT
> > > statement without running into the OutOfMemoryError I'd
> love to hear it.
> > >
> > > Thanks
> > > Kevin Schroeder
> > >
> > >
> > > ---------------------------(end of
> broadcast)---------------------------
> > > TIP 9: the planner will ignore your desire to choose an
> index scan if
> your
> > >       joining column's datatypes do not match
> > >
> >
> >
> >
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> >                http://archives.postgresql.org
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index
> scan if your
>       joining column's datatypes do not match
>