transactions within functions

From: Steve Holdoway <steve(at)treshna(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: transactions within functions
Date: 2004-07-02 00:24:23
Message-ID: 40E4AB37.2040804@treshna.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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?

Cheers,

Steve.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Oliver Jowett 2004-07-02 00:30:07 Re: [Re] Re: PREPARE and transactions
Previous Message Mike Benoit 2004-07-01 23:47:09 Re: Nested Transactions, Abort All