Re: Transaction Question

On Wednesday 03 December 2003 13:34, John Sidney-Woollett wrote:
> Richard
>
> You summized correctly. The field being updated is basically a sequence
> per volume. BUT the sequences cannot be shared across the volumes...

Why not? Are you worried about running out of numbers, or is there some other
reason?

> I did wonder about "binding" one sequence object to each Volume record, and
> modifying the function so that the identified the volume to use, and then
> obtained the next value from the appropriate sequence object. Is it
> possible to do the following:
>
> --declare var to "hold" the sequence name
> vVolSeqName := "SEQ_VOLUME_1";
>
> --access the sequence from the name in the variable
> select nextval(vVolSeqName) into vFileSeq;

If nothing else, you could use EXECUTE and build your select in a string. See
the manual for details of EXECUTE.

> If I cannot do this, can you suggest a solution to my original bottleneck
> issue. And also the problem of the sequencing number being rolled back in
> the event that the CreateFile function aborts or is rolled back.

A sequence number will never be rolled back. The sequence generator guarantees

> However, for me a bigger area of confusion is how to deal with concurrency
> issues in Postgres generally. Are there any good docs with examples of
> different scenarios?

Not as far as I know (if you would like to contribute some as you learn, I'm
sure it would be appreciated). The isolation levels are listed with the
corresponding standard SQL though, so it might be worth googling for a
general reference.

--
Richard Huxton
Archonet Ltd