Thread: Advice
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! -- Lane Sharman Providing Private and SPAM-Free Email http://www.opendoors.com 858-755-2868
On Mon, 16 Aug 2004, Lane Sharman wrote: > 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. > I'm not sure what you are looking for, but certainly this list is a good place to ask any questions you may have. If you're looking for something a little more interactive you could try the #postgresql irc channel on freenode.net. Kris Jurka
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
Dave Cramer wrote: > 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 ) This should also be fixed if you use the latest development driver, connect to a 7.4 or later server, and use setLong() (or setObject with Types.BIGINT) to set the parameter value. The V3 protocol Parse/Bind messages that newer drivers generate will specify int8 for the parameter type in this case, which is roughly equivalent to explicitly casting the parameter to int8. I haven't tested this, though -- it's theoretical :) -O
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
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!
>>
>>
--
Lane Sharman
Providing Private and SPAM-Free Email
http://www.opendoors.com
858-755-2868