[GENERAL] basic stored proc/transaction question

[Ben]

My understanding is that a stored procedure does an implicit begin/commit when 
it executes. Maybe my brain isn't working so well this morning, because I can't 
figure out how I would do:


begin;
call stored proc;
call another stored proc;
commit;

It seems that the transaction would be committed after the first call.

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] basic stored proc/transaction question

[Ian Harding]

On 3/24/06, Ben [EMAIL PROTECTED] wrote:
 My understanding is that a stored procedure does an implicit begin/commit when
 it executes. Maybe my brain isn't working so well this morning, because I 
 can't
 figure out how I would do:

 begin;
 call stored proc;
 call another stored proc;
 commit;

 It seems that the transaction would be committed after the first call.

Nope.  Unless you use the new SAVEPOINT stuff, the explicit
transaction is the transaction.  Any error in any function will
rollback the whole thing.  The commit happens at the explicit commit.

Every SQL statement (such as calling a function) runs in an implicit
transaction.  Explicit transactions effectively group these implicit
transactions such that any one failure causes them all to fail.

- Ian

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] basic stored proc/transaction question

[Ben]

Well, that's awesome. Thanks!

On Fri, 24 Mar 2006, Ian Harding wrote:


On 3/24/06, Ben [EMAIL PROTECTED] wrote:

My understanding is that a stored procedure does an implicit begin/commit when
it executes. Maybe my brain isn't working so well this morning, because I can't
figure out how I would do:

begin;
call stored proc;
call another stored proc;
commit;

It seems that the transaction would be committed after the first call.


Nope.  Unless you use the new SAVEPOINT stuff, the explicit
transaction is the transaction.  Any error in any function will
rollback the whole thing.  The commit happens at the explicit commit.

Every SQL statement (such as calling a function) runs in an implicit
transaction.  Explicit transactions effectively group these implicit
transactions such that any one failure causes them all to fail.

- Ian



---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match