From: | Mike Mascari <mascarm(at)mascari(dot)com> |
---|---|
To: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
Cc: | Andrew Sullivan <andrew(at)libertyrms(dot)info>, PostgresSQL Hackers Mailing List <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: idle connection timeout ... |
Date: | 2002-10-25 19:31:22 |
Message-ID: | 3DB99C0A.70900@mascari.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)mascari(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2002-10-25 19:54:27 | Re: idle connection timeout ... |
Previous Message | Mike Benoit | 2002-10-25 19:16:50 | Re: idle connection timeout ... |