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

I need others wanting this before I can add something of this
sophistication to TODO.

---------------------------------------------------------------------------

Mike Mascari wrote:
> 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
> 
> 
> 
> 
> 
> 
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073