On 5/20/2010 8:24 AM, Jeff wrote:
> On May 19, 2010, at 5:00 AM, Jan Wieck wrote:
> 
>>
>>
>> Notice also that there is a 1000+ bump in the log_actionsequence for  
>> the
>> two operations done by the last transaction. This suggests that  
>> somehow
>> that sequence is configured for cacheing, which is a bad thing in  
>> general.
>>
> 
> The db is quite write heavy - so it is feasible that it bumped by 1000  
> in that window. (We generate around 5500 wal segments a day on the  
> origin to give you an idea)

If the sl_action_seq has a cache_value of 1 (as it should be), then this 
bump would mean that between the DELETE and the INSERT, which are 
consecutive statements inside the same PL/pgSQL function, other 
transactions were able to call nextval('sl_action_seq') 1,000+ times.

> 
> Yeah, I'm still completely baffled - I saved sl_log from yesterday's  
> hiccup - would there be anything else of use to capture when it occurs  
> again?  But I think the smoking gun was that log_actionseq being  
> bassackwards.  If it were something with caching or ordering of  
> triggers we'd be seeing that a whole lot more often I'd wager.  I also  
> find it paticularly interesting that it is almost always (as far as I  
> can remember anyway) this one paticular table.
> 
> to give a bit more details - we have a few tables here, lets call them  
> "sourcereport", "events", and "summary".  All three are replicated.
> 
> An insert is done on the sourcereport table (which fires slon logger)
> This fires a trigger to determine if the contents of that insert are  
> worthy of getting an entry in events. If so, we insert into events.  
> (which fires a slon logger).
> After that trigger we fire the summary trigger which updates the  
> summary table based on the new sourcereport row by doing delete from  
> summary where id = XXX;
> insert into summary (id, blah) select XXX, blah from sourcereport;

Does that happen inside of a transaction context that involves other 
triggers and/or constraints? As per my tests, AFTER user triggers are 
fired from the queue at the end of each statement. The slon logger 
firing out of order of those statements could still point towards a bug 
in the trigger queue, which would be extremely serious.


Jan

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