Re: Transaction Question - Mailing list pgsql-general

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
>