Re: idle connection timeout ...

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(at)mascari(dot)com