Re: Advice - Mailing list pgsql-jdbc

Hi Lane

I don't use large objects, but looking at some posts on various things,
it looks like bytea is the way to go.

If I remember correctly there are issues with vacuuming blobs ( I think
you need to do a vacuum full to recover the space, plus I doubt the free
space map deals with them well )

Also by way of a "heads up" the development version of the driver Oliver
mentioned is still somewhat immature. We do encourage it's use, but
don't bang your head needlessly.

Dave

On Tue, 2004-08-17 at 11:50, Lane Sharman wrote:
> Hi Dave,
>
> Thanks very much. Really appreciated this insight on sequence generation
> which is very close to Oracle's.
>
> In stress tests in the past, I have run into issues with using the BLOB
> methods on j.s.ResultSet.getBlob(). Are there any caveats here, should I
> stick with ResultSet.getBytes() using bytea types to store large binary
> objects?
>
> thanks,
>
> Lane
>
> Dave Cramer wrote:
>
> >Lane,
> >
> >Greetings, we chatted a while back on the webmacro list...
> >
> >Yes, just ask on the list, or the postgres irc channel,
> >
> >couple of things:
> >
> >sequence generation is atomic (it transcends transactions, can't be
> >rolled back, etc.), and they are not "special" in the sense that the
> >driver doesn't know anything about generated keys.
> >
> >There are two functions to get a sequence, nextval('sequence_name'), and
> >currval('sequence_name');
> >
> >nextval will increment the sequence and return the next value.
> >
> >currval returns the result of the last executed nextval by this
> >connection. In other words if con1 and con2 both do a nextval then
> >calling currval on con1 will get the value generated by nextval on that
> >connection.
> >
> >there is a well known issue with serial8. The index will not be used if
> >you do select foo where serialvalue=1;
> >you need to cast the 1 to int8
> >ie select foo where serialvalue=1::int8 ( this is fixed in 8.0 )
> >
> >
> >re locking: postgresql is an MVCC db, so you should very carefully read
> >the sections on transactions, and visibility.
> >
> >Cheers,
> >
> >Dave
> >
> >
> >On Mon, 2004-08-16 at 22:12, Lane Sharman wrote:
> >
> >
> >>Greetings,
> >>
> >>I am new to PG but have been writing JAVA since 1.0 and working with SQL
> >>for much longer than that. I am the author of VeryLargeHashtable
> >>(http://www.webmacro.org/VeryLargeHashtable for info). In short, VLH
> >>takes a common Java idiom, the Hashtable, and makes the backing store a
> >>DB table instead of volatile memory. For many java applications, this is
> >>a perfect persistence scheme.
> >>
> >>The component was developed under Oracle and now I want to extend it to
> >>the Postgres dialect. I need some advice on DDL and optimizing under PG.
> >>
> >>Is there someone on this list that I could converse with very briefly
> >>offline about BLOB versus bytea types, sequence generators and table/row
> >>locking inside a jdbc-centric component.
> >>
> >>many thanks in advance for a small bit of your time!
> >>
> >>
--
Dave Cramer
519 939 0336
ICQ # 14675561