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 '
-- allocates a volume and unique file ID for storing a resource
-- determines where to store a (file) resource according to the
allocation strategy
-- pAllocStrategy can be either
-- null, FIRST (default) = fill first available volume before using next
-- DISTRIBUTE = distribute files across the available volumes
DECLARE
pAllocStrategy ALIAS OF $1;
pSpaceReqd ALIAS OF $2;
vAllocStrategy varchar(16) := pAllocStrategy;
vVolumeID integer := -1;
vFileID integer := -1;
BEGIN
-- set defaults
vVolumeID := -1;
pFileID := -1;
-- get the volume ID, and next file ID
-- check what allocation strategy has been requested
-- to locate the most appropriate volume
if (upper(vAllocStrategy) = ''DISTRIBUTE'') then
select WDVolumeID into vVolumeID
from WDVolume
where Writeable = ''Y''
and DiskAvailPC >= 5
order by DiskAvailPC desc
limit 1;
else
-- use first volume with space available
select WDVolumeID into vVolumeID
from WDVolume
where Writeable = ''Y''
and DiskAvailPC >= 5
order by WDVolumeID
limit 1;
end if;
-- cannot find a free volume
if vVolumeID is null then
return -1;
end if;
-- 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;
RETURN lpad(vVolumeID, 10, '' '') || lpad(vFileID, 10, '' '');
END;
' LANGUAGE 'plpgsql';
Here is a stripped down version of the function that calls the one above -
there are many threads calling this function simultaneously:
CREATE OR REPLACE FUNCTION CreateFile (integer, varchar, varchar, integer)
RETURNS integer AS '
-- creates a new file (if it does not exist)
-- and returns the WDResource ID of the newly
-- created file. If the file already exists
-- then the resource ID is returned, otherwise
-- if the file could not be created then -1 is returned
DECLARE
pFolderID ALIAS OF $1;
pFilename ALIAS OF $2;
pContentType ALIAS OF $3;
pSize ALIAS OF $4;
vUserID integer;
vResourceID integer := -1;
vURL varchar(255) := null;
vVolumeID integer := -1;
vFileSeq integer := -1;
vRefPath varchar(64) := null;
vRefName varchar(64) := null;
vContentType varchar(16) := pContentType;
vSize integer := pSize;
vVolFileSeq varchar(64) := null;
BEGIN
-- get the parent folder information
select WDUserID, URL into vUserID, vURL
from WDResource
where WDResourceID = pFolderID;
if vURL is null then
return -1;
end if;
-- check that the file doesnt exist already
select WDResourceID into vResourceID from WDResource
where URL = vURL||''/''||pFileName;
-- if it does exist then return the ID
if (vResourceID is not null) then
return vResourceID;
end if;
-- determine the volume to locate the file on, and get the file
-- sequence number
vVolFileSeq := GetVolumeFileReference(''FIRST'', vSize);
vVolumeID := to_number(trim(substring(vVolFileSeq, 1, 10)));
vFileSeq := to_number(trim(substring(vVolFileSeq, 11, 10)));
-- if any error occured here then abort
if (vVolumeID = -1) or (vFileSeq = -1) then
return -1;
end if;
-- guess the content type if not known
if (vContentType is null) then
vContentType := GuessContentType(pFilename);
end if;
-- create the unique file reference name
-- this is the one stored on disk
vRefName := getFileRefName(vVolumeID, vFileSeq, vContentType, pFilename);
-- get the pathname from the file reference name
vRefPath := getFileRefPath(vRefName);
-- get the next resource ID
select nextval(''SEQ_WDRESOURCE'') into vResourceID;
-- set the content length (file size) to zero initially
-- it will be updated after successfully writing the file
-- using the UpdateDiskUsed(WDResourceID, newSize) procedure
vSize := 0;
-- create the new file resource
insert into WDResource (WDRESOURCEID, WDPARENTID, WDUSERID, WDRESTYPEID,
URL, WDVOLUMEID,
FILENAME, REFPATH, REFNAME, CONTENTTYPE, CONTENTLENGTH, LASTMODIFIED)
values (vResourceID, pFolderID, vUserID, 2, vURL||''/''||pFilename,
vVolumeID,
pFilename, vRefPath, vRefName, vContentType, vSize, now());
return vResourceID;
END;
' LANGUAGE 'plpgsql';
I recognize that the above code may not be ana elegant way to code using
Postgres - but it is a fairly literal transation from the Oracle PL/SQL
code.
You can see that the call to
vVolFileSeq := GetVolumeFileReference(''FIRST'', vSize);
is going to be a bottleneck.
Is there some way of dealing with this issue? I cannot allow dirty reads
onto the value returned by this function.
I'm really unsure of how Postgres guarantees data consistancy with MVCC...
Thanks for any help.
John
Richard Huxton said:
On Wednesday 03 December 2003 08:08, John Sidney-Woollett wrote: I have to convert an java web application currently using an Oracle DB
back end to one using a Postgres backend.
[snip] Issue - nested transactions
=====
[snip] This is an issue for us because some procedures make use of a function
which issues a row level lock on a table (select ... for update) in
order
to read and then update a counter, and which then commits to release the
lock. The nested function returns the new counter value on return. We
cannot use Sequence objects, because the counter is tied directly to the
record which contains it, and there are any number of these record
types.
Can you elaborate on what this counter is/how you are using it? It sounds
like
the "counter" gets incremented regardless of whether an insert/update gets
committed, which makes me wonder what it is counting.
Is there a simple/elegant solution to this problem? And is there a good
document on dealing with concurrency issues - I have read the manual for
7.4 and while it describes the transaction isolation levels, and MVCC -
it
doesn't really offer any practical tips or solutions to this problem.
Hmm - we don't seem to have any items dealing with concurrency issues on
techdocs.postgresql.org, which is a shame since they are exactly the sort
of
thing benefit from having examples of pitfalls.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to
ma*******@postgresql.org)
