Thread

1. Admin nice-to-have's

   Scott Shattuck <ss@technicalpursuit.com> — 2002-08-15T19:53:56Z

   A couple of admin nice-to-have's based on the last few weeks of 24x7
   operation are:
   
   Allow DBA/Database Owner to log in even when max_connections has been
   reached so they can determine which queries are hung via
   pg_stat_activity etc. and perform any other needed work to restore
   stability.
   
   Log offending query during DEBUG logging for syntax errors, missing
   tables/attributes, etc. so that the offending queries can be located in
   the codebase.
   
   
   
   ss
   
   
   
   
   
   

2. Re: Admin nice-to-have's

   Neil Conway <nconway@klamath.dyndns.org> — 2002-08-16T04:50:39Z

   Scott Shattuck <ss@technicalpursuit.com> writes:
   > Allow DBA/Database Owner to log in even when max_connections has
   > been reached so they can determine which queries are hung via
   > pg_stat_activity etc. and perform any other needed work to restore
   > stability.
   
   Allowing the database owner to login seems definately wrong: it's not
   unusual for many of the normal database clients to run as the owner of
   the database they operate on. So this would effectively disable the
   max_connections limit in this situation.
   
   I don't see a major problem with allowing postgres to login if the
   connection limit is hit (although I'm not sure it's worth the worry,
   when 'kill a backend executing SELECT ; psql template1 postgres' works
   as-is).
   
   Cheers,
   
   Neil
   
   -- 
   Neil Conway <neilconway@rogers.com>
   PGP Key ID: DB3C29FC
   
   
   

3. Re: Admin nice-to-have's

   Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-16T04:55:08Z

   Neil Conway wrote:
   > Scott Shattuck <ss@technicalpursuit.com> writes:
   > > Allow DBA/Database Owner to log in even when max_connections has
   > > been reached so they can determine which queries are hung via
   > > pg_stat_activity etc. and perform any other needed work to restore
   > > stability.
   > 
   > Allowing the database owner to login seems definately wrong: it's not
   > unusual for many of the normal database clients to run as the owner of
   > the database they operate on. So this would effectively disable the
   > max_connections limit in this situation.
   > 
   > I don't see a major problem with allowing postgres to login if the
   > connection limit is hit (although I'm not sure it's worth the worry,
   > when 'kill a backend executing SELECT ; psql template1 postgres' works
   > as-is).
   
   You would have to do it the unix kernel way when the PROC structure
   fills;  it keeps the last slot open and only lets root use it.  If this
   is desirable, I can add it to TODO.
   
   -- 
     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
   
   

4. Re: Admin nice-to-have's

   Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-16T13:35:26Z

   Neil Conway <nconway@klamath.dyndns.org> writes:
   > I don't see a major problem with allowing postgres to login if the
   > connection limit is hit (although I'm not sure it's worth the worry,
   > when 'kill a backend executing SELECT ; psql template1 postgres' works
   > as-is).
   
   max_connections is a hard limit; you do not have the option of letting
   people in anyway, because there'll be no PROC slot for them.
   
   We could consider establishing a "soft" connection limit that's somewhat
   less than max_connections, and allowing non-superusers to log in only
   if the soft limit hasn't been exceeded.  This does not guarantee that
   superusers can always get in: the extra slots might have been filled by
   other superuser connections.  But it'd give them better odds than the
   rabble.
   
   I tend to concur with Neil that the usefulness of such a feature is
   dubious.  But OTOH such a practice has always existed for Unix disk
   space --- maybe we should respect that precedent.
   
   			regards, tom lane
   
   

5. Re: Admin nice-to-have's

   Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-16T15:12:40Z

   Tom Lane wrote:
   > Neil Conway <nconway@klamath.dyndns.org> writes:
   > > I don't see a major problem with allowing postgres to login if the
   > > connection limit is hit (although I'm not sure it's worth the worry,
   > > when 'kill a backend executing SELECT ; psql template1 postgres' works
   > > as-is).
   > 
   > max_connections is a hard limit; you do not have the option of letting
   > people in anyway, because there'll be no PROC slot for them.
   > 
   > We could consider establishing a "soft" connection limit that's somewhat
   > less than max_connections, and allowing non-superusers to log in only
   > if the soft limit hasn't been exceeded.  This does not guarantee that
   > superusers can always get in: the extra slots might have been filled by
   > other superuser connections.  But it'd give them better odds than the
   > rabble.
   > 
   > I tend to concur with Neil that the usefulness of such a feature is
   > dubious.  But OTOH such a practice has always existed for Unix disk
   > space --- maybe we should respect that precedent.
   
   Yea, added to TODO:
   
   	* Reserve last process slot for super-user if max_connections reached
   
   -- 
     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
   
   

6. Re: Admin nice-to-have's

   Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-16T16:27:12Z

   Bruce Momjian <pgman@candle.pha.pa.us> writes:
   > Tom Lane wrote:
   >> We could consider establishing a "soft" connection limit that's somewhat
   >> less than max_connections, and allowing non-superusers to log in only
   >> if the soft limit hasn't been exceeded.  This does not guarantee that
   >> superusers can always get in: the extra slots might have been filled by
   >> other superuser connections.  But it'd give them better odds than the
   >> rabble.
   
   > Yea, added to TODO:
   > 	* Reserve last process slot for super-user if max_connections reached
   
   I don't like phrasing it that way: if we are going to do this at all
   then the number of reserved slots should be a configurable parameter.
   If I were a DBA I'd want it to be at least two: figure one for a cron
   job (doing backups, periodic vacuums, etc) and one for emergency
   interactive superuser access.  It definitely seems like something that
   installations would have differing views about.
   
   			regards, tom lane
   
   

7. Re: Admin nice-to-have's

   Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-16T17:04:35Z

   Tom Lane wrote:
   > Bruce Momjian <pgman@candle.pha.pa.us> writes:
   > > Tom Lane wrote:
   > >> We could consider establishing a "soft" connection limit that's somewhat
   > >> less than max_connections, and allowing non-superusers to log in only
   > >> if the soft limit hasn't been exceeded.  This does not guarantee that
   > >> superusers can always get in: the extra slots might have been filled by
   > >> other superuser connections.  But it'd give them better odds than the
   > >> rabble.
   > 
   > > Yea, added to TODO:
   > > 	* Reserve last process slot for super-user if max_connections reached
   > 
   > I don't like phrasing it that way: if we are going to do this at all
   > then the number of reserved slots should be a configurable parameter.
   > If I were a DBA I'd want it to be at least two: figure one for a cron
   > job (doing backups, periodic vacuums, etc) and one for emergency
   > interactive superuser access.  It definitely seems like something that
   > installations would have differing views about.
   
   Added "few":
   
   	* Reserve last few process slots for super-user if
   	  max_connections reached
   
   -- 
     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
   
   

8. Re: Admin nice-to-have's

   Bruce Momjian <pgman@candle.pha.pa.us> — 2002-09-02T01:15:09Z

   Scott Shattuck wrote:
   > A couple of admin nice-to-have's based on the last few weeks of 24x7
   > operation are:
   > 
   > Allow DBA/Database Owner to log in even when max_connections has been
   > reached so they can determine which queries are hung via
   > pg_stat_activity etc. and perform any other needed work to restore
   > stability.
   
   Done for 7.3.
   
   > Log offending query during DEBUG logging for syntax errors, missing
   > tables/attributes, etc. so that the offending queries can be located in
   > the codebase.
   
   Probably in 7.3.
   
   -- 
     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