Re: Admin nice-to-have's

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

Scott Shattuck <ss(at)technicalpursuit(dot)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(at)rogers(dot)com>
PGP Key ID: DB3C29FC

Neil Conway wrote:
> Scott Shattuck <ss(at)technicalpursuit(dot)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(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

Neil Conway <nconway(at)klamath(dot)dyndns(dot)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

Tom Lane wrote:
> Neil Conway <nconway(at)klamath(dot)dyndns(dot)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(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

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(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

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