Transaction Question

[John Sidney-Woollett]

I have to convert an java web application currently using an Oracle DB
back end to one using a Postgres backend.

In Oracle much of the application logic is abstracted away from the java
middleware layer using stored procedures within the Oracle database. There
are certain features in Oracle that appear to be missing from Postgres
which are causing us some concern, and we wondered how much we're going to
have to butcher the backend and db stored procs.

Issue - nested transactions
=====

Oracle provides the #pragma autonomous hint which allows you to declare
that a procedure/function will run in its own transaction and which can be
committed regardless of the final commit/rollback state of the enclosing
transaction.

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.

We have the function being called by many threads simultaneously, and if
the lock is only released at the end of the enclosing transaction, then
the subsequent calls after the first will block until the first completes.
In other words, although threads are making calls in parallel, they will
only run serially because of the bottleneck.

I have seen a note about using separate connections/threads to resolve
this issue. There is NO possibility of our java middleware using two
threads/connections to separate out the transaction as the idea is that
the java makes one call to the database, and it handles all concurrency
issues (beautifully) without us having to embed db specific code/logic in
the middleware.

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.

Thanks for any info anyone can provide.

John Sidney-Woollett


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Richard Huxton]

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.postgr esql.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 YourEmailAddres sHere" to ma*******@postg resql.org)

[John Sidney-Woollett]

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 GetVolumeFileRe ference (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(vAllocSt rategy) = ''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 := GetVolumeFileRe ference(''FIRST '', vSize);
vVolumeID := to_number(trim( substring(vVolF ileSeq, 1, 10)));
vFileSeq := to_number(trim( substring(vVolF ileSeq, 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 := GuessContentTyp e(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_W DRESOURCE'') 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 := GetVolumeFileRe ference(''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.postgr esql.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 YourEmailAddres sHere" to ma*******@postg resql.org)

[Richard Huxton]

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 GetVolumeFileRe ference (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 "LastFileSe q" just used as a unique identifier, or does it have a
hidden meaning too (e.g. "approximat e 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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[John Sidney-Woollett]

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(vVolSeq Name) 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 GetVolumeFileRe ference (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 "LastFileSe q" just used as a unique identifier, or does it have a
hidden meaning too (e.g. "approximat e 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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Richard Huxton]

On Wednesday 03 December 2003 13:34, John Sidney-Woollett wrote:

Richard

You summized correctly. The field being updated is basically a sequence
per volume. BUT the sequences cannot be shared across the volumes...
Why not? Are you worried about running out of numbers, or is there some other
reason?
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(vVolSeq Name) into vFileSeq;
If nothing else, you could use EXECUTE and build your select in a string. See
the manual for details of EXECUTE.
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.
A sequence number will never be rolled back. The sequence generator guarantees
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?

Not as far as I know (if you would like to contribute some as you learn, I'm
sure it would be appreciated). The isolation levels are listed with the
corresponding standard SQL though, so it might be worth googling for a
general reference.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Scott Ribe]

You could do something with threads on the backend, invisible to your Java
middleware. I don't have enough experience to feel confident about trying to
evaluate the pros and cons of (possibly) different ways of doing this. But
given that you can write functions in C and load them into Postgres so that
they can be called from plpgsql, I think you could in essence add to
Postgres a function which when called would hand off the sequence load &
update on a separate thread/connection, wait for its commit and completion,
and return the value. Of course you still have to be careful about
concurrency issues with this approach, so that you don't wind up with the 2
threads deadlocked.

That may well strike you as a gross hack. I don't particularly like it
either, but I think it would get the job done without requiring any changes
to your current code base except for the rewrite of GetVolumeFileRe ference.

BTW, in reference to other suggestions: I believe that a sequence name is
indeed just a string, so you can build the name and pass it to sequence
functions on the fly; I know that sequences do not roll back, once a value
is issued it is "burned" regardless of whether the enclosing transaction
commits or not. So you should be able to have a trigger that on insert of a
WDVolume row creates a corresponding sequence, then use that sequence within
GetVolumeFileRe ference. Whether this is a good idea depends I'm sure on how
many WDVolumes you'll have. I feel confident that dozens or hundreds would
be no problem; thousands I don't have any idea; millions I wouldn't try.
--
Scott Ribe
sc********@kill erbytes.com
http://www.killerbytes.com/
[phone] voice
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[John Sidney-Woollett]

Hi Scott

Firstly, your name rings a bell. I'm sure you're a 4D developer - I was
one too (once, but no more sadly)...

You imply that it should be possible to create an embedded C function that
can access the DB in its own thread, and therefore its own transaction. Is
there a document that would explain how this is done?

Also do you know if you can embedded java functions - I'm more comfortable
with java than C these days?

I think that for this problem, I will use SEQUENCE objects who name is
stored within the WDVolume table. It will allow me to avoid the row level
lock bottleneck, and my counters won't be rolled back on a transaction
rollback. There are only going to be less than 100 Volumes - so this
approach will be fine.

I'm amazed that nested transactions are still not yet supported. Postgres
is a mature product (from my understanding), and I can see loads of
messages asking about nested transaction support.

Thanks for your feedback.

John

Scott Ribe said:

You could do something with threads on the backend, invisible to your Java
middleware. I don't have enough experience to feel confident about trying
to
evaluate the pros and cons of (possibly) different ways of doing this. But
given that you can write functions in C and load them into Postgres so
that
they can be called from plpgsql, I think you could in essence add to
Postgres a function which when called would hand off the sequence load &
update on a separate thread/connection, wait for its commit and
completion,
and return the value. Of course you still have to be careful about
concurrency issues with this approach, so that you don't wind up with the
2
threads deadlocked.

That may well strike you as a gross hack. I don't particularly like it
either, but I think it would get the job done without requiring any
changes
to your current code base except for the rewrite of
GetVolumeFileRe ference.

BTW, in reference to other suggestions: I believe that a sequence name is
indeed just a string, so you can build the name and pass it to sequence
functions on the fly; I know that sequences do not roll back, once a value
is issued it is "burned" regardless of whether the enclosing transaction
commits or not. So you should be able to have a trigger that on insert of
a
WDVolume row creates a corresponding sequence, then use that sequence
within
GetVolumeFileRe ference. Whether this is a good idea depends I'm sure on
how
many WDVolumes you'll have. I feel confident that dozens or hundreds would
be no problem; thousands I don't have any idea; millions I wouldn't try.
--
Scott Ribe
sc********@kill erbytes.com
http://www.killerbytes.com/
[phone] voice
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Richard Huxton]

On Thursday 04 December 2003 07:57, John Sidney-Woollett wrote:

I'm amazed that nested transactions are still not yet supported. Postgres
is a mature product (from my understanding), and I can see loads of
messages asking about nested transaction support.

Loads of messages asking for many other features too ;-)
It will get done sooner or later - the more assistance people offer the core
the sooner it'll be.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[John Sidney-Woollett]

Point taken.

I'm also interested in replication - seeing lots of messages about that
too! :)

John

Richard Huxton said:

Loads of messages asking for many other features too ;-)
It will get done sooner or later - the more assistance people offer the
core
the sooner it'll be.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster