Re: transactions within functions

Steve Holdoway wrote:
> Yes, I know it's not possible, but can anyone suggest an alternative for
> this problem?
>
> I've written a very simple trigger-driven replication system, which
> works in stages. First the trigger generates an entry in a log table
> which is a fully formatted sql command... insert into/delete from, etc.
> Secondly, this table is transferred to the receiving database, and
> cleared down. This all works fine.
>
> On the receiving end, there is a cron job that processes all of the
> commands in this table. However, this is written as a plpgsql function,
> so it's 'all or nothing'... ie any errors in the data mean that all
> successful updates preceeding this error are rolled back. This makes
> finding and debugging the data errors extremely difficult, but, more
> importantly, stops the update process cold.
>
> I have tried calling a child function from the parent to perform the
> update in batches, but it still exhibits the same 'all or nothing'
> functionality.
>
> Can anyone suggest a way that I can get around this?
>
The easisest way is probably let your cron job be a small client program
using one of the available interfaces and call a plsql function from
there, once for each batch. Each call followed by a commit. This
approach will give you full control, both with respect to transactions
and logging/debugging.

Kind regards,

Thomas Hallgren