Re: Actions requiring commit

On Mon, Feb 15, 2010 at 4:31 AM, Lee Horowitz <leeh(at)panix(dot)com> wrote:
> 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).

Correct

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

The flat file is outside Postgres's control. The modification to it
will happen as soon as the perl or python or plpgsql code is run and
can't be rolled 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?

You can't commit inside a function regardless of the language.
Functions live entirely inside a transaction.

>

--
greg