Re: Transaction Question

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