Thread: db sever seems to be dropping connections

db sever seems to be dropping connections

From
"Rushabh Doshi"
Date:
I'm facing an issue with PostgreSQL .. The server seems to drop the
connection to my script after a couple of hours (sometimes, not always).

DBD::Pg::db selectrow_array failed: could not receive data from server:
....
....
:server closed the connection unexpectedly.

And in another scenario it failed with the message:
DBD::Pg::db selectrow_array failed: could not receive data from server:
Connection timed out

Is this a known issue? I'm running PG 7.3

--Rushabh Doshi


Re: db sever seems to be dropping connections

From
Chris
Date:
Rushabh Doshi wrote:
> I'm facing an issue with PostgreSQL .. The server seems to drop the
> connection to my script after a couple of hours (sometimes, not always).
>
> DBD::Pg::db selectrow_array failed: could not receive data from server:
> ....
> ....
> :server closed the connection unexpectedly.

Do you get a better error in the postgresql logs?

> And in another scenario it failed with the message:
> DBD::Pg::db selectrow_array failed: could not receive data from server:
> Connection timed out

Sounds more like a network issue. How is the script connecting to
postgres? using a socket? Through tcpip?

--
Postgresql & php tutorials
http://www.designmagick.com/

Re: db sever seems to be dropping connections

From
"Rushabh Doshi"
Date:
Rushabh Doshi wrote:
> I'm facing an issue with PostgreSQL .. The server seems to drop the
> connection to my script after a couple of hours (sometimes, not always).
>
> DBD::Pg::db selectrow_array failed: could not receive data from server:
> ....
> ....
> :server closed the connection unexpectedly.

Do you get a better error in the postgresql logs?

--- Unfortunately there's nothing in the postgresql logs.

> And in another scenario it failed with the message:
> DBD::Pg::db selectrow_array failed: could not receive data from server:
> Connection timed out

Sounds more like a network issue. How is the script connecting to
postgres? using a socket? Through tcpip?

--- Using tcpip. I've tried to re-establish connection in my script in case
of invalid handles or network issues. But this seems to be happening quite
often


Re: db sever seems to be dropping connections

From
Tom Lane
Date:
Chris <dmagick@gmail.com> writes:
> Rushabh Doshi wrote:
>> I'm facing an issue with PostgreSQL .. The server seems to drop the
>> connection to my script after a couple of hours (sometimes, not always).

> Sounds more like a network issue.

Yeah --- in particular, it sounds like a connection timeout imposed by
a router or firewall.  A lot of NAT-capable routers will drop idle TCP
connections after a certain period of inactivity (typically an hour or
so ... if you're lucky, the router will let you adjust the timeout).
There is no connection timeout built into Postgres itself, so you should
be looking for network-related limitations.

            regards, tom lane

Re: db sever seems to be dropping connections

From
"Rushabh Doshi"
Date:
Tom,

This is my problem. I have a connection to the database using DB->connect.
Apparently, this connection sits idle for around an hour or so because of my
application, and after that it has to do something. So I check that if the
handle is still a valid one or not. If it's not, then re-establish the
connection and use that handle. This works fine. However, when the time comes
to destroy all the handles, the first handle (stale) seems to be throwing
warning/error messages on exit.

DBI handle: DBI::db=HASH(0x907045c)  # this is the latest handle
result of disconnect: 1
DBI handle: DBI::db=HASH(0x9480bdc)  # this is the stale handle
DBD::Pg::db disconnect failed: rollback failed at *** line 241.
result of disconnect:
disconnect failed for DBI::db=HASH(0x9480bdc):  DBD::Pg::db disconnect
failed: rollback failed at *** line 241.

I've used evals in the DESTROY to avoid these messages. Still this keeps
happening. I've tried using connect_cached but to no effect.

Thanks,
--Rushabh Doshi

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, March 20, 2006 2:16 PM
To: Chris
Cc: Rushabh Doshi; pgsql-general@postgresql.org
Subject: Re: [GENERAL] db sever seems to be dropping connections

Chris <dmagick@gmail.com> writes:
> Rushabh Doshi wrote:
>> I'm facing an issue with PostgreSQL .. The server seems to drop the
>> connection to my script after a couple of hours (sometimes, not always).

> Sounds more like a network issue.

Yeah --- in particular, it sounds like a connection timeout imposed by a
router or firewall.  A lot of NAT-capable routers will drop idle TCP
connections after a certain period of inactivity (typically an hour or so ...
if you're lucky, the router will let you adjust the timeout).
There is no connection timeout built into Postgres itself, so you should be
looking for network-related limitations.

            regards, tom lane

Re: db sever seems to be dropping connections

From
Vivek Khera
Date:
On Mar 21, 2006, at 4:39 PM, Rushabh Doshi wrote:

> This is my problem. I have a connection to the database using DB-
> >connect.
> Apparently, this connection sits idle for around an hour or so
> because of my
> application, and after that it has to do something. So I check that
> if the
> handle is still a valid one or not. If it's not, then re-establish the

I see you're using DBI.

When you detect the dead connection, you need to close it immediately
so that later on it will not attempt a disconnect.  What you probably
also want is to tell DBI not to try to do any cleanup on it since the
connection is already lost.  You do this by setting

  $dbh->{InactiveDestroy} = 1

before you undef $dbh.


Re: db sever seems to be dropping connections

From
Tom Lane
Date:
"Rushabh Doshi" <rdoshi@vmware.com> writes:
> DBI handle: DBI::db=HASH(0x9480bdc)  # this is the stale handle
> DBD::Pg::db disconnect failed: rollback failed at *** line 241.
> result of disconnect:
> disconnect failed for DBI::db=HASH(0x9480bdc):  DBD::Pg::db disconnect
> failed: rollback failed at *** line 241.

This seems like fairly stupid programming within DBD::Pg --- there's no
reason for it to be issuing an explicit rollback command at disconnect,
as the backend will certainly do that itself on seeing the connection
drop.  Possibly you could get the DBD::Pg developers to change that,
so that a disconnect would be more robust in the face of network failure
(and a tad faster too).

            regards, tom lane