Re: Transaction Question

You could do something with threads on the backend, invisible to your Java
middleware. I don't have enough experience to feel confident about trying to
evaluate the pros and cons of (possibly) different ways of doing this. But
given that you can write functions in C and load them into Postgres so that
they can be called from plpgsql, I think you could in essence add to
Postgres a function which when called would hand off the sequence load &
update on a separate thread/connection, wait for its commit and completion,
and return the value. Of course you still have to be careful about
concurrency issues with this approach, so that you don't wind up with the 2
threads deadlocked.

That may well strike you as a gross hack. I don't particularly like it
either, but I think it would get the job done without requiring any changes
to your current code base except for the rewrite of GetVolumeFileReference.

BTW, in reference to other suggestions: I believe that a sequence name is
indeed just a string, so you can build the name and pass it to sequence
functions on the fly; I know that sequences do not roll back, once a value
is issued it is "burned" regardless of whether the enclosing transaction
commits or not. So you should be able to have a trigger that on insert of a
WDVolume row creates a corresponding sequence, then use that sequence within
GetVolumeFileReference. Whether this is a good idea depends I'm sure on how
many WDVolumes you'll have. I feel confident that dozens or hundreds would
be no problem; thousands I don't have any idea; millions I wouldn't try.

--
Scott Ribe
scott_ribe(at)killerbytes(dot)com
http://www.killerbytes.com/
(303) 665-7007 voice