[Fwd: Abrupt close of pgsql backend]

Deepa K wrote:

Hi,
I am running postgresql 7.1.3 in RedHat Linux 7.2. From an external C
application, three connections are established with postmaster (it is
started with -i option) through unix sockets. Two times I received EPIPE
error when trying to send a query to postmaster. This is because of the
abrupt closure of pgsql backend.

Your logs should say something about why a backend crashed, and if you
alter the settings will record each statement too.

--
Richard Huxton
Archonet Ltd

On Mon, 2004-10-25 at 23:00, Deepa K wrote:

Hi,
I am running postgresql 7.1.3 in RedHat Linux 7.2. From an external C
application, three connections are established with postmaster (it is
started with -i option) through unix sockets. Two times I received EPIPE
error when trying to send a query to postmaster. This is because of the
abrupt closure of pgsql backend.

The query sent is formed dynamically and hence I'm unable to get
the query whose processing may have led to such abrupt closure.

Were you running a later version, you'd have the option of logging your
queries. I don't think 7.1 supported that though.

But this
application is working for 2 years till now without any such
problems. So I don't really suspect the query.

You may be having a bigger overall problem that is hard to see,
especially since 7.1 suffered from several conditions, like index
bloating, that the fix is reindexing the database.

The postgresql todo lists that usage of views can lead to
such abrupt closure. But there are no views used in my application.

You'll be glad to know views are MUCH more robust and performant since
7.1 was introduced.

The last reason, for abnormal closure that I understand is if
the hard disk is full. But this is also not the case under study.

Anything that could cause a major error or a backend crash could cause
your abnormal closures.

Coming to the question,
a. What are the (other) reasons for postmaster closing
sockets abnormally?

b. How can the application detect such a abnormal closure of
socket?

You're asking a question about why an outdated version of PostgreSQL
that has known (and possible unknown) issues. While I'm sure folks will
be more than willing to try and help you, it's quite likely that many
problems you'll find in 7.1.x will have been fixed in the intervening
years, and the very real solution might wind up being an upgrade. If
you've already been thinking of upgrading, it might be worth trying it
now on a test server to see how it goes. If an upgrade isn't too hard,
it might be the easiest solution there is. And if it DOESN'T fix the
problem, people in the general (and then the hackers group if general
can't fix it,) will be much more interested in it.

f-f@club-internet.fr wrote:

Hello !

To kill a session i used KILL -INT <PID>.

This command wasn't successfull. The processus is still here when a
'ps ax' or a 'select * from pg_stat_activity'

Is there an other way to kill this process only because a web server
24/7 use postgres and cannot stop postgresql now.

From the manuals:
To terminate the postmaster normally, the signals SIGTERM, SIGINT, or
SIGQUIT can be used. The first will wait for all clients to terminate
before quitting, the second will forcefully disconnect all clients, and
the third will quit immediately without proper shutdown, resulting in a
recovery run during restart.

The utility command pg_ctl can be used to start and shut down the
postmaster safely and comfortably.

Does pg_ctl do anything for you?

--
Richard Huxton
Archonet Ltd

On Tue, Oct 26, 2004 at 08:27:31PM -0600, Scott Marlowe wrote:

Were you running a later version, you'd have the option of logging your
queries. I don't think 7.1 supported that though.

It certainly did. I can't remember the invocation. You'd better
have a log rotator, though, if you log all your queries, because your
logs get real big real fast.

That isn't to say that moving off 7.1 isn't a really good idea.

--
Andrew Sullivan | ajs@crankycanuck.ca
The plural of anecdote is not data.
--Roger Brinner

Thanks for your answer!

I would like to kill only a postgres session not postmaster.
The table pg_session indicates the pid of the wrong session but kill -INT <pid> didn't stop this session.

pg_ctl works fine when i want to stop postmaster with the argument '-m immediate stop'

----Message d'origine----