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 17:14:24
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
> You can't commit inside a function regardless of the language.
> Functions live entirely inside a transaction.
OK, now I imagine you're thinking "What's wrong with that dufus, how many
times do I have to tell him you can't commit from inside a function before
he gets it? "

My problem is that I'be been told that I can use dblink() to make a second
connection to the same database, and then, on that second connection, do
insert to some table and issue a commit which will make the data in that
commit visible to other sessions, but NOT commit any pending data on the
first connection.

An underground assumption I must have made, was that opening the second
connection, doing the insert to table B and subsequent commit, all that
was to be done inside a function called from psql or from some pl/xxx
routine which itself is called from psql.

If I've "got" what you're trying to tel me, that can't be the way to do it.

We've got to be in the original psql script. We do an insert into table A,
we open a second connection (right there in the script? cant do it in a
function?) then insert into table B using the second connection, then
commit the second connection (all in the main psql routine) and then we
can commit or roll back table A as circumstances dictate. Either way, data
in table B persists and is visible to other sessions.

Am I getting warmer?

In response to


pgsql-novice by date

Next:From: Greg StarkDate: 2010-02-15 17:36:28
Subject: Re: Actions requiring commit
Previous:From: rafahoiroDate: 2010-02-15 14:59:46
Subject: Instalar postgresql windows 7

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