Re: [HACKERS] For review: Server instrumentation patch - Mailing list pgsql-patches

Andreas Pflug wrote:
> Bruce Momjian wrote:
>
> >
> >
> > I don't see how listing the log files relates to editing the confuration
> > files.
>
> Both are remote administration. While we've seen the discussion that one
> aspect (config file editing) should be performed in psql, you assume the
> other aspect (viewing the logfile) to be not interesting. Your
> argumentation doesn't seem consequent to me.

To me monitoring (logfiles) and configuration (postgresql.conf) are
different, but if I can make it easy to do both from psql, great.

I can see making postgresql.conf easy (SET GLOBAL), I am unsure about
making pg_hba.conf easy (and many feel that way), and I am not sure how
adding a log directory listing took makes things significantly easier to
monitor log files.

What I can imagine making things very easy is a readonly GUC that returns
the current log file name.  That I think should be in the backend, and I
can see a query that combines that with pg_read_file() that prints the
last 1000 bytes from the file.

    SELECT pg_read_file(t1.setting, -1000, 1000);
    FROM     (SELECT setting FROM pg_settings WHERE NAME = 'log_current_name') AS t1

> >>>it would be something done in C or another application language.  Aren't
> >>>the file names already ordered based on their file names, given the
> >>>default pattern, postgresql-%Y-%m-%d_%H%M%S.log?
> >>
> >>The issue is _filtering_, not ordering. Since the log directory might be
> >>directed to a different location, non-pgsql logfiles might be there too.
> >>You'd probably won't expect to retrieve these files over a pgsql connection.
> >
> >
> > Well, if they mix log files and non-log files in the same directory, we
> > would have to filter based on the log_filename directive in the
> > application, or use LIKE in a query.
>
> .. which is what pg_logdir_ls does. And it's robust against filenames
> that don't have valid dates too; imagine postgresql-2005-01-01_crash1.log.

True, but that is more for the application.  I don't imagine a user
looking at that from psql would have a problem.

However, you asked for a query that looks like pg_ls_logdir() and here
it is:

    SELECT pg_ls_dir
    FROM    (
            SELECT pg_ls_dir(t1.setting)
            FROM     (SELECT setting FROM pg_settings WHERE NAME = 'log_directory') AS t1
        ) AS t2,
        (SELECT setting FROM pg_settings WHERE NAME = 'log_filename') AS t3
    WHERE  t2.pg_ls_dir LIKE regexp_replace(t3.setting, '%.*', '') || '%';

The one thing it doesn't do, as you mentioned, is check for valid dates,
but it is certainly more flexible than embedding something in the backend.

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