[sr-dev] db_fetch_next problem with Postgres?

[Daniel-Constantin Mierla]

On 12/13/11 4:16 PM, Paul Pankhurst wrote:
> Thanks for that Daniel.
> I modified the db_url for 2nd db so it used the actual IP address of 
> my db rather than localhost,
nice idea, indeed -- perhaps can be extended to add some custom 
hostnames pointing to same in local computer's /etc/hosts, in case one 
would need more dedicated connection. But it would be better if APi gets 
extended.

Cheers,
Daniel

> hence it didn't get pooled, and everything magically worked.
> I think I'll extend the db api, it doesn't look too difficult to do.
> Paul
> *From:* Daniel-Constantin Mierla <mailto:miconda at gmail.com>
> *Sent:* Tuesday, December 13, 2011 12:27 PM
> *To:* Development mailing list of the sip-router project 
> <mailto:sr-dev at lists.sip-router.org>
> *Cc:* Paul Pankhurst <mailto:paul at crocodile-rcs.com>
> *Subject:* Re: [sr-dev] db_fetch_next problem with Postgres?
> Hello,
>
> On 12/13/11 1:08 PM, Paul Pankhurst wrote:
>> should have sent this to list -- sorry
>> *From:* Paul Pankhurst <mailto:paul at crocodile-rcs.com>
>> *Sent:* Tuesday, December 13, 2011 12:03 PM
>> *To:* miconda at gmail.com <mailto:miconda at gmail.com>
>> *Subject:* Re: [sr-dev] db_fetch_next problem with Postgres?
>> Hi Daniel,
>> I have a bit more information...
>> the outline pseudo code for what I am doing looks like this (removing 
>> non relevant stuff)
>> db_fetch_query( xcap_db )
>> do
>> {
>> query( another_2nd_db )
>> } while ( db_fetch_next( xcap_db) )
>> it is the query to the second database that breaks everything...if I 
>> remove it the problem goes away and db_fetch_next works as expected.
>> The access to the 2nd database uses completely different db_func_t 
>> and db1_cont_t structures to the xcap_db
>
> if it is the same db_url value, then it results in same db1_con_t 
> pointers, since the postgres has connection pool inside and matches an 
> old connection for same db_url when initializing the second one.
>
> Some ideas:
> - extend db api with a new function init_new that will always return a 
> new connection -- requires coding in db modules
> - make another database where you add a view to the table in initial 
> database and work with it for second connection -- should work without 
> touching kamailio code
>
> Cheers,
> Daniel
>
>> I'll try running it with extra debug shortly to see if I can see 
>> what's happening
>> Paul
>> *From:* Daniel-Constantin Mierla <mailto:miconda at gmail.com>
>> *Sent:* Tuesday, December 13, 2011 11:10 AM
>> *To:* Development mailing list of the sip-router project 
>> <mailto:sr-dev at lists.sip-router.org>
>> *Cc:* Paul Pankhurst <mailto:paul at crocodile-rcs.com>
>> *Subject:* Re: [sr-dev] db_fetch_next problem with Postgres?
>> Hello,
>>
>> On 12/13/11 11:40 AM, Paul Pankhurst wrote:
>>> I am having problems with the db_fetch_next routine  and Postgres.
>>> I can't see any problem with my code that calls it, and as far as I 
>>> can tell the problem appears to be in the db_postgres_fetch_result 
>>> routine.
>>> On the first invocation of db_postgres_fetch_result  it allocates a 
>>> result structure and gives me the first 5 rows of the 198 that match 
>>> my query after calling db_postgres_convert_rows.
>>> On the 2nd invocation it fails to retrieve the next 5 rows because 
>>> CON_RESULT(_con) is NULL !
>>> Anyone got any suggestions?
>> can you doublecheck and see if you don't call free_result() before 
>> fetching the next rows? If you run with debug=3, if there is a 
>> free_result, you should see the log message from:
>>
>> LM_DBG("PQclear(%p) result set\n", CON_RESULT(_con))
>>
>> Cheers,
>> Daniel
>> -- 
>> Daniel-Constantin Mierla --http://www.asipto.com
>> http://linkedin.com/in/miconda  -- http://twitter.com/miconda
>>
>>
>> _______________________________________________
>> sr-dev mailing list
>> sr-dev at lists.sip-router.org
>> http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev
>
> -- 
> Daniel-Constantin Mierla --http://www.asipto.com
> http://linkedin.com/in/miconda  -- http://twitter.com/miconda
>
>
> _______________________________________________
> sr-dev mailing list
> sr-dev at lists.sip-router.org
> http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev

-- 
Daniel-Constantin Mierla -- http://www.asipto.com
http://linkedin.com/in/miconda -- http://twitter.com/miconda

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sip-router.org/pipermail/sr-dev/attachments/20111213/a9d903ae/attachment.htm>