Re: Problem while using start transaction ans commit;

Sri,

> I have a small problem in using nested transactions while
> working on Postgres 8.0.

This is a known problem with Postgres 8.0 - there is no support for
nested transactions (which occurs when calling functions). Your best bet
would be to raise an exception within B or C - this will cause a
rollback to wherever the exception is caught. If you surround the calls
to B and C in a block to catch the exception, this will provide
transaction-like semantics.

An example:

----------
CREATE OR REPLACE FUNCTION tr_addcourse(employeeno, int4, coursename,
float4, text, timestamptz, int4) RETURNS int4 AS
$BODY$-- Use case: 10.2.9: Add a course

DECLARE  transid int4;  cid int4;  errcode int4;
BEGIN -- Setup default return code. This is used if we hit an -- exception that we didn't throw. SELECT -32767 into
errcode; -- E_UNKNOWN 
 -- Start the transaction, lock tables LOCK TABLE backend.courses IN ACCESS EXCLUSIVE MODE; LOCK TABLE
backend.courseareasIN SHARE MODE; 
 -- Access to administrators only IF NOT tt_user_access(actor, 'a') THEN   SELECT -1 into errcode;  -- Return
E_NO_ACCESS  RAISE EXCEPTION 'User % does not have access.', actor; END IF; 
 -- Check for a duplicate course name. IF tt_course_name_active(cname) THEN   SELECT -2000 INTO errcode;  --
E_DUP_COURSE  RAISE EXCEPTION 'Course "%" already exists.', cname; END IF; 
 -- Check for course area status SELECT tt_coursearea_status(carea) INTO errcode; IF NOT errcode = 0 THEN  -- NOT
errcode= SUCCESS   RAISE EXCEPTION 'Error finding active course area %', carea; END IF; 
 -- Grab a transaction ID SELECT tt_acquire_transaction(actor, 'tr_addcourse') INTO transid; IF transid < 0 THEN
SELECTtransid into errcode; -- Return the error code.   RAISE EXCEPTION 'Could not acquire transaction.'; END IF; 
 -- Get the next course ID SELECT nextval('backend.courses_courseid_seq') INTO cid;
 -- Insert the row INSERT INTO backend.courses  (transactionid, courseid, coursearea, coursename, active, duration,
description, contentdate, valid_months)  VALUES (transid, cid, carea, cname, TRUE, dur, desc, cdate,
valid_mths);
 -- Success RETURN cid;

EXCEPTION WHEN RAISE_EXCEPTION THEN   RETURN errcode;
 WHEN OTHERS THEN   RETURN -32767;  -- E_UNKNOWN

END;$BODY$ LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;

----------

In this code, whenever an exception is raised, the system will rollback
to the start of the block (BEGIN).

Best regards,

Ben Stewart

--
Robert Bosch (Australia) Pty. Ltd.
Engineering Quality Services, Student Software Engineer (RBAU/EQS4)
Locked Bag 66 - Clayton South, VIC 3169 - AUSTRALIA
mailto:ben.stewart@au.bosch.com
http://www.bosch.com.au/