>
> 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
an
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?