Обсуждение: idle in transaction...unexpected EOF on client connection

Hi Everybody,

I see in serverlog many entries that read:
 <gadb 2007-11-09 13:13:47 PST idle in transaction>LOG:  unexpected EOF on client connection
 <gadb 2007-11-09 13:23:08 PST idle in transaction>LOG:  unexpected EOF on client connection
 <gadb 2007-11-09 13:23:44 PST idle in transaction>LOG:  unexpected EOF on client connection
 <gadb 2007-11-09 13:25:42 PST idle in transaction>LOG:  unexpected EOF on client connection

This occurs regularly and frequently and I am
wondering if it is indicative of any underlying
issue(s).  Would you please let me know what
you know?

Thank you.

Tena Sakai
tsakai@gallo.ucsf.edu

On Nov 9, 2007 3:58 PM, Tena Sakai <tsakai@gallo.ucsf.edu> wrote:
>
> Hi Everybody,
>
>  I see in serverlog many entries that read:
>   <gadb 2007-11-09 13:13:47 PST idle in transaction>LOG:  unexpected EOF on
> client connection
>   <gadb 2007-11-09 13:23:08 PST idle in transaction>LOG:  unexpected EOF on
> client connection
>   <gadb 2007-11-09 13:23:44 PST idle in transaction>LOG:  unexpected EOF on
> client connection
>   <gadb 2007-11-09 13:25:42 PST idle in transaction>LOG:  unexpected EOF on
> client connection
>
>  This occurs regularly and frequently and I am
>  wondering if it is indicative of any underlying
>  issue(s).  Would you please let me know what
>  you know?

Most likely what is happening is that you have a firewall between your
app and your db server that is closing idle connections after x
minutes.  Could be other things, like crashing clients, etc...  Hard
to say without more evidence.

Campbell, Lance wrote:
> I notice this in my log file as well.  I do not have a fire wall that
> would impact this.

It could be a monitoring tool that's opening the socket to check whether
Postgres is listening.  If it connects then closes the socket without
actually talking the PG protocol, this is exactly what you would see.

Is there any reason you're so fond of top-posting?

--
Alvaro Herrera                 http://www.amazon.com/gp/registry/DXLWNGRJD34J
"Escucha y olvidarás; ve y recordarás; haz y entenderás" (Confucio)

On Nov 9, 2007 4:29 PM, Alvaro Herrera <alvherre@commandprompt.com> wrote:
> Campbell, Lance wrote:
> > I notice this in my log file as well.  I do not have a fire wall that
> > would impact this.
>
> It could be a monitoring tool that's opening the socket to check whether
> Postgres is listening.  If it connects then closes the socket without
> actually talking the PG protocol, this is exactly what you would see.

That might explain Lance's problem but not Tena's, since his (hers?)
are idle in transaction, which means the connection at least did a
begin; after connecting.

Also possible for Lance's is an application that isn't properly
closing its connections.

Hi Scott,

> Most likely what is happening is that you have a firewall between your
> app and your db server that is closing idle connections after x minutes.

Thanks for your comment.

I hate to be disagreeable, but the app and postgres are running
on the same computer.  The app is a java program using jdbc to
issue postgres commands.  However, I see the same message via
psql (on the same machine).

Tena Sakai
tsakai@gallo.ucsf.edu


-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: Fri 11/9/2007 2:11 PM
To: Tena Sakai
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] idle in transaction...unexpected EOF on client connection

On Nov 9, 2007 3:58 PM, Tena Sakai <tsakai@gallo.ucsf.edu> wrote:
>
> Hi Everybody,
>
>  I see in serverlog many entries that read:
>   <gadb 2007-11-09 13:13:47 PST idle in transaction>LOG:  unexpected EOF on
> client connection
>   <gadb 2007-11-09 13:23:08 PST idle in transaction>LOG:  unexpected EOF on
> client connection
>   <gadb 2007-11-09 13:23:44 PST idle in transaction>LOG:  unexpected EOF on
> client connection
>   <gadb 2007-11-09 13:25:42 PST idle in transaction>LOG:  unexpected EOF on
> client connection
>
>  This occurs regularly and frequently and I am
>  wondering if it is indicative of any underlying
>  issue(s).  Would you please let me know what
>  you know?

Most likely what is happening is that you have a firewall between your
app and your db server that is closing idle connections after x
minutes.  Could be other things, like crashing clients, etc...  Hard
to say without more evidence.

On Nov 9, 2007 4:36 PM, Tena Sakai <tsakai@gallo.ucsf.edu> wrote:

> Hi Scott,
>
>
>  > Most likely what is happening is that you have a firewall between your
>  > app and your db server that is closing idle connections after x minutes.
>
>  Thanks for your comment.
>
>  I hate to be disagreeable, but the app and postgres are running
>  on the same computer.  The app is a java program using jdbc to
>  issue postgres commands.  However, I see the same message via
>  psql (on the same machine).

Hmmm.  Well, when you see an error like unexpected EOF on client
connection then that means that the client "disappeared" without
closing the connection, or the network connection went away.  Either
way, the pgsql backend was still alive, and tried to send something to
the client, and the client wasn't there.  I.e. the problem isn't
generally a postgresql problem, it's a client problem.  So, the more
info about the client app the better chance of fixing it.

Note that if you've got a lot of "idle in transactions" connections,
that's a bad thing because it prevents postgresql from being properly
vacuumed.  Most connection managers can be set to NOT issue a begin as
part of a connection reset when returning it to the pool.

"Scott Marlowe" <scott.marlowe@gmail.com> writes:
> Hmmm.  Well, when you see an error like unexpected EOF on client
> connection then that means that the client "disappeared" without
> closing the connection, or the network connection went away.

More specifically, the client didn't send the expected "terminate session"
message before dropping the network connection.

I seem to recall that old versions of the JDBC driver had a bad habit
of not sending Terminate --- what JDBC version are you using?

            regards, tom lane

Hi Tom,

Good to have you jump in.

The answer to your question (what JDBC version) is:
postgresql-8.1-404.jdbc3.jar
and the postgres version is: 8.2.4

I just downloaded postgresql-8.2-506.jdbc3.jar and
will go ahead and recompile the app over the weekend
with this one.

Aside from that, can this be caused by many deleted
rows from tables (and having neglected to vacuum)?

Tena Sakai
(tsakai@gallo.ucsf.edu)


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Fri 11/9/2007 3:03 PM
To: Scott Marlowe
Cc: Tena Sakai; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] idle in transaction...unexpected EOF on client connection

"Scott Marlowe" <scott.marlowe@gmail.com> writes:
> Hmmm.  Well, when you see an error like unexpected EOF on client
> connection then that means that the client "disappeared" without
> closing the connection, or the network connection went away.

More specifically, the client didn't send the expected "terminate session"
message before dropping the network connection.

I seem to recall that old versions of the JDBC driver had a bad habit
of not sending Terminate --- what JDBC version are you using?

                        regards, tom lane

Tena Sakai escribió:

> Aside from that, can this be caused by many deleted
> rows from tables (and having neglected to vacuum)?

No.

--
Alvaro Herrera                        http://www.advogato.org/person/alvherre
Major Fambrough: You wish to see the frontier?
John Dunbar: Yes sir, before it's gone.

Thank you!

Tena


-----Original Message-----
From: Alvaro Herrera [mailto:alvherre@commandprompt.com]
Sent: Fri 11/9/2007 4:29 PM
To: Tena Sakai
Cc: Tom Lane; Scott Marlowe; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] idle in transaction...unexpected EOF on clientconnection

Tena Sakai escribió:

> Aside from that, can this be caused by many deleted
> rows from tables (and having neglected to vacuum)?

No.

--
Alvaro Herrera                        http://www.advogato.org/person/alvherre
Major Fambrough: You wish to see the frontier?
John Dunbar: Yes sir, before it's gone.