pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProblem while using start transaction ans commit;
3+ messages / 3 participants
[nested] [flat]
* Problem while using start transaction ans commit;
@ 2005-10-17 07:23 Sri <asrinivas@effigent.net>
2005-10-19 07:45 ` Re: Problem while using start transaction ans commit; Oliver Elphick <olly@lfix.co.uk>
0 siblings, 1 reply; 3+ messages in thread
From: Sri @ 2005-10-17 07:23 UTC (permalink / raw)
To: pgsql-sql
Hi All,
I have a small problem in using nested transactions while working on Postgres 8.0.
Ex: I have a function A() which in turn calls functions b() and c() , if i want commit something in b or c. i have to use
start transaction read write;
-- set of sql statements and then say commit.
commit;
but this is not working it is showing the the following error near start transaction read write
" ERROR: SPI_execute_plan failed executing query "start transaction read write": SPI_ERROR_TRANSACTION ";
or
if i use only commit with out the start transaction command it throwing the runtime error
as
" ERROR: SPI_execute_plan failed executing query "commit": SPI_ERROR_TRANSACTION ";
Can any one send me an example of how to use the start transaction or how to commit an nested transaction.
Thanks in advance.
Thanks & Regards,
Sri
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Problem while using start transaction ans commit;
2005-10-17 07:23 Problem while using start transaction ans commit; Sri <asrinivas@effigent.net>
@ 2005-10-19 07:45 ` Oliver Elphick <olly@lfix.co.uk>
0 siblings, 0 replies; 3+ messages in thread
From: Oliver Elphick @ 2005-10-19 07:45 UTC (permalink / raw)
To: Sri <asrinivas@effigent.net>; +Cc: pgsql-sql
On Mon, 2005-10-17 at 12:53 +0530, Sri wrote:
> Hi All,
>
> I have a small problem in using nested transactions while working on
> Postgres 8.0.
>
> Ex: I have a function A() which in turn calls functions b() and c() ,
> if i want commit something in b or c. i have to use
You cannot start or commit a transaction inside a function. You can use
savepoints.
--
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
Do you want to know God? http://www.lfix.co.uk/knowing_god.html
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Problem while using start transaction ans commit;
@ 2005-10-18 23:13 Stewart Ben (RBAU/EQS4) * <Ben.Stewart@au.bosch.com>
0 siblings, 0 replies; 3+ messages in thread
From: Stewart Ben (RBAU/EQS4) * @ 2005-10-18 23:13 UTC (permalink / raw)
To: pgsql-sql
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.courseareas IN 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
SELECT transid 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/
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-10-19 07:45 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-17 07:23 Problem while using start transaction ans commit; Sri <asrinivas@effigent.net>
2005-10-19 07:45 ` Oliver Elphick <olly@lfix.co.uk>
2005-10-18 23:13 Re: Problem while using start transaction ans commit; Stewart Ben (RBAU/EQS4) * <Ben.Stewart@au.bosch.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox