Re: Transaction Question - Mailing list pgsql-general

Richard

You summized correctly. The field being updated is basically a sequence
per volume. BUT the sequences cannot be shared across the volumes...

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 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.

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?

I appreciate all your help so far. Thanks.

John

Richard Huxton said:
> On Wednesday 03 December 2003 11:01, John Sidney-Woollett wrote:
>> Here are two procedures converted from Oracle's PL/SQL (so the oracle
>> specific stuff (like #pragma autonomous) has gone.
>>
>> This first function returns two values (it used to use an extra pair of
>> out parameters). You are correct in that the function SHOULD increment
>> the
>> counter regardless of whether the enclosing transaction commits or not.
>> (Of course in Postgres this is not the case).
>>
>> CREATE OR REPLACE FUNCTION GetVolumeFileReference (varchar, integer)
>> RETURNS integer AS '
> [snip]
>>   -- now lock the volume (and wait for it to become free)
>>   select LastFileSeq into vFileID
>>   from WDVolume
>>   where WDVolumeID = vVolumeID
>>   for update;
>>
>>   -- increment the file seq counter
>>   if (vFileID is null) then vFileID := 0; end if;
>>   vFileID := vFileID + 1;
>>
>>   -- update the volume, and write the changed values back
>>   update WDVolume
>>   set LastFileSeq = vFileID
>>   where WDVolumeID = vVolumeID;
>
> OK - here you are basically recreating what a sequence does. The
> difference
> being that you have one sequence per "VolumeID".
>
> Now, is "LastFileSeq" just used as a unique identifier, or does it have a
> hidden meaning too (e.g. "approximate number of files created")? If it is
> just a unique identifier, just share one sequence between all the volumes.
>
> If it comes down to it, you can have many sequences, but I don't know how
> the
> system copes if you have thousands or millions of them.
>
> Oh - did you know you could return a pair of integers from your function?
> Use
> CREATE TYPE to make a type containing two ints and then return that type.
> Should save you a substring later (incidentally, I don't think integer
> will
> go to 20 digits, so you'll have a problem there).
>
> --
>   Richard Huxton
>   Archonet Ltd
>