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

Tom's idea of doing this as part of the already added per-user/per-db
settings seems like a good direction to take.

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

Marc G. Fournier wrote:
> 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@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, Pennsylvania 19073
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html
> >
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

--  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