Re: idle connection timeout ...

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

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