[sr-dev] db_fetch_next problem with Postgres?

Wed Dec 14 17:15:14 CET 2011

I have a code change that resolves this issue in a flexible way, before I check it in can anyone see any flaws in it?

I decided to allow the first character of the db_url to be set to ‘*’ to indicate that non-pooling is required. 
In this way the pooling can be controlled from the config file giving extra flexibility.
I have added a new variable poolid to  db_id struct. 
By default this is zero, however in the case that non-pooling is required the poolid is set to a unique value within that pid ensuring the db_id struct is unique and non pooled.

Any comments? Good or bad 

The code changes are fairly minimal and limited to lib/srdb1: db_id.c, db_id.h, db.c

Paul

From: Daniel-Constantin Mierla 
Sent: Tuesday, December 13, 2011 3:53 PM
To: Development mailing list of the sip-router project 
Cc: Paul Pankhurst 
Subject: Re: [sr-dev] db_fetch_next problem with Postgres?

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 
  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 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 
    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 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/20111214/04615b5f/attachment-0001.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: image/png
Size: 1041 bytes
Desc: not available
URL: <http://lists.sip-router.org/pipermail/sr-dev/attachments/20111214/04615b5f/attachment-0001.png>