<HTML><HEAD>
<META content="text/html; charset=ISO-8859-1" http-equiv=Content-Type></HEAD>
<BODY dir=ltr bgColor=#ffffff text=#000000>
<DIV dir=ltr>
<DIV style="FONT-FAMILY: 'Calibri'; COLOR: #000000; FONT-SIZE: 12pt">
<DIV>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?</DIV>
<DIV> </DIV>
<DIV>I decided to allow the first character of the db_url to be set to ‘*’ to
indicate that non-pooling is required. </DIV>
<DIV>In this way the pooling can be controlled from the config file giving extra
flexibility.</DIV>
<DIV>I have added a new variable poolid to db_id struct. </DIV>
<DIV>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.</DIV>
<DIV> </DIV>
<DIV>Any comments? Good or bad <IMG
style="BORDER-BOTTOM-STYLE: none; BORDER-RIGHT-STYLE: none; BORDER-TOP-STYLE: none; BORDER-LEFT-STYLE: none"
class="wlEmoticon wlEmoticon-smile" alt=Smile
src="cid:F2F9428B28044A7790F811BDEFFA90E7@pjplaptop"></DIV>
<DIV> </DIV>
<DIV>The code changes are fairly minimal and limited to lib/srdb1: db_id.c,
db_id.h, db.c</DIV>
<DIV> </DIV>
<DIV>Paul</DIV>
<DIV
style="FONT-STYLE: normal; DISPLAY: inline; FONT-FAMILY: 'Calibri'; COLOR: #000000; FONT-SIZE: small; FONT-WEIGHT: normal; TEXT-DECORATION: none">
<DIV style="FONT: 10pt tahoma">
<DIV><FONT size=3 face=Calibri></FONT> </DIV>
<DIV style="BACKGROUND: #f5f5f5">
<DIV style="font-color: black"><B>From:</B> <A title=miconda@gmail.com
href="mailto:miconda@gmail.com">Daniel-Constantin Mierla</A> </DIV>
<DIV><B>Sent:</B> Tuesday, December 13, 2011 3:53 PM</DIV>
<DIV><B>To:</B> <A title=sr-dev@lists.sip-router.org
href="mailto:sr-dev@lists.sip-router.org">Development mailing list of the
sip-router project</A> </DIV>
<DIV><B>Cc:</B> <A title=paul@crocodile-rcs.com
href="mailto:paul@crocodile-rcs.com">Paul Pankhurst</A> </DIV>
<DIV><B>Subject:</B> Re: [sr-dev] db_fetch_next problem with
Postgres?</DIV></DIV></DIV>
<DIV> </DIV></DIV>
<DIV
style="FONT-STYLE: normal; DISPLAY: inline; FONT-FAMILY: 'Calibri'; COLOR: #000000; FONT-SIZE: small; FONT-WEIGHT: normal; TEXT-DECORATION: none"><BR><BR>On
12/13/11 4:16 PM, Paul Pankhurst wrote:
<BLOCKQUOTE cite=mid:61F8F956E48446FAA98629168A67314E@pjplaptop type="cite">
<DIV dir=ltr>
<DIV style="FONT-FAMILY: 'Calibri'; COLOR: rgb(0,0,0); FONT-SIZE: 12pt">
<DIV>Thanks for that Daniel.</DIV>
<DIV> </DIV>
<DIV>I modified the db_url for 2nd db so it used the actual IP address of my
db rather than localhost,</DIV></DIV></DIV></BLOCKQUOTE>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.<BR><BR>Cheers,<BR>Daniel<BR><BR>
<BLOCKQUOTE cite=mid:61F8F956E48446FAA98629168A67314E@pjplaptop type="cite">
<DIV dir=ltr>
<DIV style="FONT-FAMILY: 'Calibri'; COLOR: #000000; FONT-SIZE: 12pt">
<DIV>hence it didn’t get pooled, and everything magically worked.</DIV>
<DIV> </DIV>
<DIV>I think I’ll extend the db api, it doesn’t look too difficult to
do.</DIV>
<DIV> </DIV>
<DIV>Paul</DIV>
<DIV
style="FONT-STYLE: normal; DISPLAY: inline; FONT-FAMILY: 'Calibri'; COLOR: #000000; FONT-SIZE: small; FONT-WEIGHT: normal; TEXT-DECORATION: none">
<DIV style="FONT: 10pt tahoma">
<DIV> </DIV>
<DIV style="BACKGROUND: #f5f5f5">
<DIV style="font-color: black"><B>From:</B> <A title=miconda@gmail.com
href="mailto:miconda@gmail.com" moz-do-not-send="true">Daniel-Constantin
Mierla</A> </DIV>
<DIV><B>Sent:</B> Tuesday, December 13, 2011 12:27 PM</DIV>
<DIV><B>To:</B> <A title=sr-dev@lists.sip-router.org
href="mailto:sr-dev@lists.sip-router.org" moz-do-not-send="true">Development
mailing list of the sip-router project</A> </DIV>
<DIV><B>Cc:</B> <A title=paul@crocodile-rcs.com
href="mailto:paul@crocodile-rcs.com" moz-do-not-send="true">Paul Pankhurst</A>
</DIV>
<DIV><B>Subject:</B> Re: [sr-dev] db_fetch_next problem with
Postgres?</DIV></DIV></DIV>
<DIV> </DIV></DIV>
<DIV
style="FONT-STYLE: normal; DISPLAY: inline; FONT-FAMILY: 'Calibri'; COLOR: #000000; FONT-SIZE: small; FONT-WEIGHT: normal; TEXT-DECORATION: none">Hello,<BR><BR>On
12/13/11 1:08 PM, Paul Pankhurst wrote:
<BLOCKQUOTE cite=mid:F9EDD430CADE4DCF8BF32BD7071530C8@pjplaptop type="cite">
<DIV dir=ltr>
<DIV style="FONT-FAMILY: 'Calibri'; COLOR: rgb(0,0,0); FONT-SIZE: 12pt">
<DIV>should have sent this to list – sorry</DIV>
<DIV
style="FONT-STYLE: normal; DISPLAY: inline; FONT-FAMILY: 'Calibri'; COLOR: #000000; FONT-SIZE: small; FONT-WEIGHT: normal; TEXT-DECORATION: none">
<DIV style="FONT: 10pt tahoma">
<DIV> </DIV>
<DIV style="BACKGROUND: #f5f5f5">
<DIV style="font-color: black"><B>From:</B> <A title=paul@crocodile-rcs.com
href="mailto:paul@crocodile-rcs.com" moz-do-not-send="true">Paul
Pankhurst</A> </DIV>
<DIV><B>Sent:</B> Tuesday, December 13, 2011 12:03 PM</DIV>
<DIV><B>To:</B> <A title=miconda@gmail.com href="mailto:miconda@gmail.com"
moz-do-not-send="true">miconda@gmail.com</A> </DIV>
<DIV><B>Subject:</B> Re: [sr-dev] db_fetch_next problem with
Postgres?</DIV></DIV></DIV>
<DIV> </DIV></DIV>
<DIV
style="FONT-STYLE: normal; DISPLAY: inline; FONT-FAMILY: 'Calibri'; COLOR: rgb(0,0,0); FONT-SIZE: small; FONT-WEIGHT: normal; TEXT-DECORATION: none">
<DIV dir=ltr>
<DIV style="FONT-FAMILY: 'Calibri'; COLOR: rgb(0,0,0); FONT-SIZE: 12pt">
<DIV>Hi Daniel,</DIV>
<DIV> </DIV>
<DIV>I have a bit more information...</DIV>
<DIV> </DIV>
<DIV>the outline pseudo code for what I am doing looks like this (removing
non relevant stuff)</DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>db_fetch_query( xcap_db )</DIV>
<DIV> </DIV>
<DIV>do</DIV>
<DIV
style="FONT-STYLE: normal; DISPLAY: inline; FONT-FAMILY: 'Calibri'; COLOR: rgb(0,0,0); FONT-SIZE: small; FONT-WEIGHT: normal; TEXT-DECORATION: none">
<DIV style="FONT: 10pt tahoma">
<DIV><FONT size=3 face=Calibri>{</FONT></DIV>
<DIV> </DIV>
<DIV><FONT size=3 face=Calibri>query( another_2nd_db )</FONT></DIV>
<DIV> </DIV>
<DIV><FONT size=3 face=Calibri>} while ( db_fetch_next( xcap_db) )
</FONT></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV><FONT size=3 face=Calibri>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.</FONT></DIV>
<DIV><FONT size=3 face=Calibri>The access to the 2nd database uses
completely different db_func_t and db1_cont_t structures to the
xcap_db</FONT></DIV></DIV></DIV></DIV></DIV></DIV></DIV></DIV></BLOCKQUOTE><BR>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.<BR><BR>Some ideas:<BR>- extend db api
with a new function init_new that will always return a new connection --
requires coding in db modules<BR>- 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<BR><BR>Cheers,<BR>Daniel<BR><BR>
<BLOCKQUOTE cite=mid:F9EDD430CADE4DCF8BF32BD7071530C8@pjplaptop type="cite">
<DIV dir=ltr>
<DIV style="FONT-FAMILY: 'Calibri'; COLOR: #000000; FONT-SIZE: 12pt">
<DIV
style="FONT-STYLE: normal; DISPLAY: inline; FONT-FAMILY: 'Calibri'; COLOR: #000000; FONT-SIZE: small; FONT-WEIGHT: normal; TEXT-DECORATION: none">
<DIV dir=ltr>
<DIV style="FONT-FAMILY: 'Calibri'; COLOR: #000000; FONT-SIZE: 12pt">
<DIV
style="FONT-STYLE: normal; DISPLAY: inline; FONT-FAMILY: 'Calibri'; COLOR: #000000; FONT-SIZE: small; FONT-WEIGHT: normal; TEXT-DECORATION: none">
<DIV style="FONT: 10pt tahoma">
<DIV> </DIV>
<DIV><FONT size=3 face=Calibri>I’ll try running it with extra debug shortly
to see if I can see what’s happening</FONT></DIV>
<DIV> </DIV>
<DIV><FONT size=3 face=Calibri>Paul</FONT></DIV>
<DIV> </DIV>
<DIV style="BACKGROUND: #f5f5f5">
<DIV style="font-color: black"><B>From:</B> <A title=miconda@gmail.com
href="mailto:miconda@gmail.com" moz-do-not-send="true">Daniel-Constantin
Mierla</A> </DIV>
<DIV><B>Sent:</B> Tuesday, December 13, 2011 11:10 AM</DIV>
<DIV><B>To:</B> <A title=sr-dev@lists.sip-router.org
href="mailto:sr-dev@lists.sip-router.org" moz-do-not-send="true">Development
mailing list of the sip-router project</A> </DIV>
<DIV><B>Cc:</B> <A title=paul@crocodile-rcs.com
href="mailto:paul@crocodile-rcs.com" moz-do-not-send="true">Paul
Pankhurst</A> </DIV>
<DIV><B>Subject:</B> Re: [sr-dev] db_fetch_next problem with
Postgres?</DIV></DIV></DIV>
<DIV> </DIV></DIV>
<DIV
style="FONT-STYLE: normal; DISPLAY: inline; FONT-FAMILY: 'Calibri'; COLOR: #000000; FONT-SIZE: small; FONT-WEIGHT: normal; TEXT-DECORATION: none">Hello,<BR><BR>On
12/13/11 11:40 AM, Paul Pankhurst wrote:
<BLOCKQUOTE cite=mid:D3F2C658884F44B6882BA8DB016504E8@pjplaptop
type="cite"><DIV dir=ltr>
<DIV style="FONT-FAMILY: 'Calibri'; COLOR: rgb(0,0,0); FONT-SIZE: 12pt">
<DIV>I am having problems with the db_fetch_next routine and
Postgres.</DIV>
<DIV> </DIV>
<DIV>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.</DIV>
<DIV>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.</DIV>
<DIV>On the 2nd invocation it fails to retrieve the next 5 rows because
CON_RESULT(_con) is NULL !</DIV>
<DIV>Anyone got any suggestions? </DIV></DIV></DIV></BLOCKQUOTE>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:<BR><BR>LM_DBG("PQclear(%p) result set\n",
CON_RESULT(_con))<BR><BR>Cheers,<BR>Daniel<BR><PRE class=moz-signature cols="72">--
Daniel-Constantin Mierla -- <A class=moz-txt-link-freetext href="http://www.asipto.com" moz-do-not-send="true">http://www.asipto.com</A>
<A class=moz-txt-link-freetext href="http://linkedin.com/in/miconda" moz-do-not-send="true">http://linkedin.com/in/miconda</A> -- <A class=moz-txt-link-freetext href="http://twitter.com/miconda" moz-do-not-send="true">http://twitter.com/miconda</A></PRE></DIV></DIV></DIV></DIV></DIV></DIV><BR>
<FIELDSET class=mimeAttachmentHeader></FIELDSET> <BR><PRE wrap="">_______________________________________________
sr-dev mailing list
<A class=moz-txt-link-abbreviated href="mailto:sr-dev@lists.sip-router.org" moz-do-not-send="true">sr-dev@lists.sip-router.org</A>
<A class=moz-txt-link-freetext href="http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev" moz-do-not-send="true">http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev</A>
</PRE></BLOCKQUOTE><BR><PRE class=moz-signature cols="72">--
Daniel-Constantin Mierla -- <A class=moz-txt-link-freetext href="http://www.asipto.com" moz-do-not-send="true">http://www.asipto.com</A>
<A class=moz-txt-link-freetext href="http://linkedin.com/in/miconda" moz-do-not-send="true">http://linkedin.com/in/miconda</A> -- <A class=moz-txt-link-freetext href="http://twitter.com/miconda" moz-do-not-send="true">http://twitter.com/miconda</A></PRE></DIV></DIV></DIV><BR>
<FIELDSET class=mimeAttachmentHeader></FIELDSET> <BR><PRE wrap="">_______________________________________________
sr-dev mailing list
<A class=moz-txt-link-abbreviated href="mailto:sr-dev@lists.sip-router.org">sr-dev@lists.sip-router.org</A>
<A class=moz-txt-link-freetext href="http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev">http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev</A>
</PRE></BLOCKQUOTE><BR><PRE class=moz-signature cols="72">--
Daniel-Constantin Mierla -- <A class=moz-txt-link-freetext href="http://www.asipto.com">http://www.asipto.com</A>
<A class=moz-txt-link-freetext href="http://linkedin.com/in/miconda">http://linkedin.com/in/miconda</A> -- <A class=moz-txt-link-freetext href="http://twitter.com/miconda">http://twitter.com/miconda</A></PRE></DIV></DIV></DIV></BODY></HTML>