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

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thu, Aug 16, 2007 at 05:41:32PM -0500, Peter Koczan wrote:
> Hey, I found something that finally clears netstat's recv-q with async
> notifies. I don't quite understand what's going on, so any
> enlightenment would be greatly appreciated. As near as I can figure,
> the client doesn't appear to read the notifies until it sends a notify
> of its own. Then it reads all pending notifies at once and clears the
> recv-q.
>
> For instance, I can open up two connections on my test server and
> illustrate the problem.
>
> Here's the netstat before everything.
>
> [koczan@ator] ~ $ netstat | grep mitchell
> tcp        0      0 ator.cs.wisc.edu:34279
> mitchell.cs.wisc:postgresql ESTABLISHED
> tcp        0      0 ator.cs.wisc.edu:34280
> mitchell.cs.wisc:postgresql ESTABLISHED
>
> Connection 1:
> csdb=> listen req;
> LISTEN
>
> Connection 2:
> csdb=> notify req;
> NOTIFY
> (repeat 5 times)
>
> Netstat as of now (after 5 notifies):
> [koczan@ator] ~ $ netstat | grep mitchell
> tcp       70      0 ator.cs.wisc.edu:34279
> mitchell.cs.wisc:postgresql ESTABLISHED
> tcp        0      0 ator.cs.wisc.edu:34280
> mitchell.cs.wisc:postgresql ESTABLISHED
>
> Connection 1:
> csdb=> notify req;
> NOTIFY
> Asynchronous notification "req" received from server process with PID 7268.
> Asynchronous notification "req" received from server process with PID 7268.
[...]

Hm. To me it looks like the first psql session isn't prepared to gather
async notifies (checking...) indeed, psql only checks for async notifies when
issuing commands (you don't have to issue a NOTIFY in "Connection 1" to
receive the pending notifies -- *any* request directed to the server would do).

> Any ideas?

If you see the queue building up as above, the problem lies almost
certainly on the client not gathering the notifies. The backend is
obviously willing to talk to you :-)

I'd look for the problem in the client library (It's DBD::Pg in your
"real" case, isn't it?)

Regards
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFGxZCABcgs9XrR2kYRAkAsAJ9lvE86sUMQstU0S08/eWT64XqgpwCfZzjt
XVQrPm6b82m47QDFxcGyFTg=
=Omtb
-----END PGP SIGNATURE-----