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: (view raw, whole thread or download thread mbox)
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).


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



In response to


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-2017 The PostgreSQL Global Development Group