Thread: 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?

> ... 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.

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/

=?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