Re: Broken pipe errors using guile-pg with guile 1.8

[Sebastian Tennant <sebyte@smolny.plus.com>]

Quoth Greg Troxel <gdt@ir.bbn.com>:
> Sebastian Tennant <sebyte@smolny.plus.com> writes:
>> Is anyone familiar with the internals of guile-pg (version 0.16, author
>> Ian Grant) which wraps PostgreSQL's libpq?
>
> By 0.16, I think you mean ttn's version of Ian Grant's abandoned 0.07 or
> so.

It's the version shipped by Debian (deb modified by Dale Smith so that
it apt-installs without pulling in guile-1.6 as a dependency).  I
inspected the source (postgres.scm) to find the version number:

 ;;; Author: Ian Grant <xxx@xxx.xxx>

 (define-public (pg-guile-pg-module-version) "0.16")

On closer inspection, it probably is the version you're referring to as
ttn is the author of all the supplementary postgres-*.scm files included
in the package.

I know ttn's guile-pg (available from gnuvola.org) is much more
up-to-date but unfortunately it's only compatible with his guile-1.4.x
branch.

>> I'm getting a lot of 'broken pipe' errors of the sort:
>>
>>  Feb 23 11:32:13 stderr: ERROR: In procedure pg-connectdb:
>>  Feb 23 11:32:13 stderr: ERROR: could not send startup packet: Broken pipe
>>
>> It's especially bad at times of light load (rather than no load) and I
>> never once noticed this error whilst the webapp was in development
>> (i.e., I was the only person using it) which suggests to me that it's
>> happening whenever two or more page requests call scripts that attempt
>> to open database connections more or less at the same time.
>>
>> All my calls to pg-connectdb are let bound so database connections are
>> (supposed to be) closed after every query.
>
> I am not sure that gc will run as soon as you drop the reference, 

So you agree with my diagnosis?  Are there some arbitrary limits to how
many database connections (iniated by pg-connectdb) can remain open at
once?  There's no mention of that in the manual.

> so you might want to force gc, or better yet explicitly close the db
> object.

From the manual accompanying my version:

   "`Guile-pg' connections are closed when Guile's garbage collector
 collects the discarded object.  This only happens when the connection
 itself and all of the results of `pg-exec' are unreachable.  So to
 close the connection we just rebind the results from that connection
 and the connection symbol itself:

      guile> (define result '())
      guile> (define test '())

    Typically though the result and connection variables will be `let'
 bindings so the connection will usually be closed soon after the body
 of the `let' returns."          ^^^^^^^           ^^^^

I don't like the sound of 'usually' and 'soon' now that I read this
again in the light of the problems I'm experiencing.  Here's one of
scores of database procedures I've written for this particular web-app:

 (define (username-in-use? username)
   (let* ((dbconn (pg-connectdb "dbname=xxxx"))
          (q (pg-exec dbconn (sa "SELECT username FROM joblist "
                                 "WHERE username='" username "';"))))
     (> (pg-ntuples q) 0)))

I can't see how rebinding 'dbconn' and 'q' within this let form is going
to make any difference (plus I need a boolean return value, not
#unspecified as returned by set!).  Is this what you mean by "explicitly
closing the db object"?

Forcing gc after each procedure call which initiates a database
connection is a possible course of action.

> You could add a debug printf in the free/close function to check this
> hypothesis, 

As there are no free/close functions (everything is let bound) could you
be more specific as to how I could debug the cause of these broken
pipes?

>> P.S. Perhaps upgrading guile-pg is the first thing I should try? IIRC
>>      there's a more recent pkgsrc version (which is guile 1.8
>>      comaptible) somewhere... hunting for it now.
>
> I suspect a day or so of work would suffice to make the most recent
> guile-pg work with 1.8.  If you do it definitely post patches.

For C programmers perhaps, but not lowly scripters like myself.

Out of interest which do you consider the most recent guile-pg (ttn's or
some other, available from where)?

Regards,

Sebastian
-- 
Emacs' AlsaPlayer - Music Without Jolts
Lightweight, full-featured and mindful of your idyllic happiness.
http://home.gna.org/eap