When grilled further on (Sat, 24 Jan 2004 15:54:07 -0500),
Tom Lane <tgl@sss.pgh.pa.us> confessed:
> > A custom Perl 5.6.1, DBI 1.30, DBD::Pg 1.22 script on Solaris 5.8.
> > Using pgsql client version 7.3.3. I'm thinking it may be related to
> > my forking? My program has a db connection, which is then overwritten
> > in all child processes, first thing upon forking:
>
> Judging by that trace, there are multiple processes trying to use the
> same database connection. Better take a closer look at what you are
> doing.
>
> I suspect that "overwriting" the connection is not good enough, and that
> you have to somehow actively close the DBI object inherited from the
> parent. The fact that the excess operations all seem to be empty
> queries is suspicious. I wonder whether those are coming from logic
> internal to DBI or DBD::Pg, that might still have access to the
> connection even though your Perl code doesn't anymore.
I have more of a clue now. I'm calling "DBI->connect_cached" when I fork, even
though I only use that particular db connection in the parent. connect_cached
sends a ping to verify the connection is alive, which is probably what the empty
queries are. The logs confirm that the pid associated with that connection
[721] and [1050] don't actually execute any requests. The new server is much
faster than the old one, so it is probably processing the packets faster, thus
occasionally getting into this problem when the fork timing is just wrong and
the dual packets are received before the first can be processed.
I can remove that second connection (which was used to vacuum) now that I have
pg_autovacuum running on the new server. I suspect this will clear up the empty
queries and the message coming back because of the overlapping empty queries.
Presuming this is the problem, I never would of found this without your help.
Thanks for taking the time. I'll not forget to be more careful of using
"connect_cached" next time around.
>
> Hmm ... AFAIR, libpq doesn't provide any clean way to close the socket
> and discard the PGconn without actually sending a Terminate message to
> the backend, which is what you'd want to happen in the child process.
> This seems like a shortcoming in its API.
>
Cheers,
Rob
--
14:08:58 up 27 days, 3:55, 4 users, load average: 2.05, 2.05, 2.05
