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