In-Reply-To: <26792.1144949880@sss.pgh.pa.us>
References: <1144135482.063425.33380@z34g2000cwc.googlegroups.com>
	<20060411225515.GZ49405@pervasive.com> <op.s7vvyggtcigqcu@apollo13>
	<200604121603.43986.josh@agliodbs.com>
	<20060413001024.GP49405@pervasive.com>
	<6236.1144888514@sss.pgh.pa.us>
	<4D23826E-7325-4946-B79A-80E321E0E497@gmail.com>
	<26792.1144949880@sss.pgh.pa.us>
Mime-Version: 1.0 (Apple Message framework v749.3)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <CBA568C8-227E-47D4-924B-506AF756DFB0@pervasive.com>
Cc: Christian Storm <christian.storm@gmail.com>,
	Josh Berkus <josh@agliodbs.com>, pgsql-performance@postgresql.org,
	PFC <lists@peufeu.com>
Content-Transfer-Encoding: 7bit
From: Jim Nasby <jnasby@pervasive.com>
Subject: Re: pgmemcache 
Date: Thu, 13 Apr 2006 13:29:25 -0500
To: Tom Lane <tgl@sss.pgh.pa.us>

On Apr 13, 2006, at 12:38 PM, Tom Lane wrote:

> Christian Storm <christian.storm@gmail.com> writes:
>> Not sure if I follow why this is a problem.  Seems like it would be
>> beneficial to have both BEFORE and AFTER COMMIT triggers.
>> With the BEFORE COMMIT trigger you would have the ability to 'un-
>> commit' (rollback) the transaction.  With
>> the AFTER COMMIT trigger you wouldn't have that option because the
>> commit has already been successful.  However,
>> with an AFTER COMMIT you would be able to trigger other downstream
>> events that rely on a transaction successfully committing.
>
> An AFTER COMMIT trigger would have to be in a separate transaction.
> What happens if there's more than one, and one of them fails?  Even
> more to the point, if it's a separate transaction, don't you have
> to fire all these triggers again when you commit that transaction?
> The idea seems circular.

I suspect that in reality you'd probably want each on-commit trigger  
to be it's own transaction, but it depends on what you're doing.  
Also, I can't see any use for them where you'd actually be  
interacting with the database, only if you were calling something  
externally via a function. One example would be sending an email out  
when a certain table changes; in many cases it's better to let the  
change happen even if the email can't be sent, and you'd rather not  
send an email if the transaction just ends up rolling back for some  
reason. And yes, you'd have to ensure you didn't code yourself up a  
trigger loop.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461