Re: Actions requiring commit

> On Mon, Feb 15, 2010 at 1:50 AM, Lee Horowitz <leeh@panix.com> wrote:
>>
>> Does that go for pl/pgsql as well?
>>
>> If I call a pl/pgsql procedure from the psql  command line (or from
>> another pl/pgsql procedure?) will commits in the called procedure cause
>> commit in the calling procedure as well, or will the caller and callee
>> have in effect different "threads" or sessions so that their respective
>> commits are separated?
>>
>
> pl/pgsql functions can't commit. Any database modifications are
> committed at the end of the transaction. We currently don't have
> "stored procedures" which live outside of transactions and can start
> and commit transactions on their own, only functions which live
> entirely within one transaction.
>
Uh oh! Lets see if I've got this right.
A pl/perl or pl/python routine CAN commit, but a pl/pgsql procedure can not?

Suppose I have a psql script that inserts a row into table A and then calls
a pl/pgsql routine that inserts into table B. Upon return from the pl/pgsql
routine, I can commit (thereby keeping the data in both tables), or I can
roll back, (thus losing the data in both tables).

If, instead of inserting into table B I were to write to a "flat" file,
same story. The flat file would either be written if we issue a commit
from the calling psql routine or be lost if we roll back.

Ah, but if the called routine were pl/perl or pl/python then things would
be different? Then in that case, we insert into table A in the psql
routine, call the pl/perl or pl/python routine that inserts into table B
or writes to a flat file, and now, still in the pl/perl or pl/sql routine
we can commit (hence keeping table B and or the data written to the flat
file) and then return back to the psql routine where we can either commit
table A or roll it back?