Thread: Troublesome handling of dropped connection

Troublesome handling of dropped connection

From
Francisco Reyes
Date:
I had connected to a Postgresql database, running 7.1.3 on FreeBSD,
machine through ssh/psql. My machine crashed.

Upon restart of my machine I recconected to the database machine, again
ssh to the machine and then run psql.

The table I was loading data to at the time of the disconnection was
unresponsive. I tried to "truncate" it, tried to "vacuum <table> and psql
would just sit there for a long time. On the server log there were several
"MoveOfflineLogs" entries, but nothing else.

I tried to do a "smart" shutdown (pg_ctl stop) and that didn't seem to
work. I ended up doing "pg_ctl stop -m fast".

After the fast stop, the server came back up fine. Did a vacuum <table>
and it was instantaneous.

Any ideas what happened to the database prior to the shutdown?
I didn't have anyone else connected so it was not a problem shutting down
the server, however is there anything else I could have tried? In the
coming weeks this machine will go into production and then I will have
more users and a shutdown would not be convenient.


Re: Troublesome handling of dropped connection

From
Tom Lane
Date:
Francisco Reyes <lists@natserv.com> writes:
> I had connected to a Postgresql database, running 7.1.3 on FreeBSD,
> machine through ssh/psql. My machine crashed.

> Upon restart of my machine I recconected to the database machine, again
> ssh to the machine and then run psql.

Did you look to see whether your old session had disconnected or not?

> The table I was loading data to at the time of the disconnection was
> unresponsive.

It sounds like your new session was waiting around for the old session
to complete a transaction and release locks.

Unfortunately, if your old session was not in the midst of
sending/receiving data at the time of the crash, it may take quite a
while (an hour or two) before the TCP code notices that the connection's
been lost.  The backend will just sit there patiently until the
connection times out.  There is not a lot we can do about this; the
long timeout is mandated by the TCP specs, and the code involved is
kernel code not Postgres code anyway.

You could have zapped the backend more cleanly by sending it a SIGINT.

            regards, tom lane

Re: Troublesome handling of dropped connection

From
Francisco Reyes
Date:
On Mon, 26 Nov 2001, Tom Lane wrote:

> Francisco Reyes <lists@natserv.com> writes:
> > I had connected to a Postgresql database, running 7.1.3 on FreeBSD,
> > machine through ssh/psql. My machine crashed.
>
> > Upon restart of my machine I recconected to the database machine, again
> > ssh to the machine and then run psql.
>
> Did you look to see whether your old session had disconnected or not?

How would I see this from within psql?

> > The table I was loading data to at the time of the disconnection was
> > unresponsive.
>
> It sounds like your new session was waiting around for the old session
> to complete a transaction and release locks.


Is there anything I could have looked at to see this?
Right now this machine is in testing stages, but I waiting authorization
to start production. Once I go on production then I will have more
people/connections. More importantly, right now I am the only one that
goes in so I know exactly who is connected. When I go on production I
won't know who/when other users connect.

I can see how many postgresql sessions are running from top, but how would
I see what users are connected from within psql?

> You could have zapped the backend more cleanly by sending it a SIGINT.
>             regards, tom lane

If there is a way to see who is connected on psql, is there a way to kill
a connection other than SIGINT?


Re: Troublesome handling of dropped connection

From
Tom Lane
Date:
Francisco Reyes <lists@natserv.com> writes:
>> Did you look to see whether your old session had disconnected or not?

> How would I see this from within psql?

Right now I think "ps" on the server machine is the only viable way.
In 7.2 the pg_stat_activity table would give you a clue, at least.
You might care to read
http://candle.pha.pa.us/main/writings/pgsql/sgml/monitoring.html
which is from 7.2 docs, but the info about using ps applies to 7.1.

            regards, tom lane

Re: Troublesome handling of dropped connection

From
Francisco Reyes
Date:
On Tue, 27 Nov 2001, Tom Lane wrote:

> Francisco Reyes <lists@natserv.com> writes:
> >> Did you look to see whether your old session had disconnected or not?
>
> > How would I see this from within psql?
>
> Right now I think "ps" on the server machine is the only viable way.

I find that "top" works pretty good.