Skip site navigation (1) Skip section navigation (2)

Re: Actions requiring commit

From: "Lee Horowitz" <leeh(at)panix(dot)com>
To: "Greg Stark" <gsstark(at)mit(dot)edu>
Cc: "Lee Horowitz" <leeh(at)panix(dot)com>, "Lee" <lee(at)jamtoday(dot)com>, pgsql-novice(at)postgresql(dot)org
Subject: Re: Actions requiring commit
Date: 2010-02-15 04:31:45
Message-ID: b4fb0c7593700693292999180ee5fb0f.squirrel@mail.panix.com (view raw or flat)
Thread:
Lists: pgsql-novice
> On Mon, Feb 15, 2010 at 1:50 AM, Lee Horowitz <leeh(at)panix(dot)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?




In response to

Responses

pgsql-novice by date

Next:From: Greg StarkDate: 2010-02-15 04:41:50
Subject: Re: Actions requiring commit
Previous:From: John DeSoiDate: 2010-02-15 03:39:07
Subject: Re: Function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group