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
