pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedtransaction management in plpgsql functions
3+ messages / 3 participants
[nested] [flat]
* transaction management in plpgsql functions
@ 2003-11-06 09:24 Cris Carampa <cris119@operamail.com>
2003-11-09 20:36 ` Re: transaction management in plpgsql functions Christopher Browne <cbbrowne@acm.org>
2003-11-09 21:14 ` Re: transaction management in plpgsql functions Chester Kustarz <chester@arbor.net>
0 siblings, 2 replies; 3+ messages in thread
From: Cris Carampa @ 2003-11-06 09:24 UTC (permalink / raw)
To: pgsql-sql
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?
Kind regards,
--
Cris Carampa (spamto:cris119@operamail.com)
"Poveri fanatici comunisti, noglobal e affetti dalla sindrome
anti-microsoft" (gli utenti Linux secondo un poster di ICOD)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: transaction management in plpgsql functions
2003-11-06 09:24 transaction management in plpgsql functions Cris Carampa <cris119@operamail.com>
@ 2003-11-09 20:36 ` Christopher Browne <cbbrowne@acm.org>
1 sibling, 0 replies; 3+ messages in thread
From: Christopher Browne @ 2003-11-09 20:36 UTC (permalink / raw)
To: pgsql-sql
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?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: transaction management in plpgsql functions
2003-11-06 09:24 transaction management in plpgsql functions Cris Carampa <cris119@operamail.com>
@ 2003-11-09 21:14 ` Chester Kustarz <chester@arbor.net>
1 sibling, 0 replies; 3+ messages in thread
From: Chester Kustarz @ 2003-11-09 21:14 UTC (permalink / raw)
To: Cris Carampa <cris119@operamail.com>; +Cc: pgsql-sql
http://www.postgresql.org/docs/7.2/interactive/plpgsql-structure.html
"It is important not to confuse the use of BEGIN/END for grouping statements in PL/pgSQL with the database commands for transaction control. PL/pgSQL's BEGIN/END are only for grouping; they do not start or end a transaction. Functions and trigger procedures are always executed within a transaction established by an outer query --- they cannot start or commit transactions, since PostgreSQL does not have nested transactions."
Makes sense if you think about it.
On Thu, 6 Nov 2003, Cris Carampa wrote:
> 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?
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-11-09 21:14 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-11-06 09:24 transaction management in plpgsql functions Cris Carampa <cris119@operamail.com>
2003-11-09 20:36 ` Christopher Browne <cbbrowne@acm.org>
2003-11-09 21:14 ` Chester Kustarz <chester@arbor.net>
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