Re: [Sqlgrey-users] 1.6.x and 1.7.x freezes

[Lionel B. <lio...@bo...>]

Rupa Schomaker (lists) wrote the following on 25.07.2005 19:26 :

>On 7/5/2005 5:05 AM, Lionel Bouton wrote:
>  
>
>>Ok,
>>
>>we must find out what triggers these freezes. As I couldn't reproduce
>>them on my systems, I'd like to have some feedback from all 1.6/1.7 users.
>>    
>>
>
>I just started experiencing this problem.  Small volume mail server
>(3-6k msgs a day).  I have to restart sqlgrey a few times a day.
>
> > Could you please fill in the following:
>  
>
>>Do you have freeze problems: (yes/no)
>>    
>>
>
>yes
>
>  
>
>>Latest SQLgrey version used: (1.6.[012] / 1.7.0)
>>    
>>
>
>1.6.4 from sf.net with clean_method = sync.
>  
>

You are the first one to report this bug with "clean_method = sync". 
This is *odd*. I need to be absolutely sure about that: please make sure 
that there's no older sqlgrey version lying around (only 1.6.4 uses the 
clean_method entry correctly).

If this is the case then there probably is a bug in the DBI/DBD layer 
according to the following line you sent in your other mail:

Jul 25 10:18:43 shakti sqlgrey: dbaccess: error: couldn't get reconnect delay: ERROR:  prepared statement "dbdpg_282" does not exist


The code takes great length to make sure the prepared statement indeed 
exists before calling execute (SQLgrey tests if prepare_cached returns a 
defined value before attempting to call execute on it). My guess is that 
there's a bug in DBI that makes it lose the prepared statement just 
after preparing it or checking it is already in its cache.

This is already surprising, but the freeze in itself is even more 
surprising. From the various logs related to this problem it seems 
SQLgrey is blocked trying to reconnect to the database.

Next time you get a freeze, could you
1/ strace the process to see what it is blocking on?
2/ At the same time I'm wondering if DBI is leaking connections, could 
you check the number of open connections on PostgreSQL when a freeze 
occur? You can either do a 'ps aux' on the database server to check how 
many connections there are on the sqlgrey database or connect as the 
'postgres' user on whatever table and execute (I'm not sure if every 
config supports this):
SELECT * from pg_stat_activity WHERE datname = 'sqlgrey';

Lionel.