nested transaction
Hello,
The new release includes savepoints, but I need to do a begin inside another
begin and a commit/rollback after another commit/rollback.
For example, I have a pA procedure that update some data, so this procedure
execute a begin, after manipulate data and ends with commit/rollback. pA
procedure can to be called directly by the client aplication. In other hand,
I have a pB procedure that also update some data and calls pA procedure, so
pB procedure execute a begin, after manipulate data, after call pA procedure
and ends with commit/rollback. pB procedure can also to be called directly
by the client aplication.
The problem is that pA procedure can't do a new transaction, so what can I
do?
Is possible to detect from pA that there is a transaction and so do a
savepoint instead of a begin?
Thanks in advance,
pere
Am Dienstag, 10. August 2004 13:30 schrieb "Rodrï¿œguez Rodrï¿œguez, Pere":
For example, I have a pA procedure that update some data, so this procedure
execute a begin, after manipulate data and ends with commit/rollback. pA
procedure can to be called directly by the client aplication. In other
hand, I have a pB procedure that also update some data and calls pA
procedure, so pB procedure execute a begin, after manipulate data, after
call pA procedure and ends with commit/rollback. pB procedure can also to
be called directly by the client aplication.The problem is that pA procedure can't do a new transaction, so what can I
do?
Use savepoints all the time.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
For example, I have a pA procedure that update some data, so this
procedure
execute a begin, after manipulate data and ends with commit/rollback. pA
procedure can to be called directly by the client aplication. In other
hand, I have a pB procedure that also update some data and calls pA
procedure, so pB procedure execute a begin, after manipulate data, after
call pA procedure and ends with commit/rollback. pB procedure can also to
be called directly by the client aplication.The problem is that pA procedure can't do a new transaction, so what can
I
do?
Use savepoints all the time.
then, when I must open the transaction?, where I put begin command?, and
commit/rollback?
The two store procedures, pA and pB, can to be called directly by the client
aplication, so pA and pB must open a transaction, but if pA is called by pB,
then pA can't do a new begin command, insted of pA can do a savepoint if pA
can detect that there is alredy an open transaction, but, how can pA detect
that there is an open transaction?
Import Notes
Resolved by subject fallback
... but, how can pA detect that there is an open transaction?
If I'm not mistaken, there alwasy must be a transaction in process, even
if it is an implicit one, so you can alwys count on a transaction being
opened.
Cheers,
Csaba.
=?ISO-8859-1?Q?=22Rodr=EDguez_Rodr=EDguez=2C_Pere=22?= <prr@hosppal.es> writes:
Is possible to detect from pA that there is a transaction and so do a
savepoint instead of a begin?
Depends on what you're programming in, but in principle yes. If using
libpq, see PQtransactionStatus().
regards, tom lane
Am Dienstag, 10. August 2004 15:17 schrieb Csaba Nagy:
... but, how can pA detect that there is an open transaction?
If I'm not mistaken, there alwasy must be a transaction in process, even
if it is an implicit one, so you can alwys count on a transaction being
opened.
Correct.
The only variation is that the top-level query processor will start a
transaction implicitly and commit it after the statement if you don't start
one explicitly. But functions are always in some transaction, and need not
care whether it was explicit or not.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/