Re: [Bulk] Re: Disconnecting non active (IDLE ) users - Mailing list pgsql-admin

>----- Original Message -----
>From: "Ezequias Rodrigues da Rocha" <ezequias.rocha@gmail.com>
>To: "Goran Rakic" <gossa@disyu.com>
>Cc: <pgsql-admin@postgresql.org>
>Sent: Wednesday, February 28, 2007 11:01 AM
>Subject: [Bulk] Re: [ADMIN] Disconnecting non active (IDLE ) users


>I would like to know how postgresql defines that a connection is
>inactive. I have the same problem but in the future I couldn't restart
>my server every day.

>Any explanation ?

I would be quite interested in the answer to this, but it seems to me that
there is a bigger problem.

>2007/2/26, Goran Rakic <gossa@disyu.com>:
>> I have installed POSTGRESQL 8.2 on W2K3 32bit , 100 users connecting from
>> desktop applications and 200 users connecting thru web service from
>> handheld
>> computers
>>
>> I have problem with second groups of users.
>>
>> Often they do not disconnect from POSTGRE Server and with time passing
>> thru
>> I have lot of IDLE users and very much memory consumptions. From time to
>> time that can crash server then only restarting server will kill all
>> postgres.exe from memory and this annoying me, because that I build
>> script
>> to restart server every night. I could not get programmers to change
>> program.

Part of the solution here seems to be a management issue rather than a
technological one.

If the web application above is bought from a third party, I would expect
management to diligently pursue the provider for a reasonable solution.  It
is simply unacceptable that a company server should crash on a regular basis
because of slop in an application purchased from some other company.
Similarly, if the application was developed in house, then, obviously,
management ought to be aware of the server crash issue and ought to be
directing the relevant programmers to fix the problem.  It is outrageously
unethical, IMHO, for a programmer, or group thereof, to refuse to fix a
problem that has arisen from how their targetted users use their
application.  Of course, before becoming too hard on the programmers
mentioned above, the onus should be on Goran to provide substantive proof to
his management and the other programmers that their client code is
responsible (or, better, management ought to have created a small team
including both Goran and the programmers in question to study the problem in
order to determine precisely what the cause of the problem is and how it is
best addressed).  After all, it may just be coincidence that the server is
crashing when there are lots of users connecting using a web service, and
the real cause may be something entirely different.  Goran noted a
correlation between memory consumption and the number of idle connections.
He now needs to determine why, especially since an idle connection by itself
ought not consume a significant amount of resources.  What has happened
previously during the establishment and use of these connections?  Did, the
user create, and not release, a significant number of temporary tables or
even a single temporary table that was populated with a lot of data?  I have
known SQL programmers to do precisely this on the assumption the temporary
table goes away once the session is terminated, but if the session doesn't
get terminated the temporary table just hangs around.  IMHO, such a
programmer deserves a good reprimand, and my practice is to drop such
temporaries the moment they're no longer needed.  I have yet to find a
fellow programmer that objected to this practice, even when that person is
responsible for the kind of slop that depends on the server software, or
middleware code in some cases, to free the resources so carelessly used.
Over the years, I have encountered serious bugs even in commercial libraries
in which resources weren't properly freed, and I found ways to use ANSI
standard specifications to work around such bugs, to solve the problems they
created for me, until such a time as the vendor fixed the bugs in question.
Alas, too often fixing such bugs is given a low priority because only a
small proportion of the vendor's clients are hurt by them because they
develop applications that don't intensively use resources (mine,
particularly my analysis and modelling applications, do tend to use a lot of
memory so I am especially sensitive to memory leaks).

One final observation I would make is that something is seriously wrong with
either the design or the implementation of the client application if either
the number of idle connections grows continuously without limit or if it
gets very large.  I would expect a few idle connections, the actual number
of which being dependant on how you're doing connection pooling, but I would
insist on the client code being very careful about closing excess idle
connections.  I can see a wide variety of programming blunders or slop
whereby connections are leaked, and never closed until they cause something
to crash.  If there is evidence of such slop, even if it is not the
proximate cause of the server crashing, the programmer responsible deserves
a good reprimand.

On this thread, there have been considerations given of why a few hundred
idle connections should not consume enough resources to cause a server to
crash.  If so few of them could cause such a crash, that would raise doubts
about whether or not the server software is ready for production use.  In
any event, I haven't seen enough information to begin to figure out the real
cause of the problem.  But, there has also been information provided about
how one can disconnect idle connections from within PostgreSQL: the SQL
looked simple enough.  I might even use it, but only as a last resort since
in my experience, these kinds of problems are often due to programming slop,
and this slop ought to never be tolerated.  To rely on this, without dealing
with any programming slop, masks the real problem and strikes me as being as
foolish as a programmer restarting his program after each task because it
has a memory leak instead of fixing the memory leak (or any other favourite
resource leak you may have experienced, if your programming language of
choice doesn't support pointers or creation of objects on the heap instead
of the stack).

HTH

Ted