Re: BUG #3504: Some listening sessions never return from writing, problems ensue

Either it's not reading the notifies or it's not telling the server that
it's read them and flushing out the pipe.

This app uses Perl/DBD::Pg, and the code is basically:

while ($dbh->func('pg_notifies')) {
  # do stuff
}

The corresponding C code in DBD::Pg appears to be:
/* ================================================================== */
SV * dbd_db_pg_notifies (dbh, imp_dbh)
                 SV *dbh;
                 imp_dbh_t *imp_dbh;
{
        PGnotify *notify;
        AV *ret;
        SV *retsv;
        int status;

        if (dbis->debug >= 3) { (void)PerlIO_printf(DBILOGFP, "dbdpg:
dbd_db_pg_notifies\n"); }

        status = PQconsumeInput(imp_dbh->conn);
        if (0 == status) {
                status = PQstatus(imp_dbh->conn);
                pg_error(dbh, status, PQerrorMessage(imp_dbh->conn));
                return &sv_undef;
        }

        notify = PQnotifies(imp_dbh->conn);

        if (!notify)
                return &sv_undef;

        ret=newAV();

        av_push(ret, newSVpv(notify->relname,0) );
        av_push(ret, newSViv(notify->be_pid) );

#if PGLIBVERSION >= 70400
        PQfreemem(notify);
#else
        Safefree(notify);
#endif

        retsv = newRV(sv_2mortal((SV*)ret));

        return retsv;

} /* end of dbd_db_pg_notifies */


On 8/2/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "Peter Koczan" <pjkoczan@gmail.com> writes:
> > A quick perusal of the other "notify interrupt" connections shows 13032
> in
> > the Send-Q column. They all got into this state at the same time.
>
> That's far too much data to be a notify message, or even a small number
> of notify messages.  Is it possible that the client's failed to collect
> hundreds of notify events?
>
> But in any case this clearly lets the backend off the hook.  The problem
> is on the client side: it's not reading the data.
>
>                         regards, tom lane
>