Re: Partial commit within the trasaction

On Thu, Sep 08, 2005 at 03:39:50PM +0200, Bohdan Linda wrote:
> On Thu, Sep 08, 2005 at 02:53:47PM +0200, Michael Fuhr wrote:
> > One way would be to use contrib/dblink to open another connection
> > to the database so the status messages could be inserted in a
> > separate transaction.
>
> This could do the trick for logging, even writting of a package that would
> do all the stuff should not be hard. But what if you want to flush
> something processed to db. Consider you are doing massive updates/deletes.
> Again in logical blocks. You as a programmer may decide: "ok so far I am
> done and even if I crash I want to preserve these changes." It happened
> me, that db aborted processing such huge updates with out of memory
> message. Would calling stored procedure from stored procedure solved
> this? Or if parent procedure is not commited then even called procedure
> will not commit?

Functions are executed in the context of an outer transaction, so
if that outer transaction fails then the function's changes will
be rolled back. Another implication of this is that functions can't
start or commit/rollback transactions because they're already inside
a transaction, although in 8.x they can use exception handlers to
do partial rollbacks (i.e., functions can use savepoints, albeit
not directly with a SAVEPOINT statement).

If you want changes to survive a database or system crash then
you'll have to commit them. Since server-side functions can't start
or commit transactions, you'll have to do those commits with client
code, either from a client application or by using dblink or something
similar from a server-side function, effectively making the server-side
function a client application.

This might not be what you're after, but 8.1 will have two-phase
commit, which is a way to tell the database "get ready to commit,
but don't actually do it until I tell you." Those prepared commits
will survive a crash, so after you recover you can say, "Remember
that transaction that you prepared before the crash? Go ahead and
commit it now." You have to do some extra bookkeeping and you can't
commit several prepared transactions atomically (as far as I know),
but that's one way you could make changes durable without actually
committing them until later.

--
Michael Fuhr