Re: idle connection timeout ...

On Fri, 25 Oct 2002, Bruce Momjian wrote:

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

Sounds cool to me, and would satisfy what I'm looking for, since it sounds
like the same user could have different limits depending on the database
they are/were connectin gto ...

>
> ---------------------------------------------------------------------------
>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>