Re: Server instrumentation for 8.1 - Mailing list pgsql-hackers

> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org
> [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Andreas Pflug
> Sent: 11 May 2005 17:44
> To: pgsql-hackers@postgresql.org
> Subject: [HACKERS] Server instrumentation for 8.1
>
> There's still a lengthy discussion going on whether it's a
> good idea to
> add a forth way to read pgsql's schema (pg_* tables, pg_* views,
> information_schema, did I miss one?), but I'd like to see helper
> functions for issues *not* covered in the core package.

I was going to write pretty much the same message - thanks for saving me
the time!

> - dbsize has been in contrib for a long time, though it
> appears to me as
> quite a basic functionality to find out about storage needs.

Agreed.

> - The superuser only generic file functions in the admin package have
> been posted for 8.0, but where (more or less ) silently
> dropped. These
> functions allow pgadmin to display the server logs, as well
> as editing
> pg_hba.conf and postgresql.conf without console access to
> whatever-pgsql-is-running-on.  I'd like to see this at least
> as contrib
> module (the functions are probably safer than pl_sh).
>
> Both these modules are bundled with the pgsql win32
> installer, and are
> installed by default. Both are supported by (at least) pgAdmin.

I would like to see these as permanent additions to the server. They are
useful functions that allow functionality to be included in interfaces
like pgAdmin that any user coming from MS SQL or other DBMSs would
probably expect to find. For anyone wanting to take a look, the module
can be found in our shiny new Subversion repo at
http://svn.pgadmin.org/cgi-bin/viewcvs.cgi/trunk/pgadmin3/xtra/admin/

>
> - There was a pg_kill_backend function in pre-8.0, but it was dropped
> because "it's too dangerous". Incidentially, I've been in the
> situation
> more than once where I needed to kill a backend process that
> was running
> wild; alternatively, I'd have to shutdown the whole server. I
> had to do
> this on the linux console with kill -9 (fortunately I did
> have access),
> or using the win32 task manager (same). This appears even more error
> prone to me than to point to the malicious process and kill
> it (through
> pgadmin/pg_kill_backend)

This is also essential functionality, though only if it can be made safe
imo.

> - We don't have a profiling facility to tap an individual
> backend for a
> limited period to find out what the client is doing there, so
> we need to
> use log_statement for this (I'd like to work on profiling,
> but I didn't
> find the time so far). Consequently, we have to deal with
> long logfiles,
> containing much stuff we don't need. In the past, I found it
> to be very
> helpful if a fresh logfile could be used (on a private installation,
> stop/start server), that's why my logfile process implementation did
> include a logfile rotation trigger functionality. Tom didn't
> need it, so
> he dropped it. I'd opt for re-adding it again.

Yes, I ran into exactly this problem this morning as it happens when
tracking down an obscure bug in some code that couldn't easily be
debugged.

Now I know you're all thinking 'oh yeah, obviously the pgAdmin team are
putting on a united front', but honestly, I knew nothing about Andreas'
email until I saw it, and he knew nothing of my intention to write one!
:-)

Regards, Dave.