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

Re: Actions requiring commit

From: Greg Stark <gsstark(at)mit(dot)edu>
To: Lee Horowitz <leeh(at)panix(dot)com>
Cc: Lee <lee(at)jamtoday(dot)com>, pgsql-novice(at)postgresql(dot)org
Subject: Re: Actions requiring commit
Date: 2010-02-15 04:41:50
Message-ID: 407d949e1002142041k48a9e9b0x2b7f8eb267b30eea@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

Responses

pgsql-novice by date

Next:From: Jasen BettsDate: 2010-02-15 07:16:20
Subject: Re: Actions requiring commit
Previous:From: Lee HorowitzDate: 2010-02-15 04:31:45
Subject: Re: Actions requiring commit

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