From: Christopher Browne <cbbrowne@acm.org>
To: pgsql-sql@postgresql.org
Subject: Re: transaction management in plpgsql functions
Date: Sun, 09 Nov 2003 15:36:38 -0500
Message-ID: <m3he1dxort.fsf@wolfe.cbbrowne.com> (raw)
References: <bod454$e2o$1@panco.nettuno.it>
After takin a swig o' Arrakan spice grog, Cris Carampa <cris119@operamail.com> belched out...:
> It seems that transaction management statements (begin work...commit
> work) are not allowed into plpgsql functions. Is it true? If true,
> what happens if I put a DML statement into a function? Is it
> automatically commited every time the function executes? Is there no
> way to rollback the changes?
The "problem" with using BEGIN/COMMIT in plpgsql is fundamentally that
those functions have to be _started_ in the context of a transaction,
so by the time they get started, there is already a transaction in
progress.
If-and-when support for nested transactions gets into place, you would
presumably be able to have nested transactions inside functions.
What happens may be a little different from what you think; things are
not COMMITted when the function executes, but rather when the COMMIT
takes place /on the transaction in which the function runs/.
Thus...
BEGIN;
INSERT INTO T1 (4, 5);
INSERT INTO T2 (6, 7, NOW());
SELECT FUNNY_FUNCTION(4,5,6,7, NOW());
DELETE FROM T1;
DELETE FROM T2;
COMMIT;
All of the changes commit as of the COMMIT statement at the end, and
not before.
If you had DML creating table T3 in FUNNY_FUNCTION, then T3 would not
become visible to other users until the COMMIT, although the current
transaction could readily add/modify records before the COMMIT.
--
(format nil "~S@~S" "cbbrowne" "cbbrowne.com")
http://cbbrowne.com/info/sap.html
If you're sending someone some Styrofoam, what do you pack it in?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: cbbrowne@acm.org
Subject: Re: transaction management in plpgsql functions
In-Reply-To: <m3he1dxort.fsf@wolfe.cbbrowne.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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