Re: ROLLBACK triggers?

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