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
