Re: idle connection timeout ... - Mailing list pgsql-hackers

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
mascarm@mascari.com