Re: [HACKERS] idle connection timeout ...

[Mike Mascari]

Bruce Momjian wrote:

Andrew Sullivan wrote:

On Fri, Oct 25, 2002 at 11:02:48AM -0400, Tom Lane wrote:

So?  If it hits the installation-wide limit, you'll have the same
problem; and at that point the (presumably runaway) app would have
sucked up all the connections, denying service to other apps using other
databases.  I think Marc's point here is to limit his exposure to
misbehavior of any one client app, in a database server that is serving
multiple clients using multiple databases.

That would indeed be a useful item.  The only way to avoid such
exposure right now is to run another back end.

Added to TODO:

	* Allow limits on per-db/user connections

Could I suggest that such a feature falls under the category of resource limits, and that the TODO should read something like:

Implement the equivalent of Oracle PROFILEs.

I think this would be a good project for 7.4. I'm not yet volunteering, but if I can wrap up my current project, I might be able to do it, depending upon the 7.4 target date. It would be:

1. A new system table:

pg_profile

2. The attributes of the profiles would be:

profname
session_per_user
cpu_per_session
cpu_per_call
connect_time
idle_time
logical_reads_per_session
logical_reads_per_call

3. A new field would be added to pg_user/pg_shadow:

profileid

4. A 'default' profile would be created when a new database is created with no resource limits. CREATE/ALTER user would be modified to allow for the specification of the profile. If no profile is provided, 'default' is assumed.

5. A new CREATE PROFILE/ALTER PROFILE/DROP PROFILE command set would be implemented to add/update/remove the tuples in pg_profiles. And according modification of pg_dump for dump/reload and psql for appropriate \ command.

Example:

CREATE PROFILE clerk
IDLE_TIME 30;

ALTER USER john PROFILE clerk;
ALTER USER bob PROFILE clerk;

or, for an ISP maybe:

ALYTER PROFILE default
IDLE_TIME 30;

It seems like a nice project, particularly since it wouldn't affect anyone that doesn't want to use it. And whenever a new resource limitation issue arrises, such as PL/SQL recursion depth, a new attribute would be added to pg_profile to handle the limitation...

Mike Mascari
[EMAIL PROTECTED]







---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]