Re: serverlog rotation/functions

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