Re: [HACKERS] serverlog rotation/functions

[Andreas Pflug]

Bruce Momjian wrote:
Andreas Pflug wrote:
You do something that splits the value into directory name and file name
and removes every letter after %.
        /var/log
        postgresql.log.%-%-%_%%%
Another idea is to allow filename wildcards in the listing so it
becomes:
        SELECT *
        FROM dir_listing('/var/log/postgresql.log.*-*-*_***') AS dir
While that is nice, it doesn't match the functionality of opendir so we
are perhaps better with one that doesn't handle wildcards and we just do
the wildcard processing in the WHERE clause.
Uh, this looks ugly.
How about
pg_logfile_list() RETURNS setof timestamp -- to list available logfiles
pg_logfile_filename(timestamp) to return filename for that logfile

I don't see the need to return timestamps. If you select any empty
directory, you can just return the file names.  The only reason you
might need a pattern is to distinguish pg log files from other log
files.  If you want, create a server-side function that returns the file
name with the strftime() patterns converted to '*'.

and generic
pg_dir(wildcard_text)

Maybe pg_dir_ls().
OK, it would be nice if we could do a sed operation like:
        sed 's/%./*/g'
but I don't know a way to do that without defining a function or pulling
in a procedural language, but if we could do it we could do:
        pg_dir(echo log_destination | sed 's/%./*/g')
Argggg.... ever used sed on win32?!? And how should the timestamp be 
represented in client tools? Date/time interpretation is always a source 
of problems, so *please* let the server do that.

Rethinking all this, I'd like the pg_logfile_list to return a complex type:
CREATE TYPE pg_logfile_list AS (
        filedate timestamp,
        filename text,
        backendpid int,
        inuse bool)
and
pg_logfile_list() RETURNS SETOF pg_logfile_list
which would enable
SELECT  filename,
        pg_file_unlink(filename)
  FROM  pg_logfile_list()
 WHERE  filedate < current_timestamp - '3 months'::interval
        AND NOT inuse
In-use check is easy for the backend, if the syslog process publishes 
the current logfile's timestamp in sharedmem.

We can use a GUC variable for the log_directory (not log_destination); 
anyway, I'd like the filenames to be selected by the server.

Regards,
Andreas
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
     subscribe-nomail command to [EMAIL PROTECTED] so that your
     message can get through to the mailing list cleanly