[asterisk-dev] [Code Review] res_config_pgsql needs db reconnection support

[kobaz]

> On 2010-11-24 03:52:05, Olle E Johansson wrote:
> > trunk/res/res_config_pgsql.c, line 228
> > <https://reviewboard.asterisk.org/r/944/diff/2/?file=12629#file12629line228>
> >
> >     On line 223 you check that attempts is lower than 2, now you want it to be greater than 1... Am I missing something?

It's a bit of trickery to basically try the query twice. And print a note if we succeeded on the second attempt.

First run through the first loop iteration... the condition (line 223) will be (0 < 2).

With a query on a disconnected handle (line 238) (res == -2), it will loop again, the condition (line 223) is now (1 < 2).  But then the attempts++ makes it 2.  If attempts is > 1, which it is now, which signifies the query was tried more than once.  it'll print a bit of a notice/warning noting that the query previously failed but succeeded after a reconnect.

So here's the flow:
query... get result
if failed, note the failure, try again
query again, get result, if successful, print we succeeded and bail


> On 2010-11-24 03:52:05, Olle E Johansson wrote:
> > trunk/res/res_config_pgsql.c, line 152
> > <https://reviewboard.asterisk.org/r/944/diff/2/?file=12629#file12629line152>
> >
> >     Please always log more information - which database and host failed? "Reconnect failed" doesn't say much to the poor admin.

Yeah, good point.  I'm a big fan of logging as much as possible.  I'll have to look up what's available for getting postgres connection errors.


> On 2010-11-24 03:52:05, Olle E Johansson wrote:
> > trunk/res/res_config_pgsql.c, line 149
> > <https://reviewboard.asterisk.org/r/944/diff/2/?file=12629#file12629line149>
> >
> >     Should we use PostgreSQL or just "postgres" - we have a mix in the messages here. (Check line 166)
> >     
> >     Also - is it "not defined" or "not active" ? Well it's just a debug message.. But anyway.

Sounds good... I'll make everything PostgreSQL.


- kobaz


-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviewboard.asterisk.org/r/944/#review2972
-----------------------------------------------------------


On 2010-09-28 15:46:25, kobaz wrote:
> 
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviewboard.asterisk.org/r/944/
> -----------------------------------------------------------
> 
> (Updated 2010-09-28 15:46:25)
> 
> 
> Review request for Asterisk Developers.
> 
> 
> Summary
> -------
> 
> If your postgres connection died suddenly in between res_config_pgsql queries, the query will fail because the query is executed on a disconnected/disconnecting handle.
> 
> Reviewboard patch adds support for reconnecting and retrying the failed query if the connection was closed while executing the query. The patch also removes lots of duplication of checking for handling query errors.
> 
> Ideally there should be a centralized res module for postgres connections that cdr_pgsql and res_config_pgsql (and any other pgsql dependent module) can use for a centralized db abstraction module.
> 
> But I believe fixing up res_config_pgsql is the first step.
> 
> 
> Diffs
> -----
> 
>   trunk/res/res_config_pgsql.c 289174 
> 
> Diff: https://reviewboard.asterisk.org/r/944/diff
> 
> 
> Testing
> -------
> 
> res_pgsql_conf.
> ===============
> [general]
> dbhost=localhost
> dbport=5432
> dbname=pbx
> dbuser=root
> dbpass=password
> 
> 
> extconfig.conf
> ===================
> [settings]
> voicemail => pgsql,pbx,asterisk.v_asterisk_voicemail_config
> 
> ===================
> 
> Start asterisk
> (kill all current connections, replace pbx with appropriate database name)
> kill `ps aux | grep postgres | grep pbx | awk {'print $2'}`
> 
> Launch voicemail (or something else that uses res_config_pgsql)
> 
> [Sep 28 16:02:31] ERROR[23650]: res_config_pgsql.c:167 _pgsql_exec: PostgreSQL RealTime: Failed to query 'asterisk.v_asterisk_voicemail_config at pbx'.
> [Sep 28 16:02:31] ERROR[23650]: res_config_pgsql.c:168 _pgsql_exec: PostgreSQL RealTime: Query Failed: SELECT * FROM asterisk.v_asterisk_voicemail_config WHERE mailbox = '2702' AND context = 'internal'
> [Sep 28 16:02:31] ERROR[23650]: res_config_pgsql.c:169 _pgsql_exec: PostgreSQL RealTime: Query Failed because: FATAL:  terminating connection due to administrator command
> server closed the connection unexpectedly
>         This probably means the server terminated abnormally
>         before or while processing the request.
>  (PGRES_FATAL_ERROR)
> [Sep 28 16:02:31] NOTICE[23650]: res_config_pgsql.c:230 pgsql_exec: PostgreSQL RealTime: Query finally succeeded: SELECT * FROM asterisk.v_asterisk_voicemail_config WHERE mailbox = '2702' AND context = 'internal'
>     -- <SIP/2701-00000005> Playing 'vm-theperson.ulaw' (language 'en')
>     -- <SIP/2701-00000005> Playing 'digits/2.ulaw' (language 'en')
>     -- <SIP/2701-00000005> Playing 'digits/7.ulaw' (language 'en')
>     -- <SIP/2701-00000005> Playing 'digits/0.ulaw' (language 'en')
>     -- <SIP/2701-00000005> Playing 'digits/2.ulaw' (language 'en')
> 
> 
> Thanks,
> 
> kobaz
> 
>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.digium.com/pipermail/asterisk-dev/attachments/20101225/2b3cd6c3/attachment.htm>