Re: [sr-dev] db_fetch_next problem with Postgres?

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, 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 Sent: Tuesday, December 13, 2011 12:27 PM To: Development mailing list of the sip-router project Cc: Paul Pankhurst 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 Sent: Tuesday, December 13, 2011 12:03 PM To: miconda(a)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 Sent: Tuesday, December 13, 2011 11:10 AM To: Development mailing list of the sip-router project Cc: Paul Pankhurst 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(a)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