Re: Advice

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