From: Christopher Browne <cbbrowne@acm.org>
Subject: Re: transaction management in plpgsql functions
Date: Sun, 09 Nov 2003 15:36:38 -0500
Organization: cbbrowne Computing Inc
Lines: 39
Message-ID: <m3he1dxort.fsf@wolfe.cbbrowne.com>
References: <bod454$e2o$1@panco.nettuno.it>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
User-Agent: Gnus/5.1002 (Gnus v5.10.2) XEmacs/21.4 (Reasonable Discussion,
	linux)
Cancel-Lock: sha1:7KHOFdG47IS1M0D5M3GZKPDlHgY=
To: pgsql-sql@postgresql.org

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?