Skip site navigation (1) Skip section navigation (2)

Re: ROLLBACK triggers?

From: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
To: Daisuke Maki <daisuke(at)wafu(dot)ne(dot)jp>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: ROLLBACK triggers?
Date: 2006-01-23 17:06:09
Message-ID: 20060123170609.GN20182@pervasive.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Mon, Jan 23, 2006 at 06:35:18PM +0900, Daisuke Maki wrote:
> A quick scan of the docs and the source code tree seems to indicate that
> there is no such thing as a rollback trigger, short of hacking it.
> 
> Now, I'm wondering:
>   1. Is there a rollback/commit trigger? If not, is it planned to be
>      implemented at all?
>   2. Is there a way to undo changes to data external to postgres
>      when a rollback occurs, OR, only update that external data
>      when a commit occurs?

Actually, this is something that often comes up in terms of
LISTEN/NOTIFY and doing external non-transactional stuff like sending
emails. AFAIK there's no plans to add support for anything like a
rollback trigger.

Your best bet (short of hacking the backend) is to have your triggers
(or maybe this would mean you could just do this with rules, which could
be faster) record the relevant information in a staging table. After the
transaction commits, you can then pull the info out of the staging table
and use it to update your index. You could also use LISTEN/NOTIFY to
speed this process up.

Of course that means there will be a period of time where the index is
out-of-date, so perhaps there is some argument to be made for a ROLLBACK
trigger.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby(at)pervasive(dot)com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

In response to

Responses

pgsql-hackers by date

Next:From: Stef TDate: 2006-01-23 17:30:59
Subject: Re: ROLLBACK triggers?
Previous:From: Jonah H. HarrisDate: 2006-01-23 17:05:49
Subject: Re: ROLLBACK triggers?

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