On 5/19/2010 5:00 AM, Jan Wieck wrote:
> On 5/19/2010 3:25 PM, Jeff wrote:
>> This is going to sound very very odd and highly improbable.  We've had  
>> a long time intermittent issue where a slave will get a primary key  
>> violation on a replicated table.   Sometimes we go a few weeks without  
>> it occurring, sometimes a few hours. Until now I hadn't been motivated  
>> enough to really dig in (as it often liked to do so at inopportune  
>> times).  This has happened under PG 8.2 and 8.4 (we're currently on  
>> 8.4, both origin & slave) and on various slony1 versions (we're  
>> currently on 1.2.17).
>> 
>> The code which is causing the problem is a plpgsql function fired from  
>> a trigger.
>> It basically does:
>> begin;
>> 	delete from thetable where id = v_id;
>> 	insert into thetable (id, otherjunk) values (v_id, v_otherjunk);
>> end;
>> 
>> "thetable" has a pk on the id column.
>> 
>> Now, for the evidence - this is pulled from sl_log_2 and while I can't  
>> include log_cmddata here, here is the rest:
>> 
>>    log_xid   | log_actionseq | log_cmdtype
>> ------------+---------------+-------------
>>   1153890130 |    2800679119 | I
>>   1153890130 |    2800679120 | D
>>   1153890760 |    2800716473 | D
>>   1153890760 |    2800716474 | I
>>   1153919695 |    2800872885 | D
>>   1153919695 |    2800880852 | I
> 
> I have no idea why that would happen. I will need to check if the 
> trigger queue of 8.4 may do funky stuff here, like actually firing all 
> those AFTER triggers when the stored procedure returns, rather than at 
> the end of each command within your procedure.

I created a very small test and it suggests that the triggers fire 
during the execution of the function, not at the end. So there goes that 
idea.


Jan

-- 
Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin