Resend: Question about PostgreSQL, pgpool, and Postfix - Mailing list pgsql-general

[I don't know if this message made it out before or not. If it did, please accept my apologies for the duplicate message. Thanks.]

I'm running postfix 2.0.18 with a postgresql 8.0.3 database backend. I'm also using courier imap/pop servers connected to postgresql as well. All email users are stored in tables, with views providing lookup information to courier and postfix. It works very well, with one exception.

Postfix likes to hang on to idle connections to the database, even if there are not that many postfix processes running. For example, with postfix:

18338 ?        Ss     0:00 /usr/lib/postfix/master
18339 ?        S      0:00  \_ pickup -l -t fifo -u
18340 ?        S      0:00  \_ qmgr -l -t fifo -u
18344 ?        S      0:00  \_ trivial-rewrite -n rewrite -t unix -u
18358 ?        S      0:00  \_ smtpd -n smtp -t inet -u -s 2
18360 ?        S      0:00  \_ smtpd -n smtp -t inet -u -s 2
18361 ?        S      0:00  \_ smtpd -n smtp -t inet -u -s 2
18362 ?        S      0:00  \_ smtpd -n smtp -t inet -u -s 2
18363 ?        S      0:00  \_ cleanup -z -t unix -u
18370 ?        S      0:00  \_ smtpd -n smtp -t inet -u -s 2
18371 ?        S      0:00  \_ smtpd -n smtp -t inet -u -s 2
18372 ?        S      0:00  \_ smtpd -n smtp -t inet -u -s 2
18373 ?        S      0:00  \_ smtpd -n smtp -t inet -u -s 2
18386 ?        S      0:00  \_ cleanup -z -t unix -u
18390 ?        S      0:00  \_ cleanup -z -t unix -u
18397 ?        S      0:00  \_ cleanup -z -t unix -u
18401 ?        S      0:00  \_ cleanup -z -t unix -u
18402 ?        S      0:00  \_ cleanup -z -t unix -u
18403 ?        S      0:00  \_ cleanup -z -t unix -u
18427 ?        S      0:00  \_ cleanup -z -t unix -u
18440 ?        S      0:00  \_ smtpd -n smtp -t inet -u -s 2
18441 ?        S      0:00  \_ smtpd -n smtp -t inet -u -s 2
18557 ?        S      0:00  \_ cleanup -z -t unix -u
18558 ?        S      0:00  \_ virtual -t unix

This is what postgresql looks like:
17610 pts/1    S      0:00 /usr/bin/postmaster -D /var/lib/pgsql/data
17612 pts/1    S      0:00  \_ postgres: writer process
17613 pts/1    S      0:00  \_ postgres: stats buffer process
17614 pts/1    S      0:00  |   \_ postgres: stats collector process
17916 pts/1    S      0:00  \_ postgres: courier netio 127.0.0.1(3037) idle
18345 pts/1    S      0:00  \_ postgres: transport netio 127.0.0.1(27587) idle
18346 pts/1    S      0:00  \_ postgres: virtual netio 127.0.0.1(27588) idle
18347 pts/1    S      0:00  \_ postgres: domains netio 127.0.0.1(27589) idle
18364 pts/1    S      0:00  \_ postgres: transport netio 127.0.0.1(27590) idle
18365 pts/1    S      0:00  \_ postgres: virtual netio 127.0.0.1(27591) idle
18366 pts/1    S      0:00  \_ postgres: virtual netio 127.0.0.1(27592) idle
18367 pts/1    S      0:00  \_ postgres: transport netio 127.0.0.1(27593) idle
18377 pts/1    S      0:00  \_ postgres: transport netio 127.0.0.1(27596) idle
18378 pts/1    S      0:00  \_ postgres: virtual netio 127.0.0.1(27597) idle
18379 pts/1    S      0:00  \_ postgres: mailbox netio 127.0.0.1(27598) idle
18387 pts/1    S      0:00  \_ postgres: transport netio 127.0.0.1(27601) idle
18388 pts/1    S      0:00  \_ postgres: virtual netio 127.0.0.1(27602) idle
18389 pts/1    S      0:00  \_ postgres: mailbox netio 127.0.0.1(27603) idle
18394 pts/1    S      0:00  \_ postgres: transport netio 127.0.0.1(27604) idle
18395 pts/1    S      0:00  \_ postgres: virtual netio 127.0.0.1(27605) idle
18396 pts/1    S      0:00  \_ postgres: mailbox netio 127.0.0.1(27606) idle
18398 pts/1    S      0:00  \_ postgres: transport netio 127.0.0.1(27607) idle
18399 pts/1    S      0:00  \_ postgres: virtual netio 127.0.0.1(27608) idle
18400 pts/1    S      0:00  \_ postgres: mailbox netio 127.0.0.1(27609) idle
18404 pts/1    S      0:00  \_ postgres: mailbox netio 127.0.0.1(27610) idle
18408 pts/1    S      0:00  \_ postgres: transport netio 127.0.0.1(27611) idle
18409 pts/1    S      0:00  \_ postgres: virtual netio 127.0.0.1(27612) idle
18410 pts/1    S      0:00  \_ postgres: mailbox netio 127.0.0.1(27613) idle
18411 pts/1    S      0:00  \_ postgres: transport netio 127.0.0.1(27614) idle
18412 pts/1    S      0:00  \_ postgres: virtual netio 127.0.0.1(27615) idle
18413 pts/1    S      0:00  \_ postgres: mailbox netio 127.0.0.1(27616) idle
18420 pts/1    S      0:00  \_ postgres: transport netio 127.0.0.1(27618) idle
18422 pts/1    S      0:00  \_ postgres: virtual netio 127.0.0.1(27619) idle
18423 pts/1    S      0:00  \_ postgres: mailbox netio 127.0.0.1(27620) idle
18428 pts/1    S      0:00  \_ postgres: virtual netio 127.0.0.1(27622) idle
18429 pts/1    S      0:00  \_ postgres: transport netio 127.0.0.1(27623) idle
18436 pts/1    S      0:00  \_ postgres: virtual netio 127.0.0.1(27624) idle
18437 pts/1    S      0:00  \_ postgres: transport netio 127.0.0.1(27625) idle
18438 pts/1    S      0:00  \_ postgres: virtual netio 127.0.0.1(27626) idle
18439 pts/1    S      0:00  \_ postgres: transport netio 127.0.0.1(27627) idle
18442 pts/1    S      0:00  \_ postgres: transport netio 127.0.0.1(27628) idle
18443 pts/1    S      0:00  \_ postgres: virtual netio 127.0.0.1(27629) idle
18444 pts/1    S      0:00  \_ postgres: transport netio 127.0.0.1(27630) idle
18445 pts/1    S      0:00  \_ postgres: virtual netio 127.0.0.1(27631) idle
18446 pts/1    S      0:00  \_ postgres: mailbox netio 127.0.0.1(27632) idle
18447 pts/1    S      0:00  \_ postgres: virtual netio 127.0.0.1(27633) idle
18448 pts/1    S      0:00  \_ postgres: transport netio 127.0.0.1(27634) idle
18452 pts/1    S      0:00  \_ postgres: mailbox netio 127.0.0.1(27636) idle
18555 pts/1    S      0:00  \_ postgres: virtual netio 127.0.0.1(2386) idle
18556 pts/1    S      0:00  \_ postgres: transport netio 127.0.0.1(2387) idle
18559 pts/1    S      0:00  \_ postgres: mailbox netio 127.0.0.1(2388) idle
18609 pts/1    S      0:00  \_ postgres: virtual netio 127.0.0.1(2393) idle
18610 pts/1    S      0:00  \_ postgres: transport netio 127.0.0.1(2394) idle
18654 pts/1    S      0:00  \_ postgres: virtual netio 127.0.0.1(2398) idle
18655 pts/1    S      0:00  \_ postgres: transport netio 127.0.0.1(2399) idle
18940 pts/1    S      0:00  \_ postgres: virtual netio 127.0.0.1(20578) idle
18941 pts/1    S      0:00  \_ postgres: transport netio 127.0.0.1(20579) idle


I've been wondering about pgpool.

Does pgpool have enough intelligence to reuse idle connections? My understanding is that it is able to reuse existing connections with a database after the client disconnects from pgpool.

I'm in the process of updating my server, and I'm hoping to migrate to newer versions of postfix and postgresql. I'm wondering if pgpool will gain me anything. I don't think it will - since it appears postfix is hanging on and keeping each connection open, and not disconnecting. Is anyone else using postfix with postgresql in this manner? If so, are you having this problem?

Thanks,
Greg