Thread: Server instrumentation

Server instrumentation

From

Andreas Pflug

Date:

10 June 2005, 09:32:42

This post includes "dbsize backend integration" and "Server
instrumentation: pg_terminate_backend, pg_reload_conf"

Server instrumentation functions included:

pg_tablespace_size()
pg_database_size()
pg_relation_size()
pg_size_pretty()
     well-known from contrib/dbsize, waiting in unapplied patches
http://candle.pha.pa.us/mhonarc/patches/msg00002.html


The rest is superuser only:

pg_terminate_backend()
     exposing kill -SIGTERM <backendpid> to the client
pg_reload_conf()
     SIGHUP to reload configuration files


pg_logfile_rotate()
     Logfile rotation
pg_logdir_ls() and view pg_logdir_ls
     lists all available log files in the log directory.

As posted in the initial version of the logger subprocess from July 2004.


pg_file_stat()
pg_file_length()
pg_file_read()
pg_file_write()
pg_file_unlink()
pg_file_rename()
pg_dir_ls()
     generic file functions to access files in logdir and cluster dir.
These functions where implemented as initiated by Bruce, and discussed
intensively in
http://archives.postgresql.org/pgsql-patches/2004-07/msg00287.php


All functions are fully documented.
Additionally, documentation for pg_postmaster_start_time is included,
the code itself ist posted by Matthias Schmidt
http://candle.pha.pa.us/mhonarc/patches/msg00008.html

Regards,
Andreas

/*
 * dbsize.c
 * object size functions
 *
 * Copyright (c) 2002-2005, PostgreSQL Global Development Group
 *
 * IDENTIFICATION
 *      $PostgreSQL: pgsql/contrib/dbsize/dbsize.c,v 1.17 2005/05/27 00:57:48 neilc Exp $
 *
 */

#include "postgres.h"

#include <sys/types.h>
#include <sys/stat.h>

#include "access/heapam.h"
#include "catalog/namespace.h"
#include "catalog/pg_tablespace.h"
#include "commands/dbcommands.h"
#include "miscadmin.h"
#include "storage/fd.h"
#include "utils/builtins.h"
#include "utils/syscache.h"


/* Return physical size of directory contents, or 0 if dir doesn't exist */
static int64
db_dir_size(const char *path)
{
    int64        dirsize = 0;
    struct dirent *direntry;
    DIR         *dirdesc;
    char filename[MAXPGPATH];

    dirdesc = AllocateDir(path);

    if (!dirdesc)
        return 0;

    while ((direntry = readdir(dirdesc)) != NULL)
    {
        struct stat fst;

        if (strcmp(direntry->d_name, ".") == 0 ||
            strcmp(direntry->d_name, "..") == 0)
            continue;

        snprintf(filename, MAXPGPATH, "%s/%s", path, direntry->d_name);

        if (stat(filename, &fst) < 0)
            ereport(ERROR,
                    (errcode_for_file_access(),
                     errmsg("could not stat \"%s\": %m", filename)));
        dirsize += fst.st_size;
    }

    FreeDir(dirdesc);
    return dirsize;
}


static int64
calculate_database_size(Oid dbOid)
{
    int64        totalsize = 0;
    DIR         *dirdesc;
    struct dirent *direntry;
    char pathname[MAXPGPATH];

    /* Shared storage in pg_global is not counted */

    /* Include pg_default storage */
    snprintf(pathname, MAXPGPATH, "%s/base/%u", DataDir, dbOid);
    totalsize += db_dir_size(pathname);

    /* Scan the non-default tablespaces */
    snprintf(pathname, MAXPGPATH, "%s/pg_tblspc", DataDir);
    dirdesc = AllocateDir(pathname);
    if (!dirdesc)
        ereport(ERROR,
                (errcode_for_file_access(),
                 errmsg("could not open tablespace directory \"%s\": %m",
                        pathname)));

    while ((direntry = readdir(dirdesc)) != NULL)
    {
        if (strcmp(direntry->d_name, ".") == 0 ||
            strcmp(direntry->d_name, "..") == 0)
            continue;

        snprintf(pathname, MAXPGPATH, "%s/pg_tblspc/%s/%u",
                 DataDir, direntry->d_name, dbOid);
        totalsize += db_dir_size(pathname);
    }

    FreeDir(dirdesc);

    /* Complain if we found no trace of the DB at all */
    if (!totalsize)
        ereport(ERROR,
                (ERRCODE_UNDEFINED_DATABASE,
                 errmsg("database with OID %u does not exist", dbOid)));

    return totalsize;
}

/*
 * calculate total size of tablespace
 */
Datum
pg_tablespace_size(PG_FUNCTION_ARGS)
{
    Oid tblspcOid = PG_GETARG_OID(0);
    char tblspcPath[MAXPGPATH];
    char pathname[MAXPGPATH];
    int64        totalsize=0;
    DIR         *dirdesc;
    struct dirent *direntry;

    if (tblspcOid == DEFAULTTABLESPACE_OID)
        snprintf(tblspcPath, MAXPGPATH, "%s/base", DataDir);
    else if (tblspcOid == GLOBALTABLESPACE_OID)
        snprintf(tblspcPath, MAXPGPATH, "%s/global", DataDir);
    else
        snprintf(tblspcPath, MAXPGPATH, "%s/pg_tblspc/%u", DataDir, tblspcOid);

    dirdesc = AllocateDir(tblspcPath);

    if (!dirdesc)
        ereport(ERROR,
                (errcode_for_file_access(),
                 errmsg("could not open tablespace directory \"%s\": %m",
                        tblspcPath)));

    while ((direntry = readdir(dirdesc)) != NULL)
    {
        struct stat fst;

        if (strcmp(direntry->d_name, ".") == 0 ||
            strcmp(direntry->d_name, "..") == 0)
            continue;

        snprintf(pathname, MAXPGPATH, "%s/%s", tblspcPath, direntry->d_name);

        if (stat(pathname, &fst) < 0)
            ereport(ERROR,
                    (errcode_for_file_access(),
                     errmsg("could not stat \"%s\": %m", pathname)));
        totalsize += fst.st_size;

        if (fst.st_mode & S_IFDIR)
            totalsize += db_dir_size(pathname);
    }

    FreeDir(dirdesc);

    PG_RETURN_INT64(totalsize);
}


/*
 * calculate size of database in all tablespaces
 */
Datum
pg_database_size(PG_FUNCTION_ARGS)
{
    Oid dbOid = PG_GETARG_OID(0);

    PG_RETURN_INT64(calculate_database_size(dbOid));
}


/* Calculate relation size given tablespace and relation OIDs */
static int64
calculate_relation_size(Oid tblspcOid, Oid relnodeOid)
{
    int64        totalsize=0;
    unsigned int segcount=0;
    char dirpath[MAXPGPATH];
    char pathname[MAXPGPATH];

    if (!tblspcOid)
        tblspcOid = MyDatabaseTableSpace;

    if (tblspcOid == DEFAULTTABLESPACE_OID)
        snprintf(dirpath, MAXPGPATH, "%s/base/%u", DataDir, MyDatabaseId);
    else if (tblspcOid == GLOBALTABLESPACE_OID)
        snprintf(dirpath, MAXPGPATH, "%s/global", DataDir);
    else
        snprintf(dirpath, MAXPGPATH, "%s/pg_tblspc/%u/%u",
                 DataDir, tblspcOid, MyDatabaseId);

    for (segcount = 0 ;; segcount++)
    {
        struct stat fst;

        if (segcount == 0)
            snprintf(pathname, MAXPGPATH, "%s/%u",
                     dirpath, relnodeOid);
        else
            snprintf(pathname, MAXPGPATH, "%s/%u.%u",
                     dirpath, relnodeOid, segcount);

        if (stat(pathname, &fst) < 0)
        {
            if (errno == ENOENT)
                break;
            else
                ereport(ERROR,
                        (errcode_for_file_access(),
                         errmsg("could not stat \"%s\": %m", pathname)));
        }
        totalsize += fst.st_size;
    }

    return totalsize;
}

/*
 * calculate size of relation
 */
Datum
pg_relation_size(PG_FUNCTION_ARGS)
{
    Oid         relOid=PG_GETARG_OID(0);
    HeapTuple   tuple;
    Form_pg_class pg_class;
    Oid            relnodeOid;
    Oid         tblspcOid;

    tuple = SearchSysCache(RELOID,
                           ObjectIdGetDatum(relOid),
                           0, 0, 0);
    if (!HeapTupleIsValid(tuple))
        ereport(ERROR,
                (ERRCODE_UNDEFINED_TABLE,
                 errmsg("relation with OID %u does not exist", relOid)));

    pg_class = (Form_pg_class) GETSTRUCT(tuple);
    relnodeOid = pg_class->relfilenode;
    tblspcOid = pg_class->reltablespace;

    ReleaseSysCache(tuple);

    PG_RETURN_INT64(calculate_relation_size(tblspcOid, relnodeOid));
}


/*
 * formatting with size units
 */
Datum
pg_size_pretty(PG_FUNCTION_ARGS)
{
    int64 size=PG_GETARG_INT64(0);
    char *result=palloc(50+VARHDRSZ);
    int64 limit = 10*1024;
    int64 mult=1;

    if (size < limit*mult)
        snprintf(VARDATA(result), 50, INT64_FORMAT" bytes",
                 size);
    else
    {
        mult *= 1024;
        if (size < limit*mult)
             snprintf(VARDATA(result), 50, INT64_FORMAT " kB",
                      (size+mult/2) / mult);
        else
        {
            mult *= 1024;
            if (size < limit*mult)
                snprintf(VARDATA(result), 50, INT64_FORMAT " MB",
                         (size+mult/2) / mult);
            else
            {
                mult *= 1024;
                if (size < limit*mult)
                    snprintf(VARDATA(result), 50, INT64_FORMAT " GB",
                             (size+mult/2) / mult);
                else
                {
                    mult *= 1024;
                    snprintf(VARDATA(result), 50, INT64_FORMAT " TB",
                             (size+mult/2) / mult);
                }
            }
        }
    }

    VARATT_SIZEP(result) = strlen(VARDATA(result)) + VARHDRSZ;

    PG_RETURN_TEXT_P(result);
}

/*-------------------------------------------------------------------------
 *
 * genfile.c
 *
 *
 * Copyright (c) 2004, PostgreSQL Global Development Group
 *
 * Author: Andreas Pflug <pgadmin@pse-consulting.de>
 *
 * IDENTIFICATION
 *      $PostgreSQL: $
 *
 *-------------------------------------------------------------------------
 */
#include "postgres.h"

#include <sys/file.h>
#include <sys/stat.h>
#include <unistd.h>
#include <dirent.h>

#include "utils/builtins.h"
#include "miscadmin.h"
#include "storage/fd.h"
#include "catalog/pg_type.h"
#include "funcapi.h"

#ifdef WIN32

/* we don't want *nix emulating retry stuff here, but the native behaviour */

#ifdef rename
#undef rename
#endif

#ifdef unlink
#undef unlink
#endif

#endif

extern  char *Log_directory;


typedef struct
{
    char *location;
    DIR *dirdesc;
} directory_fctx;

/*-----------------------
 * some helper functions
 */

/*
 * Return an absolute path. Argument may be absolute or
 * relative to the DataDir.
 */
static char *absClusterPath(text *arg, bool logAllowed)
{
    char *filename;
    int len=VARSIZE(arg) - VARHDRSZ;
    int dlen = strlen(DataDir);

    filename = palloc(len+1);
    memcpy(filename, VARDATA(arg), len);
    filename[len] = 0;

    if (strstr(filename, "..") != NULL)
      ereport(ERROR,
              (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
               (errmsg("No .. allowed in filenames"))));

    if (is_absolute_path(filename))
    {
        if (logAllowed && !strncmp(filename, Log_directory, strlen(Log_directory)))
            return filename;
        if (strncmp(filename, DataDir, dlen))
            ereport(ERROR,
                    (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
                     (errmsg("Absolute path not allowed"))));

        return filename;
    }
    else
    {
        char *absname = palloc(dlen+len+2);
        sprintf(absname, "%s/%s", DataDir, filename);
        pfree(filename);
        return absname;
    }
}


/*
 * check for superuser, bark if not.
 */
static void
requireSuperuser(void)
{
    if (!superuser())
        ereport(ERROR,
                (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
                 (errmsg("only superuser may access generic file functions"))));
}



/* ------------------------------------
 * generic file handling functions
 */


Datum pg_file_stat(PG_FUNCTION_ARGS)
{
    AttInMetadata *attinmeta = NULL;
    char *    filename = absClusterPath(PG_GETARG_TEXT_P(0), true);
    struct stat fst;
    int64 length;
    char lenbuf[30];
    char cbuf[30], abuf[30], mbuf[30], dbuf[]="f";
    char *values[5] =
       { lenbuf, cbuf, abuf, mbuf, dbuf };

    pg_time_t timestamp;
    HeapTuple tuple;

    if (attinmeta == NULL)
    {
        TupleDesc tupdesc = CreateTemplateTupleDesc(5, false);

        TupleDescInitEntry(tupdesc, (AttrNumber) 1, "length",
                           INT8OID, -1, 0);
        TupleDescInitEntry(tupdesc, (AttrNumber) 2, "ctime",
                           TIMESTAMPOID, -1, 0);
        TupleDescInitEntry(tupdesc, (AttrNumber) 3, "atime",
                           TIMESTAMPOID, -1, 0);
        TupleDescInitEntry(tupdesc, (AttrNumber) 4, "mtime",
                           TIMESTAMPOID, -1, 0);
        TupleDescInitEntry(tupdesc, (AttrNumber) 5, "isdir",
                           BOOLOID, -1, 0);

        attinmeta = TupleDescGetAttInMetadata(tupdesc);
    }

    if (stat(filename, &fst) < 0)
    {
        ereport(WARNING,
                (errcode_for_file_access(),
                 errmsg("could not stat file %s: %m", filename)));

        strcpy(lenbuf, "-1");
        strcpy(cbuf, "NULL");
        strcpy(abuf, "NULL");
        strcpy(mbuf, "NULL");
    }
    else
    {
        length = fst.st_size;
        snprintf(lenbuf, 30, INT64_FORMAT, length);

        timestamp = fst.st_ctime;
        pg_strftime(cbuf, 30, "%F %T", pg_localtime(×tamp, global_timezone));

        timestamp = fst.st_atime;
        pg_strftime(abuf, 30, "%F %T", pg_localtime(×tamp, global_timezone));

        timestamp = fst.st_mtime;
        pg_strftime(mbuf, 30, "%F %T", pg_localtime(×tamp, global_timezone));

        if (fst.st_mode & S_IFDIR)
          dbuf[0] = 't';
    }
    tuple = BuildTupleFromCStrings(attinmeta, values);

    PG_RETURN_DATUM(HeapTupleGetDatum(tuple));
}


Datum pg_file_read(PG_FUNCTION_ARGS)
{
    size_t size;
    char *buf=0;
    size_t nbytes;
    int64 pos;
    FILE *f;
    char *filename;

    requireSuperuser();

    filename = absClusterPath(PG_GETARG_TEXT_P(0), true);
    pos = PG_GETARG_INT64(1);
    size = PG_GETARG_INT64(2);

    f = fopen(filename, "rb");
    if (!f)
    {
        ereport(ERROR,
                (errcode_for_file_access(),
                 errmsg("could not open file %s for reading: %m", filename)));
        PG_RETURN_NULL();
    }

    if (pos >= 0)
        fseek(f, pos, SEEK_SET);
    else
        fseek(f, pos, SEEK_END);


    buf = palloc(size + VARHDRSZ);

    nbytes = fread(VARDATA(buf), 1, size, f);
    if (nbytes < 0)
    {
        ereport(ERROR,
                (errcode_for_file_access(),
                 errmsg("could not read file %s: %m", filename)));
        PG_RETURN_NULL();
    }
    VARATT_SIZEP(buf) = nbytes + VARHDRSZ;
    fclose(f);

    PG_RETURN_TEXT_P(buf);
}


Datum pg_file_write(PG_FUNCTION_ARGS)
{
    FILE *f;
    char *filename;
    text *data;
    int64 count = 0;

    requireSuperuser();

    filename = absClusterPath(PG_GETARG_TEXT_P(0), false);
    data = PG_GETARG_TEXT_P(1);

    if (PG_ARGISNULL(2) || !PG_GETARG_BOOL(2))
    {
        struct stat fst;
        if (stat(filename, &fst) >= 0)
            ereport(ERROR,
                    (ERRCODE_DUPLICATE_FILE,
                     errmsg("file %s exists", filename)));

        f = fopen(filename, "wb");
    }
    else
        f = fopen(filename, "ab");

    if (!f)
    {
        ereport(ERROR,
                (errcode_for_file_access(),
                 errmsg("could open file %s for writing: %m", filename)));
    }

    if (VARSIZE(data) != 0)
    {
        count = fwrite(VARDATA(data), 1, VARSIZE(data) - VARHDRSZ, f);

        if (count != VARSIZE(data) - VARHDRSZ)
            ereport(ERROR,
                    (errcode_for_file_access(),
                     errmsg("error writing file %s: %m", filename)));
    }
    fclose(f);

    PG_RETURN_INT64(count);
}


Datum pg_file_rename(PG_FUNCTION_ARGS)
{
    char *fn1, *fn2, *fn3;
    int rc;

    requireSuperuser();

    if (PG_ARGISNULL(0) || PG_ARGISNULL(1))
        PG_RETURN_NULL();

    fn1=absClusterPath(PG_GETARG_TEXT_P(0), false);
    fn2=absClusterPath(PG_GETARG_TEXT_P(1), false);
    if (PG_ARGISNULL(2))
        fn3=0;
    else
        fn3=absClusterPath(PG_GETARG_TEXT_P(2), false);

    if (access(fn1, W_OK) < 0)
    {
        ereport(WARNING,
                (errcode_for_file_access(),
                 errmsg("file %s not accessible: %m", fn1)));

        PG_RETURN_BOOL(false);
    }

    if (fn3 && access(fn2, W_OK) < 0)
    {
        ereport(WARNING,
                (errcode_for_file_access(),
                 errmsg("file %s not accessible: %m", fn2)));

        PG_RETURN_BOOL(false);
    }


    rc = access(fn3 ? fn3 : fn2, 2);
    if (rc >= 0 || errno != ENOENT)
    {
        ereport(ERROR,
                (ERRCODE_DUPLICATE_FILE,
                 errmsg("cannot rename to target file %s", fn3 ? fn3 : fn2)));
    }

    if (fn3)
    {
        if (rename(fn2, fn3) != 0)
        {
            ereport(ERROR,
                    (errcode_for_file_access(),
                     errmsg("could not rename %s to %s: %m", fn2, fn3)));
        }
        if (rename(fn1, fn2) != 0)
        {
            ereport(WARNING,
                    (errcode_for_file_access(),
                     errmsg("could not rename %s to %s: %m", fn1, fn2)));

            if (rename(fn3, fn2) != 0)
            {
                ereport(ERROR,
                        (errcode_for_file_access(),
                         errmsg("could not rename %s back to %s: %m", fn3, fn2)));
            }
            else
            {
                ereport(ERROR,
                        (ERRCODE_UNDEFINED_FILE,
                         errmsg("renaming %s to %s was reverted", fn2, fn3)));

            }
        }
    }
    else if (rename(fn1, fn2) != 0)
    {
            ereport(WARNING,
                    (errcode_for_file_access(),
                     errmsg("renaming %s to %s %m", fn1, fn2)));
        ereport(ERROR,
                (errcode_for_file_access(),
                 errmsg("could not rename %s to %s: %m", fn1, fn2)));
    }

    PG_RETURN_BOOL(true);
}


Datum pg_file_unlink(PG_FUNCTION_ARGS)
{
    char *filename;

    requireSuperuser();

    filename = absClusterPath(PG_GETARG_TEXT_P(0), false);

    if (access(filename, W_OK) < 0)
    {
        if (errno == ENOENT)
            PG_RETURN_BOOL(false);
        else
            ereport(ERROR,
                    (errcode_for_file_access(),
                     errmsg("file %s not accessible: %m", filename)));

    }

    if (unlink(filename) < 0)
    {
        ereport(WARNING,
                (errcode_for_file_access(),
                 errmsg("could not unlink file %s: %m", filename)));

        PG_RETURN_BOOL(false);
    }
    PG_RETURN_BOOL(true);
}


Datum pg_dir_ls(PG_FUNCTION_ARGS)
{
    FuncCallContext *funcctx;
    struct dirent *de;
    directory_fctx *fctx;

    requireSuperuser();

    if (SRF_IS_FIRSTCALL())
    {
        MemoryContext oldcontext;

        funcctx=SRF_FIRSTCALL_INIT();
        oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);

        fctx = palloc(sizeof(directory_fctx));
        fctx->location = absClusterPath(PG_GETARG_TEXT_P(0), false);

        fctx->dirdesc = AllocateDir(fctx->location);

        if (!fctx->dirdesc)
            ereport(ERROR,
                    (errcode_for_file_access(),
                     errmsg("%s is not browsable: %m", fctx->location)));

        if (PG_ARGISNULL(1) || !PG_GETARG_BOOL(1))
        {
            pfree(fctx->location);
            fctx->location = 0;
        }
        funcctx->user_fctx = fctx;
        MemoryContextSwitchTo(oldcontext);
    }

    funcctx=SRF_PERCALL_SETUP();
    fctx = (directory_fctx*) funcctx->user_fctx;

    if (!fctx->dirdesc)  /* not a readable directory  */
        SRF_RETURN_DONE(funcctx);

    while ((de = readdir(fctx->dirdesc)) != NULL)
    {
        char *name;
        text *result;
        int len;
        if (!strcmp(de->d_name, ".") || !strcmp(de->d_name, ".."))
            continue;
        if (fctx->location)
        {
            char *path=palloc(strlen(fctx->location) + strlen(de->d_name) +2);
            sprintf(path, "%s/%s", fctx->location, de->d_name);

            name = path;
        }
        else
            name = de->d_name;


        len = strlen(name);
        result = palloc(len + VARHDRSZ);
        VARATT_SIZEP(result) = len + VARHDRSZ;
        memcpy(VARDATA(result), name, len);

        SRF_RETURN_NEXT(funcctx, PointerGetDatum(result));
    }

    FreeDir(fctx->dirdesc);
    SRF_RETURN_DONE(funcctx);
}



Index: doc/src/sgml/func.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.253
diff -u -r1.253 func.sgml
--- doc/src/sgml/func.sgml    9 Jun 2005 16:35:08 -0000    1.253
+++ doc/src/sgml/func.sgml    10 Jun 2005 09:27:22 -0000
@@ -8130,6 +8130,11 @@
        <entry><type>text</type></entry>
        <entry>PostgreSQL version information</entry>
       </row>
+      <row>
+       <entry><function>pg_postmaster_start_time()</function></entry>
+       <entry><type>timestamp</type></entry>
+       <entry>PostgreSQL postmaster process start time</entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
@@ -8225,6 +8230,15 @@
     <productname>PostgreSQL</productname> server's version.
    </para>

+   <indexterm zone="functions-info">
+    <primary>pg_postmaster_start_time</primary>
+   </indexterm>
+
+   <para>
+    <function>pg_postmaster_start_time()</function> returns a timestamp
+    giving the time when the postmaster process was started.
+   </para>
+
   <indexterm>
    <primary>privilege</primary>
    <secondary>querying</secondary>
@@ -8898,6 +8912,12 @@
    <indexterm zone="functions-admin">
     <primary>pg_cancel_backend</primary>
    </indexterm>
+   <indexterm zone="functions-admin">
+    <primary>pg_terminate_backend</primary>
+   </indexterm>
+   <indexterm zone="functions-admin">
+    <primary>pg_reload_conf</primary>
+   </indexterm>

    <indexterm zone="functions-admin">
     <primary>signal</primary>
@@ -8905,7 +8925,7 @@
    </indexterm>

    <para>
-    The function shown in <xref
+    The functions shown in <xref
     linkend="functions-admin-signal-table"> sends control signals to
     other server processes.  Use of this function is restricted
     to superusers.
@@ -8927,21 +8947,46 @@
        <entry><type>int</type></entry>
        <entry>Cancel a backend's current query</entry>
       </row>
+      <row>
+       <entry>
+        <literal><function>pg_terminate_backend</function>(<parameter>pid</parameter>)</literal>
+        </entry>
+       <entry><type>int</type></entry>
+       <entry>Terminate a backend process</entry>
+      </row>
+      <row>
+       <entry>
+        <literal><function>pg_reload_conf</function>()</literal>
+        </entry>
+       <entry><type>int</type></entry>
+       <entry>Triggers the server processes to reload configuration files</entry>
+      </row>
      </tbody>
     </tgroup>
    </table>

    <para>
-    This function returns 1 if successful, 0 if not successful.
+    These functions return 1 if successful, 0 if not successful.
     The process ID (<literal>pid</literal>) of an active backend can be found
     from the <structfield>procpid</structfield> column in the
     <structname>pg_stat_activity</structname> view, or by listing the <command>postgres</command>
     processes on the server with <application>ps</>.
    </para>
-
-   <indexterm zone="functions-admin">
-    <primary>pg_start_backup</primary>
-   </indexterm>
+   <para>
+    Terminating a backend with <function>pg_terminate_backend</>
+    should be used only as a last resort, i.e. if the backend process
+    doesn't react to <function>pg_cancel_backend</> any more and can't
+    be controlled otherwise. Since the exact state of the
+    backend at the moment of termination isn't precisely known, some
+    locked resources might remain in the server's shared memory
+    structure, effectively blocking other backends. In this case,
+    you'd have to stop and restart the postmaster.
+   </para>
+   <para>
+    <function>pg_reload_conf</> sends a SIGHUP event to the
+    postmaster, and thus triggers a reload of the configuration files
+    in all backend processes.
+   </para>

    <indexterm zone="functions-admin">
     <primary>pg_stop_backup</primary>
@@ -9008,6 +9053,272 @@
     For details about proper usage of these functions, see
     <xref linkend="backup-online">.
    </para>
+
+   <para>
+    The functions shown in <xref
+    linkend="functions-admin-dbsize"> calculate the actual disk space
+    usage of database objects.
+   </para>
+
+   <table id="functions-admin-dbsize">
+    <title>Database Object Size Functions</title>
+    <tgroup cols="3">
+     <thead>
+      <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry>
+      </row>
+     </thead>
+
+     <tbody>
+      <row>
+       <entry>
+        <literal><function>pg_tablespace_size</function>(<parameter>tablespace_oid</parameter>)</literal>
+        </entry>
+       <entry><type>int8</type></entry>
+       <entry>Calculates total disk space of a tablespace</entry>
+      </row>
+      <row>
+       <entry>
+        <literal><function>pg_database_size</function>(<parameter>database_oid</parameter>)</literal>
+        </entry>
+       <entry><type>int8</type></entry>
+       <entry>Calculates total disk space of a database</entry>
+      </row>
+      <row>
+       <entry>
+        <literal><function>pg_relation_size</function>(<parameter>relation_oid</parameter>)</literal>
+        </entry>
+       <entry><type>int8</type></entry>
+       <entry>Calculates total disk space of a relation</entry>
+      </row>
+      <row>
+       <entry>
+        <literal><function>pg_size_pretty</function>(<parameter>size_int8</parameter>)</literal>
+        </entry>
+       <entry><type>text</type></entry>
+       <entry>Formats the size in a human readable format with size units </entry>
+      </row>
+     </tbody>
+    </tgroup>
+   </table>
+
+   <para>
+    <function>pg_tablespace_size</>, <function>pg_database_size</>
+    and <function>pg_relation_size</> accept an oid, and calculate
+    the disk space usage of the object.
+   </para>
+
+   <indexterm zone="functions-admin">
+    <primary>pg_relation_size</primary>
+   </indexterm>
+   <para>
+    <function>pg_relation_size</> accepts oids of standard tables, indexes and
+    toast tables. To calculate the disk space usage of a table
+    including all its indexes and toast tables, these components need
+    to be summed up.
+   </para>
+   <para>
+    <function>pg_size_pretty</> can be used to format the size of the
+    database objects in a human readable way, using kB, MB, GB or TB as appropriate.
+   </para>
+
+   <para>
+    The functions shown in <xref
+    linkend="functions-admin-genfile"> provide native file access to
+    files on the machine hosting the server. They are restricted to
+    the cluster directory or the logfile directory.
+    Use of these functions is restricted to superusers.
+   </para>
+
+   <table id="functions-admin-genfile">
+    <title>Generic File Access Functions</title>
+    <tgroup cols="3">
+     <thead>
+      <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry>
+      </row>
+     </thead>
+
+     <tbody>
+      <row>
+       <entry>
+        <literal><function>pg_file_stat</function>(<parameter>filename_text</parameter>)</literal>
+        </entry>
+       <entry><type>record</type></entry>
+       <entry>Retrieves file stats</entry>
+      </row>
+      <row>
+       <entry>
+        <literal><function>pg_file_length</function>(<parameter>filename_text</parameter>)</literal>
+        </entry>
+       <entry><type>int8</type></entry>
+       <entry>Returns the file length</entry>
+      </row>
+      <row>
+       <entry>
+        <literal><function>pg_file_read</function>(<parameter>filename_text</parameter>,
+        <parameter>offset_int8</parameter>,<parameter>length_int8</parameter>)</literal>
+        </entry>
+       <entry><type>text</type></entry>
+       <entry>Returns the contents of a text file</entry>
+      </row>
+      <row>
+       <entry>
+
<literal><function>pg_file_write</function>(<parameter>filename_text</parameter>,<parameter>offset_int8></parameter>,<parameter>append_bool</parameter>)</literal>
+        </entry>
+       <entry><type>int8</type></entry>
+       <entry>Writes text to a file</entry>
+      </row>
+      <row>
+       <entry>
+
<literal><function>pg_file_rename</function>(<parameter>oldname_text</parameter>,<parameter>newname_text</parameter>)</literal>
+        </entry>
+       <entry><type>bool</type></entry>
+       <entry>Renames a file</entry>
+      </row>
+      <row>
+       <entry>
+
<literal><function>pg_file_rename</function>(<parameter>oldname_text</parameter>,<parameter>newname_text</parameter>,<parameter>archivname_text</parameter>)</literal>
+        </entry>
+       <entry><type>bool</type></entry>
+       <entry>Renames a file and its archive file</entry>
+      </row>
+      <row>
+       <entry>
+        <literal><function>pg_file_unlink</function>(<parameter>filename_text</parameter>)</literal>
+        </entry>
+       <entry><type>bool</type></entry>
+       <entry>Returns the file length</entry>
+      </row>
+      <row>
+       <entry>
+
<literal><function>pg_dir_ls</function>(<parameter>dirname_text</parameter>,<parameter>fullpath_bool</parameter>)</literal>
+        </entry>
+       <entry><type>setof text</type></entry>
+       <entry>Returns the file length</entry>
+      </row>
+     </tbody>
+    </tgroup>
+   </table>
+
+   <indexterm zone="functions-admin">
+    <primary>pg_file_stat</primary>
+   </indexterm>
+
+   <para>
+    <function>pg_file_stat()</> returns a record that contains the
+    length, creation timestamp, last accessed timestamp, last modified
+    timestamp and and a flag indicating a directory.
+   </para>
+
+   <indexterm zone="functions-admin">
+    <primary>pg_file_length</primary>
+   </indexterm>
+   <para>
+    <function>pg_file_length()</> returns the length of the given file.
+   </para>
+
+   <indexterm zone="functions-admin">
+    <primary>pg_file_read</primary>
+   </indexterm>
+   <para>
+    <function>pg_file_read()</> returns a part of a textfile, starting
+    at the offset giving length bytes.
+
+   <indexterm zone="functions-admin">
+    <primary>pg_file_write</primary>
+   </indexterm>
+   <para>
+    <function>pg_file_write()</> writes text to a file at the given
+    offset. If the append flag is false, the file may not exist. If
+    the append file is set, the file must exist and be accessible.
+   </para>
+
+   <indexterm zone="functions-admin">
+    <primary>pg_file_rename</primary>
+   </indexterm>
+   <para>
+    <function>pg_file_rename(oldname_text,newname_text)</> is used to
+    rename a file to a new name.
+   </para>
+   <para>
+    <function>pg_file_rename(oldname_text,newname_text,archivname_text)</> is used to
+    rename a file and its backup file to new names. The
+    oldname and newname files must exist, while the archivname file
+    may not exist when this command is issued.This can be used
+    to replace important files in a relatively safe way, because the
+    function will try to do the whole renaming or nothing at all.
+   Example:
+   <programlisting>
+   select pg_file_rename('postgresql.conf.tmp', 'postgresql.conf', 'postgresql.conf.bak');
+   </programlisting>
+    will replace postgresql.conf with a new version which was
+    previously written to the temporary file postgresql.conf.tmp,
+    saving the former version as postgresql.conf.bak.
+   </para>
+
+   <indexterm zone="functions-admin">
+    <primary>pg_file_unlink</primary>
+   </indexterm>
+   <para>
+    <function>pg_file_unlink()</> unlinks the file.
+   </para>
+   <para>
+    <function>pg_dir_ls</> lists all filenames in the named directory.
+   </para>
+
+   <para>
+    The functions shown in <xref
+    linkend="functions-admin-logfile"> allow access to the server
+    logfile, if the stderr log output is redirected.
+    Use of these functions is restricted to superusers.
+   </para>
+
+   <table id="functions-admin-logfile">
+    <title>Backend Logfile Functions</title>
+    <tgroup cols="3">
+     <thead>
+      <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry>
+      </row>
+     </thead>
+
+     <tbody>
+      <row>
+       <entry>
+        <literal><function>pg_logfile_rotate</function>()</literal>
+        </entry>
+       <entry><type>int</type></entry>
+       <entry>Trigger logfile rotation</entry>
+      </row>
+      <row>
+       <entry>
+        <literal><function>pg_logdir_ls</function>()</literal>
+        </entry>
+       <entry><type>setof record</type></entry>
+       <entry>lists all logfiles in the pg_log subdirectory</entry>
+      </row>
+     </tbody>
+    </tgroup>
+   </table>
+
+   <indexterm zone="functions-admin">
+    <primary>pg_logfile_rotate</primary>
+   </indexterm>
+   <para>
+    <function>pg_logfile_rotate</> issues a logfile rotation trigger,
+    which forces the server to close the current logfile and open a
+    fresh one.
+   </para>
+
+   <indexterm zone="functions-admin">
+    <primary>pg_logdir_ls</primary>
+   </indexterm>
+   <para>
+    <function>pg_logdir_ls</> lists all files in the pg_log
+    subdirectory. For each log file, a record consisting of a
+    timestamp of its creation time and a complete path to the file is
+    returned. For convenience, the view pg_logdir_ls wraps the
+    function.
+   </para>
+
   </sect1>
 </chapter>

Index: src/backend/catalog/system_views.sql
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/catalog/system_views.sql,v
retrieving revision 1.13
diff -u -r1.13 system_views.sql
--- src/backend/catalog/system_views.sql    17 May 2005 21:46:09 -0000    1.13
+++ src/backend/catalog/system_views.sql    10 Jun 2005 09:27:24 -0000
@@ -282,3 +282,8 @@
     DO INSTEAD NOTHING;

 GRANT SELECT, UPDATE ON pg_settings TO PUBLIC;
+
+CREATE VIEW pg_logdir_ls AS
+    SELECT *
+    FROM pg_logdir_ls() AS A
+    (filetime timestamp, filename text);
Index: src/backend/postmaster/postmaster.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/postmaster/postmaster.c,v
retrieving revision 1.452
diff -u -r1.452 postmaster.c
--- src/backend/postmaster/postmaster.c    9 Jun 2005 22:01:12 -0000    1.452
+++ src/backend/postmaster/postmaster.c    10 Jun 2005 09:27:31 -0000
@@ -3306,6 +3306,11 @@
         }
     }

+    if (CheckPostmasterSignal(PMSIGNAL_ROTATE_LOGFILE) && SysLoggerPID != 0)
+    {
+        kill(SysLoggerPID, SIGUSR1);
+    }
+
     PG_SETMASK(&UnBlockSig);

     errno = save_errno;
Index: src/backend/postmaster/syslogger.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/postmaster/syslogger.c,v
retrieving revision 1.15
diff -u -r1.15 syslogger.c
--- src/backend/postmaster/syslogger.c    19 Apr 2005 03:13:59 -0000    1.15
+++ src/backend/postmaster/syslogger.c    10 Jun 2005 09:27:32 -0000
@@ -101,6 +101,7 @@
  * Flags set by interrupt handlers for later service in the main loop.
  */
 static volatile sig_atomic_t got_SIGHUP = false;
+static volatile sig_atomic_t rotation_requested = false;


 /* Local subroutines */
@@ -117,6 +118,7 @@
 static char *logfile_getname(pg_time_t timestamp);
 static void set_next_rotation_time(void);
 static void sigHupHandler(SIGNAL_ARGS);
+static void sigUsr1Handler(SIGNAL_ARGS);


 /*
@@ -200,7 +202,7 @@
     pqsignal(SIGQUIT, SIG_IGN);
     pqsignal(SIGALRM, SIG_IGN);
     pqsignal(SIGPIPE, SIG_IGN);
-    pqsignal(SIGUSR1, SIG_IGN);
+    pqsignal(SIGUSR1, sigUsr1Handler);  /* request log rotation */
     pqsignal(SIGUSR2, SIG_IGN);

     /*
@@ -235,7 +237,6 @@
     /* main worker loop */
     for (;;)
     {
-        bool        rotation_requested = false;
         bool        time_based_rotation = false;

 #ifndef WIN32
@@ -734,6 +735,8 @@
     char       *filename;
     FILE       *fh;

+    rotation_requested = false;
+
     /*
      * When doing a time-based rotation, invent the new logfile name based
      * on the planned rotation time, not current time, to avoid "slippage"
@@ -887,3 +890,10 @@
 {
     got_SIGHUP = true;
 }
+
+/* SIGUSR1: set flag to rotate logfile */
+static void
+sigUsr1Handler(SIGNAL_ARGS)
+{
+    rotation_requested = true;
+}
Index: src/backend/utils/adt/Makefile
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/Makefile,v
retrieving revision 1.57
diff -u -r1.57 Makefile
--- src/backend/utils/adt/Makefile    1 Apr 2004 21:28:45 -0000    1.57
+++ src/backend/utils/adt/Makefile    10 Jun 2005 09:27:33 -0000
@@ -24,7 +24,7 @@
     tid.o timestamp.o varbit.o varchar.o varlena.o version.o xid.o \
     network.o mac.o inet_net_ntop.o inet_net_pton.o \
     ri_triggers.o pg_lzcompress.o pg_locale.o formatting.o \
-    ascii.o quote.o pgstatfuncs.o encode.o
+    ascii.o quote.o pgstatfuncs.o encode.o dbsize.o genfile.o

 like.o: like.c like_match.c

Index: src/backend/utils/adt/misc.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/misc.c,v
retrieving revision 1.43
diff -u -r1.43 misc.c
--- src/backend/utils/adt/misc.c    19 May 2005 21:35:47 -0000    1.43
+++ src/backend/utils/adt/misc.c    10 Jun 2005 09:27:33 -0000
@@ -17,15 +17,20 @@
 #include <sys/file.h>
 #include <signal.h>
 #include <dirent.h>
+#include <time.h>
+#include <sys/time.h>

 #include "commands/dbcommands.h"
 #include "miscadmin.h"
 #include "storage/procarray.h"
+#include "storage/pmsignal.h"
 #include "storage/fd.h"
 #include "utils/builtins.h"
+#include "utils/elog.h"
 #include "funcapi.h"
 #include "catalog/pg_type.h"
 #include "catalog/pg_tablespace.h"
+#include "postmaster/syslogger.h"

 #define atooid(x)  ((Oid) strtoul((x), NULL, 10))

@@ -101,22 +106,177 @@
     return 1;
 }

+
 Datum
 pg_cancel_backend(PG_FUNCTION_ARGS)
 {
     PG_RETURN_INT32(pg_signal_backend(PG_GETARG_INT32(0), SIGINT));
 }

-#ifdef NOT_USED
-
-/* Disabled in 8.0 due to reliability concerns; FIXME someday */

 Datum
 pg_terminate_backend(PG_FUNCTION_ARGS)
 {
     PG_RETURN_INT32(pg_signal_backend(PG_GETARG_INT32(0), SIGTERM));
 }
-#endif
+
+
+Datum
+pg_reload_conf(PG_FUNCTION_ARGS)
+{
+    if (!superuser())
+        ereport(ERROR,
+                (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+                 (errmsg("only superuser can signal the postmaster"))));
+
+    if (kill(PostmasterPid, SIGHUP))
+    {
+        ereport(WARNING,
+                (errmsg("failed to send signal to postmaster: %m")));
+
+        PG_RETURN_INT32(0);
+    }
+
+    PG_RETURN_INT32(1);
+}
+
+
+/*
+ * Rotate log file
+ */
+Datum
+pg_logfile_rotate(PG_FUNCTION_ARGS)
+{
+    if (!superuser())
+        ereport(ERROR,
+                (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+                 (errmsg("only superuser can rotate log files"))));
+
+    if (!Redirect_stderr)
+    {
+        ereport(NOTICE,
+                (errcode(ERRCODE_WARNING),
+                 errmsg("no logfile configured; rotation not supported")));
+        PG_RETURN_INT32(0);
+    }
+
+    SendPostmasterSignal(PMSIGNAL_ROTATE_LOGFILE);
+
+    PG_RETURN_INT32(0);
+}
+
+typedef struct
+{
+    char *location;
+    DIR *dirdesc;
+} directory_fctx;
+
+/*
+ * scan log directory for log files
+ */
+Datum pg_logdir_ls(PG_FUNCTION_ARGS)
+{
+    FuncCallContext *funcctx;
+    struct dirent *de;
+    directory_fctx *fctx;
+
+    if (!superuser())
+        ereport(ERROR,
+                (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+                 (errmsg("only superuser can list the log directory"))));
+
+    if (memcmp(Log_filename, "postgresql-%Y-%m-%d_%H%M%S.log", 30) != 0)
+        ereport(ERROR,
+                (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+                 (errmsg("the log_filename parameter must equal 'postgresql-%%Y-%%m-%%d_%%H%%M%%S.log'"))));
+
+    if (SRF_IS_FIRSTCALL())
+    {
+        MemoryContext oldcontext;
+        TupleDesc tupdesc;
+
+        funcctx=SRF_FIRSTCALL_INIT();
+        oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+        fctx = palloc(sizeof(directory_fctx));
+        if (is_absolute_path(Log_directory))
+            fctx->location = Log_directory;
+        else
+        {
+            fctx->location = palloc(strlen(DataDir) + strlen(Log_directory) +2);
+            sprintf(fctx->location, "%s/%s", DataDir, Log_directory);
+        }
+        tupdesc = CreateTemplateTupleDesc(2, false);
+        TupleDescInitEntry(tupdesc, (AttrNumber) 1, "starttime",
+                           TIMESTAMPOID, -1, 0);
+        TupleDescInitEntry(tupdesc, (AttrNumber) 2, "filename",
+                           TEXTOID, -1, 0);
+
+        funcctx->attinmeta = TupleDescGetAttInMetadata(tupdesc);
+
+        fctx->dirdesc = AllocateDir(fctx->location);
+
+        if (!fctx->dirdesc)
+            ereport(ERROR,
+                    (errcode_for_file_access(),
+                     errmsg("%s is not browsable: %m", fctx->location)));
+
+        funcctx->user_fctx = fctx;
+        MemoryContextSwitchTo(oldcontext);
+    }
+
+    funcctx=SRF_PERCALL_SETUP();
+    fctx = (directory_fctx*) funcctx->user_fctx;
+
+    if (!fctx->dirdesc)  /* not a readable directory  */
+        SRF_RETURN_DONE(funcctx);
+
+    while ((de = readdir(fctx->dirdesc)) != NULL)
+    {
+        char *values[2];
+        HeapTuple tuple;
+
+        char     *field[MAXDATEFIELDS];
+        char    lowstr[MAXDATELEN + 1];
+        int        dtype;
+        int        nf, ftype[MAXDATEFIELDS];
+        fsec_t    fsec;
+        int        tz = 0;
+        struct    pg_tm date;
+
+        /*
+         * Default format:
+         *        postgresql-YYYY-MM-DD_HHMMSS.log
+         */
+        if (strlen(de->d_name) != 32
+            || memcmp(de->d_name, "postgresql-", 11)
+            || de->d_name[21] != '_'
+            || strcmp(de->d_name + 28, ".log"))
+              continue;
+
+        values[1] = palloc(strlen(fctx->location) + strlen(de->d_name) + 2);
+        sprintf(values[1], "%s/%s", fctx->location, de->d_name);
+
+        values[0] = de->d_name + 11;       /* timestamp */
+        values[0][17] = 0;
+
+                    /* parse and decode expected timestamp */
+        if (ParseDateTime(values[0], lowstr, sizeof(lowstr), field, ftype, MAXDATEFIELDS, &nf))
+            continue;
+
+        if (DecodeDateTime(field, ftype, nf, &dtype, &date, &fsec, &tz))
+            continue;
+
+        /* Seems the format fits the expected format; feed it into the tuple */
+
+        tuple = BuildTupleFromCStrings(funcctx->attinmeta, values);
+
+        SRF_RETURN_NEXT(funcctx, HeapTupleGetDatum(tuple));
+    }
+
+    FreeDir(fctx->dirdesc);
+    SRF_RETURN_DONE(funcctx);
+}


 /* Function to find out which databases make use of a tablespace */
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.365
diff -u -r1.365 pg_proc.h
--- src/include/catalog/pg_proc.h    9 Jun 2005 16:35:09 -0000    1.365
+++ src/include/catalog/pg_proc.h    10 Jun 2005 09:27:52 -0000
@@ -3018,12 +3018,50 @@
 DESCR("is conversion visible in search path?");


+DATA(insert OID = 2168 ( pg_terminate_backend    PGNSP PGUID 12 f f t f v 1 23 "23" _null_ _null_ _null_
pg_terminate_backend- _null_ )); 
+DESCR("Terminate a server process");
 DATA(insert OID = 2171 ( pg_cancel_backend        PGNSP PGUID 12 f f t f v 1 23 "23" _null_ _null_ _null_
pg_cancel_backend- _null_ )); 
 DESCR("Cancel a server process' current query");
 DATA(insert OID = 2172 ( pg_start_backup        PGNSP PGUID 12 f f t f v 1 25 "25" _null_ _null_ _null_
pg_start_backup- _null_ )); 
 DESCR("Prepare for taking an online backup");
 DATA(insert OID = 2173 ( pg_stop_backup            PGNSP PGUID 12 f f t f v 0 25 "" _null_ _null_ _null_
pg_stop_backup- _null_ )); 
 DESCR("Finish taking an online backup");
+DATA(insert OID = 2284 ( pg_reload_conf         PGNSP PGUID 12 f f t f v 0 23 "" _null_ _null_ _null_ pg_reload_conf -
_null_)); 
+DESCR("Reloads configuration files");
+
+DATA(insert OID = 2285 ( pg_tablespace_size        PGNSP PGUID 12 f f t f v 1 20 "26" _null_ _null_ _null_
pg_tablespace_size- _null_ )); 
+DESCR("Calculate total disk space usage for tablespace");
+DATA(insert OID = 2286 ( pg_database_size        PGNSP PGUID 12 f f t f v 1 20 "26" _null_ _null_ _null_
pg_database_size- _null_ )); 
+DESCR("Calculate total disk space usage for database");
+DATA(insert OID = 2287 ( pg_relation_size        PGNSP PGUID 12 f f t f v 1 20 "26" _null_ _null_ _null_
pg_relation_size- _null_ )); 
+DESCR("Calculate total disk space usage for relation");
+DATA(insert OID = 2288 ( pg_size_pretty          PGNSP PGUID 12 f f t f v 1 25 "20" _null_ _null_ _null_
pg_size_pretty- _null_ )); 
+DESCR("Convert a long int to a human readable text using size units");
+
+DATA(insert OID = 1232 ( pg_logfile_rotate        PGNSP PGUID 12 f f t f v 0 23 "" _null_ _null_ _null_
pg_logfile_rotate- _null_ )); 
+DESCR("rotate log file");
+DATA(insert OID = 1233 ( pg_logdir_ls            PGNSP PGUID 12 f f t t v 0 2249 "" _null_ _null_ _null_ pg_logdir_ls
-_null_ )); 
+DESCR("list all available log files");
+
+
+DATA(insert OID = 2560 ( pg_file_stat        PGNSP PGUID 12 f f t f v 1 2249 "25" _null_ _null_ _null_ pg_file_stat -
_null_)); 
+DESCR("retrieve file stats");
+DATA(insert OID = 2561 ( pg_file_length        PGNSP PGUID 14 f f t f v 1 20 "25" _null_ _null_ _null_ "SELECT len
FROMpg_file_stat($1) AS s(len int8, c timestamp, a timestamp, m timestamp, i bool)" - _null_ )); 
+DESCR("returns length of a file");
+DATA(insert OID = 2562 ( pg_file_read        PGNSP PGUID 12 f f t f v 3 25 "25 20 20" _null_ _null_ _null_
pg_file_read- _null_ )); 
+DESCR("reads text from a file");
+DATA(insert OID = 2563 ( pg_file_write      PGNSP PGUID 12 f f t f v 3 20 "25 25 16" _null_ _null_ _null_
pg_file_write- _null_ )); 
+DESCR("writes text to a file");
+DATA(insert OID = 2564 ( pg_file_rename        PGNSP PGUID 12 f f f f v 3 16 "25 25 25" _null_ _null_ _null_
pg_file_rename- _null_ )); 
+DESCR("renames a file and its backup file");
+DATA(insert OID = 2565 ( pg_file_rename        PGNSP PGUID 12 f f f t v 2 16 "25 25" _null_ _null_ _null_
pg_file_rename- _null_ )); 
+DESCR("renames a file");
+DATA(insert OID = 2566 ( pg_file_unlink        PGNSP PGUID 12 f f t f v 1 16 "25" _null_ _null_ _null_ pg_file_unlink
-_null_ )); 
+DESCR("unlinks a file");
+DATA(insert OID = 2567 ( pg_dir_ls            PGNSP PGUID 12 f f t t v 2 25 "25 16" _null_ _null_ _null_ pg_dir_ls -
_null_)); 
+DESCR("lists all filenames from a directory");
+
+


 /* Aggregates (moved here from pg_aggregate for 7.3) */
Index: src/include/storage/pmsignal.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/storage/pmsignal.h,v
retrieving revision 1.11
diff -u -r1.11 pmsignal.h
--- src/include/storage/pmsignal.h    31 Dec 2004 22:03:42 -0000    1.11
+++ src/include/storage/pmsignal.h    10 Jun 2005 09:27:53 -0000
@@ -25,7 +25,7 @@
     PMSIGNAL_PASSWORD_CHANGE,    /* pg_pwd file has changed */
     PMSIGNAL_WAKEN_CHILDREN,    /* send a SIGUSR1 signal to all backends */
     PMSIGNAL_WAKEN_ARCHIVER,    /* send a NOTIFY signal to xlog archiver */
-
+    PMSIGNAL_ROTATE_LOGFILE,    /* send SIGUSR1 to syslogger to rotate logfile */
     NUM_PMSIGNALS                /* Must be last value of enum! */
 } PMSignalReason;

Index: src/include/utils/builtins.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/utils/builtins.h,v
retrieving revision 1.257
diff -u -r1.257 builtins.h
--- src/include/utils/builtins.h    27 May 2005 00:57:49 -0000    1.257
+++ src/include/utils/builtins.h    10 Jun 2005 09:27:55 -0000
@@ -362,7 +362,26 @@
 extern Datum nonnullvalue(PG_FUNCTION_ARGS);
 extern Datum current_database(PG_FUNCTION_ARGS);
 extern Datum pg_cancel_backend(PG_FUNCTION_ARGS);
+extern Datum pg_terminate_backend(PG_FUNCTION_ARGS);
+extern Datum pg_reload_conf(PG_FUNCTION_ARGS);
 extern Datum pg_tablespace_databases(PG_FUNCTION_ARGS);
+extern Datum pg_logfile_rotate(PG_FUNCTION_ARGS);
+extern Datum pg_logdir_ls(PG_FUNCTION_ARGS);
+
+/* dbsize.c */
+extern Datum pg_tablespace_size(PG_FUNCTION_ARGS);
+extern Datum pg_database_size(PG_FUNCTION_ARGS);
+extern Datum pg_relation_size(PG_FUNCTION_ARGS);
+extern Datum pg_size_pretty(PG_FUNCTION_ARGS);
+
+
+/* genfile.c */
+extern Datum pg_file_stat(PG_FUNCTION_ARGS);
+extern Datum pg_file_read(PG_FUNCTION_ARGS);
+extern Datum pg_file_write(PG_FUNCTION_ARGS);
+extern Datum pg_file_rename(PG_FUNCTION_ARGS);
+extern Datum pg_file_unlink(PG_FUNCTION_ARGS);
+extern Datum pg_dir_ls(PG_FUNCTION_ARGS);

 /* not_in.c */
 extern Datum int4notin(PG_FUNCTION_ARGS);

Re: Server instrumentation

From

Tom Lane

Date:

10 June 2005, 13:51:38

Andreas Pflug <pgadmin@pse-consulting.de> writes:
> pg_terminate_backend()
>      exposing kill -SIGTERM <backendpid> to the client

The objections to this have not changed since last year; in fact they
are stronger because we have at least one report of actual trouble
with retail SIGTERMs.

> pg_file_stat()
> pg_file_length()
> pg_file_read()
> pg_file_write()
> pg_file_unlink()
> pg_file_rename()
> pg_dir_ls()

I really have serious doubts about the value of this.

            regards, tom lane

Re: Server instrumentation

From

Andreas Pflug

Date:

10 June 2005, 14:14:26

Tom Lane wrote:
> Andreas Pflug <pgadmin@pse-consulting.de> writes:
>
>>pg_terminate_backend()
>>     exposing kill -SIGTERM <backendpid> to the client
>
>
> The objections to this have not changed since last year; in fact they
> are stronger because we have at least one report of actual trouble
> with retail SIGTERMs.

I'm not arguing about that, still SIGTERMing a single backend is widely
used.

>
>>pg_file_stat()
>>pg_file_length()
>>pg_file_read()
>>pg_file_write()
>>pg_file_unlink()
>>pg_file_rename()
>>pg_dir_ls()
>
>
> I really have serious doubts about the value of this.

You're insisting on this exceptionally weak argument for a year now. We
all know that you personally do everything from the cmd line, but there
*is* a requirement to have additional ways of access to config files. I
do have wide positive feedback on this, see the discussion a month back
(and some more private mails). And besides, they are already widely in
use by pgadmin from the win32 pgsql distro when displaying logfiles,
which is installed by default.

Regards,
Andreas

Re: Server instrumentation

From

"Dave Page"

Date:

10 June 2005, 14:24:04


> -----Original Message-----
> From: pgsql-patches-owner@postgresql.org
> [mailto:pgsql-patches-owner@postgresql.org] On Behalf Of Tom Lane
> Sent: 10 June 2005 14:52
> To: Andreas Pflug
> Cc: PostgreSQL-patches
> Subject: Re: [PATCHES] Server instrumentation
>
>
> > pg_file_stat()
> > pg_file_length()
> > pg_file_read()
> > pg_file_write()
> > pg_file_unlink()
> > pg_file_rename()
> > pg_dir_ls()
>
> I really have serious doubts about the value of this.

They allow superusers to view and modify the server configuration using
tools like pgAdmin or phpPgAdmin. The functionality this allows has been
standard in SQL Server for years and users coming from that platform
that we are now offering an alternative to expect this. It's been a
while, but iirc, DB2 was likewise configurable in this way last time I
used it, and I believe Oracle was as well.

Regards, Dave.

Re: Server instrumentation

From

Andreas Pflug

Date:

21 June 2005, 09:41:19

- Updated to current cvs.
- pg_terminate_backend omitted
- contrib/dbsize cleaned to reflect move of functions to core

Index: contrib/dbsize/README.dbsize
===================================================================
RCS file: /projects/cvsroot/pgsql/contrib/dbsize/README.dbsize,v
retrieving revision 1.5
diff -u -r1.5 README.dbsize
--- contrib/dbsize/README.dbsize    26 Feb 2005 23:31:15 -0000    1.5
+++ contrib/dbsize/README.dbsize    21 Jun 2005 09:24:09 -0000
@@ -1,21 +1,15 @@
 This module contains several functions that report the on-disk size of a
-given database object in bytes:
+given database object in bytes. They extend the builtin object size functions.
+

     int8 database_size(name)
     int8 relation_size(text)
     int8 indexes_size(text)
     int8 total_relation_size(text)
-
-    int8 pg_database_size(oid)
-    int8 pg_relation_size(oid)
-    int8 pg_tablespace_size(oid)
-
-    text pg_size_pretty(int8)
-
     setof record relation_size_components(text)

-The first four functions take the name of the object (possibly
-schema-qualified for the latter three) and returns the size of the
+These functions take the name of the object (possibly
+schema-qualified for the second) and returns the size of the
 on-disk files in bytes.

     SELECT database_size('template1');
@@ -23,11 +17,6 @@
     SELECT indexes_size('pg_class');
     SELECT total_relation_size('pg_class');

-These functions take object OIDs:
-
-    SELECT pg_database_size(1);         -- template1 database
-    SELECT pg_relation_size(1259);      -- pg_class table size
-    SELECT pg_tablespace_size(1663);    -- pg_default tablespace

 The indexes_size() function returns the total size of the indices for a
 relation, including any toasted indices.
@@ -35,7 +24,7 @@
 The total_relation_size() function returns the total size of the relation,
 all its indices, and any toasted data.

-Please note that relation_size and pg_relation_size report only the size of
+Please note that relation_size reports only the size of
 the selected relation itself; any related indexes or toast tables are not
 counted.  To obtain the total size of a table including all indices and
 toasted data, use total_relation_size().
Index: contrib/dbsize/dbsize.c
===================================================================
RCS file: /projects/cvsroot/pgsql/contrib/dbsize/dbsize.c,v
retrieving revision 1.18
diff -u -r1.18 dbsize.c
--- contrib/dbsize/dbsize.c    19 Jun 2005 21:34:00 -0000    1.18
+++ contrib/dbsize/dbsize.c    21 Jun 2005 09:24:09 -0000
@@ -27,19 +27,9 @@
 /* hack to make it compile under Win32 */
 extern DLLIMPORT char *DataDir;

-Datum pg_tablespace_size(PG_FUNCTION_ARGS);
-Datum pg_database_size(PG_FUNCTION_ARGS);
-Datum pg_relation_size(PG_FUNCTION_ARGS);
-Datum pg_size_pretty(PG_FUNCTION_ARGS);
-
 Datum database_size(PG_FUNCTION_ARGS);
 Datum relation_size(PG_FUNCTION_ARGS);

-PG_FUNCTION_INFO_V1(pg_tablespace_size);
-PG_FUNCTION_INFO_V1(pg_database_size);
-PG_FUNCTION_INFO_V1(pg_relation_size);
-PG_FUNCTION_INFO_V1(pg_size_pretty);
-
 PG_FUNCTION_INFO_V1(database_size);
 PG_FUNCTION_INFO_V1(relation_size);

@@ -120,64 +110,6 @@
     return totalsize;
 }

-/*
- * calculate total size of tablespace
- */
-Datum
-pg_tablespace_size(PG_FUNCTION_ARGS)
-{
-    Oid tblspcOid = PG_GETARG_OID(0);
-    char tblspcPath[MAXPGPATH];
-    char pathname[MAXPGPATH];
-    int64        totalsize=0;
-    DIR         *dirdesc;
-    struct dirent *direntry;
-
-    if (tblspcOid == DEFAULTTABLESPACE_OID)
-        snprintf(tblspcPath, MAXPGPATH, "%s/base", DataDir);
-    else if (tblspcOid == GLOBALTABLESPACE_OID)
-        snprintf(tblspcPath, MAXPGPATH, "%s/global", DataDir);
-    else
-        snprintf(tblspcPath, MAXPGPATH, "%s/pg_tblspc/%u", DataDir, tblspcOid);
-
-    dirdesc = AllocateDir(tblspcPath);
-
-    while ((direntry = ReadDir(dirdesc, tblspcPath)) != NULL)
-    {
-        struct stat fst;
-
-        if (strcmp(direntry->d_name, ".") == 0 ||
-            strcmp(direntry->d_name, "..") == 0)
-            continue;
-
-        snprintf(pathname, MAXPGPATH, "%s/%s", tblspcPath, direntry->d_name);
-
-        if (stat(pathname, &fst) < 0)
-            ereport(ERROR,
-                    (errcode_for_file_access(),
-                     errmsg("could not stat \"%s\": %m", pathname)));
-        totalsize += fst.st_size;
-
-        if (fst.st_mode & S_IFDIR)
-            totalsize += db_dir_size(pathname);
-    }
-
-    FreeDir(dirdesc);
-
-    PG_RETURN_INT64(totalsize);
-}
-
-
-/*
- * calculate size of database in all tablespaces
- */
-Datum
-pg_database_size(PG_FUNCTION_ARGS)
-{
-    Oid dbOid = PG_GETARG_OID(0);
-
-    PG_RETURN_INT64(calculate_database_size(dbOid));
-}

 Datum
 database_size(PG_FUNCTION_ARGS)
@@ -241,34 +173,6 @@
     return totalsize;
 }

-/*
- * calculate size of relation
- */
-Datum
-pg_relation_size(PG_FUNCTION_ARGS)
-{
-    Oid         relOid=PG_GETARG_OID(0);
-    HeapTuple   tuple;
-    Form_pg_class pg_class;
-    Oid            relnodeOid;
-    Oid         tblspcOid;
-
-    tuple = SearchSysCache(RELOID,
-                           ObjectIdGetDatum(relOid),
-                           0, 0, 0);
-    if (!HeapTupleIsValid(tuple))
-        ereport(ERROR,
-                (ERRCODE_UNDEFINED_TABLE,
-                 errmsg("relation with OID %u does not exist", relOid)));
-
-    pg_class = (Form_pg_class) GETSTRUCT(tuple);
-    relnodeOid = pg_class->relfilenode;
-    tblspcOid = pg_class->reltablespace;
-
-    ReleaseSysCache(tuple);
-
-    PG_RETURN_INT64(calculate_relation_size(tblspcOid, relnodeOid));
-}

 Datum
 relation_size(PG_FUNCTION_ARGS)
@@ -289,50 +193,3 @@

     PG_RETURN_INT64(calculate_relation_size(tblspcOid, relnodeOid));
 }
-
-/*
- * formatting with size units
- */
-Datum
-pg_size_pretty(PG_FUNCTION_ARGS)
-{
-    int64 size=PG_GETARG_INT64(0);
-    char *result=palloc(50+VARHDRSZ);
-    int64 limit = 10*1024;
-    int64 mult=1;
-
-    if (size < limit*mult)
-        snprintf(VARDATA(result), 50, INT64_FORMAT" bytes",
-                 size);
-    else
-    {
-        mult *= 1024;
-        if (size < limit*mult)
-             snprintf(VARDATA(result), 50, INT64_FORMAT " kB",
-                      (size+mult/2) / mult);
-        else
-        {
-            mult *= 1024;
-            if (size < limit*mult)
-                snprintf(VARDATA(result), 50, INT64_FORMAT " MB",
-                         (size+mult/2) / mult);
-            else
-            {
-                mult *= 1024;
-                if (size < limit*mult)
-                    snprintf(VARDATA(result), 50, INT64_FORMAT " GB",
-                             (size+mult/2) / mult);
-                else
-                {
-                    mult *= 1024;
-                    snprintf(VARDATA(result), 50, INT64_FORMAT " TB",
-                             (size+mult/2) / mult);
-                }
-            }
-        }
-    }
-
-    VARATT_SIZEP(result) = strlen(VARDATA(result)) + VARHDRSZ;
-
-    PG_RETURN_TEXT_P(result);
-}
Index: contrib/dbsize/dbsize.sql.in
===================================================================
RCS file: /projects/cvsroot/pgsql/contrib/dbsize/dbsize.sql.in,v
retrieving revision 1.5
diff -u -r1.5 dbsize.sql.in
--- contrib/dbsize/dbsize.sql.in    26 Feb 2005 23:31:15 -0000    1.5
+++ contrib/dbsize/dbsize.sql.in    21 Jun 2005 09:24:10 -0000
@@ -6,22 +6,6 @@
     AS 'MODULE_PATHNAME', 'relation_size'
     LANGUAGE C STRICT;

-CREATE FUNCTION pg_tablespace_size(oid) RETURNS bigint
-    AS 'MODULE_PATHNAME', 'pg_tablespace_size'
-    LANGUAGE C STRICT;
-
-CREATE FUNCTION pg_database_size(oid) RETURNS bigint
-    AS 'MODULE_PATHNAME', 'pg_database_size'
-    LANGUAGE C STRICT;
-
-CREATE FUNCTION pg_relation_size(oid) RETURNS bigint
-    AS 'MODULE_PATHNAME', 'pg_relation_size'
-    LANGUAGE C STRICT;
-
-CREATE FUNCTION pg_size_pretty(bigint) RETURNS text
-    AS 'MODULE_PATHNAME', 'pg_size_pretty'
-    LANGUAGE C STRICT;
-
 CREATE FUNCTION total_relation_size (text) RETURNS bigint AS '
 SELECT pg_relation_size(r.oid)
      + COALESCE(pg_relation_size(t.oid), 0)::bigint
@@ -129,4 +113,3 @@
   AND idxr.reltoastrelid = idxtoast.oid
   AND idxtoast.reltoastrelid = idxtoastidxr.oid
 ' LANGUAGE SQL;
-
Index: doc/src/sgml/func.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.258
diff -u -r1.258 func.sgml
--- doc/src/sgml/func.sgml    15 Jun 2005 06:29:25 -0000    1.258
+++ doc/src/sgml/func.sgml    21 Jun 2005 09:24:32 -0000
@@ -8916,6 +8916,9 @@
    <indexterm zone="functions-admin">
     <primary>pg_cancel_backend</primary>
    </indexterm>
+   <indexterm zone="functions-admin">
+    <primary>pg_reload_conf</primary>
+   </indexterm>

    <indexterm zone="functions-admin">
     <primary>signal</primary>
@@ -8923,7 +8926,7 @@
    </indexterm>

    <para>
-    The function shown in <xref
+    The functions shown in <xref
     linkend="functions-admin-signal-table"> sends control signals to
     other server processes.  Use of this function is restricted
     to superusers.
@@ -8945,21 +8948,29 @@
        <entry><type>int</type></entry>
        <entry>Cancel a backend's current query</entry>
       </row>
+      <row>
+       <entry>
+        <literal><function>pg_reload_conf</function>()</literal>
+        </entry>
+       <entry><type>int</type></entry>
+       <entry>Triggers the server processes to reload configuration files</entry>
+      </row>
      </tbody>
     </tgroup>
    </table>

    <para>
-    This function returns 1 if successful, 0 if not successful.
+    These functions return 1 if successful, 0 if not successful.
     The process ID (<literal>pid</literal>) of an active backend can be found
     from the <structfield>procpid</structfield> column in the
     <structname>pg_stat_activity</structname> view, or by listing the <command>postgres</command>
     processes on the server with <application>ps</>.
    </para>
-
-   <indexterm zone="functions-admin">
-    <primary>pg_start_backup</primary>
-   </indexterm>
+   <para>
+    <function>pg_reload_conf</> sends a SIGHUP event to the
+    postmaster, and thus triggers a reload of the configuration files
+    in all backend processes.
+   </para>

    <indexterm zone="functions-admin">
     <primary>pg_stop_backup</primary>
@@ -9026,6 +9037,272 @@
     For details about proper usage of these functions, see
     <xref linkend="backup-online">.
    </para>
+
+   <para>
+    The functions shown in <xref
+    linkend="functions-admin-dbsize"> calculate the actual disk space
+    usage of database objects.
+   </para>
+
+   <table id="functions-admin-dbsize">
+    <title>Database Object Size Functions</title>
+    <tgroup cols="3">
+     <thead>
+      <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry>
+      </row>
+     </thead>
+
+     <tbody>
+      <row>
+       <entry>
+        <literal><function>pg_tablespace_size</function>(<parameter>tablespace_oid</parameter>)</literal>
+        </entry>
+       <entry><type>int8</type></entry>
+       <entry>Calculates total disk space of a tablespace</entry>
+      </row>
+      <row>
+       <entry>
+        <literal><function>pg_database_size</function>(<parameter>database_oid</parameter>)</literal>
+        </entry>
+       <entry><type>int8</type></entry>
+       <entry>Calculates total disk space of a database</entry>
+      </row>
+      <row>
+       <entry>
+        <literal><function>pg_relation_size</function>(<parameter>relation_oid</parameter>)</literal>
+        </entry>
+       <entry><type>int8</type></entry>
+       <entry>Calculates total disk space of a relation</entry>
+      </row>
+      <row>
+       <entry>
+        <literal><function>pg_size_pretty</function>(<parameter>size_int8</parameter>)</literal>
+        </entry>
+       <entry><type>text</type></entry>
+       <entry>Formats the size in a human readable format with size units </entry>
+      </row>
+     </tbody>
+    </tgroup>
+   </table>
+
+   <para>
+    <function>pg_tablespace_size</>, <function>pg_database_size</>
+    and <function>pg_relation_size</> accept an oid, and calculate
+    the disk space usage of the object.
+   </para>
+
+   <indexterm zone="functions-admin">
+    <primary>pg_relation_size</primary>
+   </indexterm>
+   <para>
+    <function>pg_relation_size</> accepts oids of standard tables, indexes and
+    toast tables. To calculate the disk space usage of a table
+    including all its indexes and toast tables, these components need
+    to be summed up.
+   </para>
+   <para>
+    <function>pg_size_pretty</> can be used to format the size of the
+    database objects in a human readable way, using kB, MB, GB or TB as appropriate.
+   </para>
+
+   <para>
+    The functions shown in <xref
+    linkend="functions-admin-genfile"> provide native file access to
+    files on the machine hosting the server. They are restricted to
+    the cluster directory or the logfile directory.
+    Use of these functions is restricted to superusers.
+   </para>
+
+   <table id="functions-admin-genfile">
+    <title>Generic File Access Functions</title>
+    <tgroup cols="3">
+     <thead>
+      <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry>
+      </row>
+     </thead>
+
+     <tbody>
+      <row>
+       <entry>
+        <literal><function>pg_file_stat</function>(<parameter>filename_text</parameter>)</literal>
+        </entry>
+       <entry><type>record</type></entry>
+       <entry>Retrieves file stats</entry>
+      </row>
+      <row>
+       <entry>
+        <literal><function>pg_file_length</function>(<parameter>filename_text</parameter>)</literal>
+        </entry>
+       <entry><type>int8</type></entry>
+       <entry>Returns the file length</entry>
+      </row>
+      <row>
+       <entry>
+        <literal><function>pg_file_read</function>(<parameter>filename_text</parameter>,
+        <parameter>offset_int8</parameter>,<parameter>length_int8</parameter>)</literal>
+        </entry>
+       <entry><type>text</type></entry>
+       <entry>Returns the contents of a text file</entry>
+      </row>
+      <row>
+       <entry>
+
<literal><function>pg_file_write</function>(<parameter>filename_text</parameter>,<parameter>offset_int8></parameter>,<parameter>append_bool</parameter>)</literal>
+        </entry>
+       <entry><type>int8</type></entry>
+       <entry>Writes text to a file</entry>
+      </row>
+      <row>
+       <entry>
+
<literal><function>pg_file_rename</function>(<parameter>oldname_text</parameter>,<parameter>newname_text</parameter>)</literal>
+        </entry>
+       <entry><type>bool</type></entry>
+       <entry>Renames a file</entry>
+      </row>
+      <row>
+       <entry>
+
<literal><function>pg_file_rename</function>(<parameter>oldname_text</parameter>,<parameter>newname_text</parameter>,<parameter>archivname_text</parameter>)</literal>
+        </entry>
+       <entry><type>bool</type></entry>
+       <entry>Renames a file and its archive file</entry>
+      </row>
+      <row>
+       <entry>
+        <literal><function>pg_file_unlink</function>(<parameter>filename_text</parameter>)</literal>
+        </entry>
+       <entry><type>bool</type></entry>
+       <entry>Returns the file length</entry>
+      </row>
+      <row>
+       <entry>
+
<literal><function>pg_dir_ls</function>(<parameter>dirname_text</parameter>,<parameter>fullpath_bool</parameter>)</literal>
+        </entry>
+       <entry><type>setof text</type></entry>
+       <entry>Returns the file length</entry>
+      </row>
+     </tbody>
+    </tgroup>
+   </table>
+
+   <indexterm zone="functions-admin">
+    <primary>pg_file_stat</primary>
+   </indexterm>
+
+   <para>
+    <function>pg_file_stat()</> returns a record that contains the
+    length, creation timestamp, last accessed timestamp, last modified
+    timestamp and and a flag indicating a directory.
+   </para>
+
+   <indexterm zone="functions-admin">
+    <primary>pg_file_length</primary>
+   </indexterm>
+   <para>
+    <function>pg_file_length()</> returns the length of the given file.
+   </para>
+
+   <indexterm zone="functions-admin">
+    <primary>pg_file_read</primary>
+   </indexterm>
+   <para>
+    <function>pg_file_read()</> returns a part of a textfile, starting
+    at the offset giving length bytes.
+
+   <indexterm zone="functions-admin">
+    <primary>pg_file_write</primary>
+   </indexterm>
+   <para>
+    <function>pg_file_write()</> writes text to a file at the given
+    offset. If the append flag is false, the file may not exist. If
+    the append file is set, the file must exist and be accessible.
+   </para>
+
+   <indexterm zone="functions-admin">
+    <primary>pg_file_rename</primary>
+   </indexterm>
+   <para>
+    <function>pg_file_rename(oldname_text,newname_text)</> is used to
+    rename a file to a new name.
+   </para>
+   <para>
+    <function>pg_file_rename(oldname_text,newname_text,archivname_text)</> is used to
+    rename a file and its backup file to new names. The
+    oldname and newname files must exist, while the archivname file
+    may not exist when this command is issued.This can be used
+    to replace important files in a relatively safe way, because the
+    function will try to do the whole renaming or nothing at all.
+   Example:
+   <programlisting>
+   select pg_file_rename('postgresql.conf.tmp', 'postgresql.conf', 'postgresql.conf.bak');
+   </programlisting>
+    will replace postgresql.conf with a new version which was
+    previously written to the temporary file postgresql.conf.tmp,
+    saving the former version as postgresql.conf.bak.
+   </para>
+
+   <indexterm zone="functions-admin">
+    <primary>pg_file_unlink</primary>
+   </indexterm>
+   <para>
+    <function>pg_file_unlink()</> unlinks the file.
+   </para>
+   <para>
+    <function>pg_dir_ls</> lists all filenames in the named directory.
+   </para>
+
+   <para>
+    The functions shown in <xref
+    linkend="functions-admin-logfile"> allow access to the server
+    logfile, if the stderr log output is redirected.
+    Use of these functions is restricted to superusers.
+   </para>
+
+   <table id="functions-admin-logfile">
+    <title>Backend Logfile Functions</title>
+    <tgroup cols="3">
+     <thead>
+      <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry>
+      </row>
+     </thead>
+
+     <tbody>
+      <row>
+       <entry>
+        <literal><function>pg_logfile_rotate</function>()</literal>
+        </entry>
+       <entry><type>int</type></entry>
+       <entry>Trigger logfile rotation</entry>
+      </row>
+      <row>
+       <entry>
+        <literal><function>pg_logdir_ls</function>()</literal>
+        </entry>
+       <entry><type>setof record</type></entry>
+       <entry>lists all logfiles in the pg_log subdirectory</entry>
+      </row>
+     </tbody>
+    </tgroup>
+   </table>
+
+   <indexterm zone="functions-admin">
+    <primary>pg_logfile_rotate</primary>
+   </indexterm>
+   <para>
+    <function>pg_logfile_rotate</> issues a logfile rotation trigger,
+    which forces the server to close the current logfile and open a
+    fresh one.
+   </para>
+
+   <indexterm zone="functions-admin">
+    <primary>pg_logdir_ls</primary>
+   </indexterm>
+   <para>
+    <function>pg_logdir_ls</> lists all files in the pg_log
+    subdirectory. For each log file, a record consisting of a
+    timestamp of its creation time and a complete path to the file is
+    returned. For convenience, the view pg_logdir_ls wraps the
+    function.
+   </para>
+
   </sect1>
 </chapter>

Index: src/backend/catalog/system_views.sql
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/catalog/system_views.sql,v
retrieving revision 1.15
diff -u -r1.15 system_views.sql
--- src/backend/catalog/system_views.sql    18 Jun 2005 19:33:42 -0000    1.15
+++ src/backend/catalog/system_views.sql    21 Jun 2005 09:24:33 -0000
@@ -292,3 +292,8 @@
                     pg_stat_get_db_blocks_hit(D.oid) AS blks_read,
             pg_stat_get_db_blocks_hit(D.oid) AS blks_hit
     FROM pg_database D;
+
+CREATE VIEW pg_logdir_ls AS
+    SELECT *
+    FROM pg_logdir_ls() AS A
+    (filetime timestamp, filename text);
Index: src/backend/postmaster/postmaster.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/postmaster/postmaster.c,v
retrieving revision 1.454
diff -u -r1.454 postmaster.c
--- src/backend/postmaster/postmaster.c    17 Jun 2005 22:32:44 -0000    1.454
+++ src/backend/postmaster/postmaster.c    21 Jun 2005 09:24:41 -0000
@@ -3319,6 +3319,11 @@
         }
     }

+    if (CheckPostmasterSignal(PMSIGNAL_ROTATE_LOGFILE) && SysLoggerPID != 0)
+    {
+        kill(SysLoggerPID, SIGUSR1);
+    }
+
     PG_SETMASK(&UnBlockSig);

     errno = save_errno;
Index: src/backend/postmaster/syslogger.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/postmaster/syslogger.c,v
retrieving revision 1.15
diff -u -r1.15 syslogger.c
--- src/backend/postmaster/syslogger.c    19 Apr 2005 03:13:59 -0000    1.15
+++ src/backend/postmaster/syslogger.c    21 Jun 2005 09:24:42 -0000
@@ -101,6 +101,7 @@
  * Flags set by interrupt handlers for later service in the main loop.
  */
 static volatile sig_atomic_t got_SIGHUP = false;
+static volatile sig_atomic_t rotation_requested = false;


 /* Local subroutines */
@@ -117,6 +118,7 @@
 static char *logfile_getname(pg_time_t timestamp);
 static void set_next_rotation_time(void);
 static void sigHupHandler(SIGNAL_ARGS);
+static void sigUsr1Handler(SIGNAL_ARGS);


 /*
@@ -200,7 +202,7 @@
     pqsignal(SIGQUIT, SIG_IGN);
     pqsignal(SIGALRM, SIG_IGN);
     pqsignal(SIGPIPE, SIG_IGN);
-    pqsignal(SIGUSR1, SIG_IGN);
+    pqsignal(SIGUSR1, sigUsr1Handler);  /* request log rotation */
     pqsignal(SIGUSR2, SIG_IGN);

     /*
@@ -235,7 +237,6 @@
     /* main worker loop */
     for (;;)
     {
-        bool        rotation_requested = false;
         bool        time_based_rotation = false;

 #ifndef WIN32
@@ -734,6 +735,8 @@
     char       *filename;
     FILE       *fh;

+    rotation_requested = false;
+
     /*
      * When doing a time-based rotation, invent the new logfile name based
      * on the planned rotation time, not current time, to avoid "slippage"
@@ -887,3 +890,10 @@
 {
     got_SIGHUP = true;
 }
+
+/* SIGUSR1: set flag to rotate logfile */
+static void
+sigUsr1Handler(SIGNAL_ARGS)
+{
+    rotation_requested = true;
+}
Index: src/backend/utils/adt/Makefile
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/Makefile,v
retrieving revision 1.57
diff -u -r1.57 Makefile
--- src/backend/utils/adt/Makefile    1 Apr 2004 21:28:45 -0000    1.57
+++ src/backend/utils/adt/Makefile    21 Jun 2005 09:24:42 -0000
@@ -24,7 +24,7 @@
     tid.o timestamp.o varbit.o varchar.o varlena.o version.o xid.o \
     network.o mac.o inet_net_ntop.o inet_net_pton.o \
     ri_triggers.o pg_lzcompress.o pg_locale.o formatting.o \
-    ascii.o quote.o pgstatfuncs.o encode.o
+    ascii.o quote.o pgstatfuncs.o encode.o dbsize.o genfile.o

 like.o: like.c like_match.c

Index: src/backend/utils/adt/misc.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/misc.c,v
retrieving revision 1.44
diff -u -r1.44 misc.c
--- src/backend/utils/adt/misc.c    19 Jun 2005 21:34:02 -0000    1.44
+++ src/backend/utils/adt/misc.c    21 Jun 2005 09:24:43 -0000
@@ -17,15 +17,20 @@
 #include <sys/file.h>
 #include <signal.h>
 #include <dirent.h>
+#include <time.h>
+#include <sys/time.h>

 #include "commands/dbcommands.h"
 #include "miscadmin.h"
 #include "storage/procarray.h"
+#include "storage/pmsignal.h"
 #include "storage/fd.h"
 #include "utils/builtins.h"
+#include "utils/elog.h"
 #include "funcapi.h"
 #include "catalog/pg_type.h"
 #include "catalog/pg_tablespace.h"
+#include "postmaster/syslogger.h"

 #define atooid(x)  ((Oid) strtoul((x), NULL, 10))

@@ -107,16 +112,170 @@
     PG_RETURN_INT32(pg_signal_backend(PG_GETARG_INT32(0), SIGINT));
 }

-#ifdef NOT_USED

-/* Disabled in 8.0 due to reliability concerns; FIXME someday */
+Datum
+pg_reload_conf(PG_FUNCTION_ARGS)
+{
+    if (!superuser())
+        ereport(ERROR,
+                (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+                 (errmsg("only superuser can signal the postmaster"))));
+
+    if (kill(PostmasterPid, SIGHUP))
+    {
+        ereport(WARNING,
+                (errmsg("failed to send signal to postmaster: %m")));
+
+        PG_RETURN_INT32(0);
+    }
+
+    PG_RETURN_INT32(1);
+}
+
+
+/*
+ * Rotate log file
+ */
+Datum
+pg_logfile_rotate(PG_FUNCTION_ARGS)
+{
+    if (!superuser())
+        ereport(ERROR,
+                (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+                 (errmsg("only superuser can rotate log files"))));
+
+    if (!Redirect_stderr)
+    {
+        ereport(NOTICE,
+                (errcode(ERRCODE_WARNING),
+                 errmsg("no logfile configured; rotation not supported")));
+        PG_RETURN_INT32(0);
+    }
+
+    SendPostmasterSignal(PMSIGNAL_ROTATE_LOGFILE);
+
+    PG_RETURN_INT32(0);
+}
+
+typedef struct
+{
+    char *location;
+    DIR *dirdesc;
+} directory_fctx;
+
+/*
+ * scan log directory for log files
+ */
+Datum pg_logdir_ls(PG_FUNCTION_ARGS)
+{
+    FuncCallContext *funcctx;
+    struct dirent *de;
+    directory_fctx *fctx;
+
+    if (!superuser())
+        ereport(ERROR,
+                (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+                 (errmsg("only superuser can list the log directory"))));
+
+    if (memcmp(Log_filename, "postgresql-%Y-%m-%d_%H%M%S.log", 30) != 0)
+        ereport(ERROR,
+                (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+                 (errmsg("the log_filename parameter must equal 'postgresql-%%Y-%%m-%%d_%%H%%M%%S.log'"))));
+
+    if (SRF_IS_FIRSTCALL())
+    {
+        MemoryContext oldcontext;
+        TupleDesc tupdesc;
+
+        funcctx=SRF_FIRSTCALL_INIT();
+        oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+        fctx = palloc(sizeof(directory_fctx));
+        if (is_absolute_path(Log_directory))
+            fctx->location = Log_directory;
+        else
+        {
+            fctx->location = palloc(strlen(DataDir) + strlen(Log_directory) +2);
+            sprintf(fctx->location, "%s/%s", DataDir, Log_directory);
+        }
+        tupdesc = CreateTemplateTupleDesc(2, false);
+        TupleDescInitEntry(tupdesc, (AttrNumber) 1, "starttime",
+                           TIMESTAMPOID, -1, 0);
+        TupleDescInitEntry(tupdesc, (AttrNumber) 2, "filename",
+                           TEXTOID, -1, 0);
+
+        funcctx->attinmeta = TupleDescGetAttInMetadata(tupdesc);
+
+        fctx->dirdesc = AllocateDir(fctx->location);
+
+        if (!fctx->dirdesc)
+            ereport(ERROR,
+                    (errcode_for_file_access(),
+                     errmsg("%s is not browsable: %m", fctx->location)));
+
+        funcctx->user_fctx = fctx;
+        MemoryContextSwitchTo(oldcontext);
+    }
+
+    funcctx=SRF_PERCALL_SETUP();
+    fctx = (directory_fctx*) funcctx->user_fctx;
+
+    if (!fctx->dirdesc)  /* not a readable directory  */
+        SRF_RETURN_DONE(funcctx);
+
+    while ((de = readdir(fctx->dirdesc)) != NULL)
+    {
+        char *values[2];
+        HeapTuple tuple;
+
+        char     *field[MAXDATEFIELDS];
+        char    lowstr[MAXDATELEN + 1];
+        int        dtype;
+        int        nf, ftype[MAXDATEFIELDS];
+        fsec_t    fsec;
+        int        tz = 0;
+        struct    pg_tm date;
+
+        /*
+         * Default format:
+         *        postgresql-YYYY-MM-DD_HHMMSS.log
+         */
+        if (strlen(de->d_name) != 32
+            || memcmp(de->d_name, "postgresql-", 11)
+            || de->d_name[21] != '_'
+            || strcmp(de->d_name + 28, ".log"))
+              continue;
+
+        values[1] = palloc(strlen(fctx->location) + strlen(de->d_name) + 2);
+        sprintf(values[1], "%s/%s", fctx->location, de->d_name);
+
+        values[0] = de->d_name + 11;       /* timestamp */
+        values[0][17] = 0;
+
+                    /* parse and decode expected timestamp */
+        if (ParseDateTime(values[0], lowstr, sizeof(lowstr), field, ftype, MAXDATEFIELDS, &nf))
+            continue;
+
+        if (DecodeDateTime(field, ftype, nf, &dtype, &date, &fsec, &tz))
+            continue;
+
+        /* Seems the format fits the expected format; feed it into the tuple */
+
+        tuple = BuildTupleFromCStrings(funcctx->attinmeta, values);
+
+        SRF_RETURN_NEXT(funcctx, HeapTupleGetDatum(tuple));
+    }
+
+    FreeDir(fctx->dirdesc);
+    SRF_RETURN_DONE(funcctx);
+}
+

 Datum
 pg_terminate_backend(PG_FUNCTION_ARGS)
 {
     PG_RETURN_INT32(pg_signal_backend(PG_GETARG_INT32(0), SIGTERM));
 }
-#endif


 /* Function to find out which databases make use of a tablespace */
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.369
diff -u -r1.369 pg_proc.h
--- src/include/catalog/pg_proc.h    20 Jun 2005 10:29:37 -0000    1.369
+++ src/include/catalog/pg_proc.h    21 Jun 2005 09:25:01 -0000
@@ -3029,6 +3029,41 @@
 DESCR("Prepare for taking an online backup");
 DATA(insert OID = 2173 ( pg_stop_backup            PGNSP PGUID 12 f f t f v 0 25 "" _null_ _null_ _null_
pg_stop_backup- _null_ )); 
 DESCR("Finish taking an online backup");
+DATA(insert OID = 2284 ( pg_reload_conf         PGNSP PGUID 12 f f t f v 0 23 "" _null_ _null_ _null_ pg_reload_conf -
_null_)); 
+DESCR("Reloads configuration files");
+
+DATA(insert OID = 2285 ( pg_tablespace_size        PGNSP PGUID 12 f f t f v 1 20 "26" _null_ _null_ _null_
pg_tablespace_size- _null_ )); 
+DESCR("Calculate total disk space usage for tablespace");
+DATA(insert OID = 2286 ( pg_database_size        PGNSP PGUID 12 f f t f v 1 20 "26" _null_ _null_ _null_
pg_database_size- _null_ )); 
+DESCR("Calculate total disk space usage for database");
+DATA(insert OID = 2287 ( pg_relation_size        PGNSP PGUID 12 f f t f v 1 20 "26" _null_ _null_ _null_
pg_relation_size- _null_ )); 
+DESCR("Calculate total disk space usage for relation");
+DATA(insert OID = 2288 ( pg_size_pretty          PGNSP PGUID 12 f f t f v 1 25 "20" _null_ _null_ _null_
pg_size_pretty- _null_ )); 
+DESCR("Convert a long int to a human readable text using size units");
+
+DATA(insert OID = 1232 ( pg_logfile_rotate        PGNSP PGUID 12 f f t f v 0 23 "" _null_ _null_ _null_
pg_logfile_rotate- _null_ )); 
+DESCR("rotate log file");
+DATA(insert OID = 1233 ( pg_logdir_ls            PGNSP PGUID 12 f f t t v 0 2249 "" _null_ _null_ _null_ pg_logdir_ls
-_null_ )); 
+DESCR("list all available log files");
+
+
+DATA(insert OID = 2562 ( pg_file_stat        PGNSP PGUID 12 f f t f v 1 2249 "25" _null_ _null_ _null_ pg_file_stat -
_null_)); 
+DESCR("retrieve file stats");
+DATA(insert OID = 2563 ( pg_file_length        PGNSP PGUID 14 f f t f v 1 20 "25" _null_ _null_ _null_ "SELECT len
FROMpg_file_stat($1) AS s(len int8, c timestamp, a timestamp, m timestamp, i bool)" - _null_ )); 
+DESCR("returns length of a file");
+DATA(insert OID = 2564 ( pg_file_read        PGNSP PGUID 12 f f t f v 3 25 "25 20 20" _null_ _null_ _null_
pg_file_read- _null_ )); 
+DESCR("reads text from a file");
+DATA(insert OID = 2565 ( pg_file_write      PGNSP PGUID 12 f f t f v 3 20 "25 25 16" _null_ _null_ _null_
pg_file_write- _null_ )); 
+DESCR("writes text to a file");
+DATA(insert OID = 2566 ( pg_file_rename        PGNSP PGUID 12 f f f f v 3 16 "25 25 25" _null_ _null_ _null_
pg_file_rename- _null_ )); 
+DESCR("renames a file and its backup file");
+DATA(insert OID = 2567 ( pg_file_rename        PGNSP PGUID 12 f f f t v 2 16 "25 25" _null_ _null_ _null_
pg_file_rename- _null_ )); 
+DESCR("renames a file");
+DATA(insert OID = 2568 ( pg_file_unlink        PGNSP PGUID 12 f f t f v 1 16 "25" _null_ _null_ _null_ pg_file_unlink
-_null_ )); 
+DESCR("unlinks a file");
+DATA(insert OID = 2569 ( pg_dir_ls            PGNSP PGUID 12 f f t t v 2 25 "25 16" _null_ _null_ _null_ pg_dir_ls -
_null_)); 
+DESCR("lists all filenames from a directory");
+


 /* Aggregates (moved here from pg_aggregate for 7.3) */
Index: src/include/storage/pmsignal.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/storage/pmsignal.h,v
retrieving revision 1.11
diff -u -r1.11 pmsignal.h
--- src/include/storage/pmsignal.h    31 Dec 2004 22:03:42 -0000    1.11
+++ src/include/storage/pmsignal.h    21 Jun 2005 09:25:02 -0000
@@ -25,7 +25,7 @@
     PMSIGNAL_PASSWORD_CHANGE,    /* pg_pwd file has changed */
     PMSIGNAL_WAKEN_CHILDREN,    /* send a SIGUSR1 signal to all backends */
     PMSIGNAL_WAKEN_ARCHIVER,    /* send a NOTIFY signal to xlog archiver */
-
+    PMSIGNAL_ROTATE_LOGFILE,    /* send SIGUSR1 to syslogger to rotate logfile */
     NUM_PMSIGNALS                /* Must be last value of enum! */
 } PMSignalReason;

Index: src/include/utils/builtins.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/utils/builtins.h,v
retrieving revision 1.258
diff -u -r1.258 builtins.h
--- src/include/utils/builtins.h    17 Jun 2005 22:32:50 -0000    1.258
+++ src/include/utils/builtins.h    21 Jun 2005 09:25:04 -0000
@@ -357,12 +357,29 @@
 extern Datum float84gt(PG_FUNCTION_ARGS);
 extern Datum float84ge(PG_FUNCTION_ARGS);

+/* dbsize.c */
+extern Datum pg_tablespace_size(PG_FUNCTION_ARGS);
+extern Datum pg_database_size(PG_FUNCTION_ARGS);
+extern Datum pg_relation_size(PG_FUNCTION_ARGS);
+extern Datum pg_size_pretty(PG_FUNCTION_ARGS);
+
+/* genfile.c */
+extern Datum pg_file_stat(PG_FUNCTION_ARGS);
+extern Datum pg_file_read(PG_FUNCTION_ARGS);
+extern Datum pg_file_write(PG_FUNCTION_ARGS);
+extern Datum pg_file_rename(PG_FUNCTION_ARGS);
+extern Datum pg_file_unlink(PG_FUNCTION_ARGS);
+extern Datum pg_dir_ls(PG_FUNCTION_ARGS);
+
 /* misc.c */
 extern Datum nullvalue(PG_FUNCTION_ARGS);
 extern Datum nonnullvalue(PG_FUNCTION_ARGS);
 extern Datum current_database(PG_FUNCTION_ARGS);
 extern Datum pg_cancel_backend(PG_FUNCTION_ARGS);
+extern Datum pg_reload_conf(PG_FUNCTION_ARGS);
 extern Datum pg_tablespace_databases(PG_FUNCTION_ARGS);
+extern Datum pg_logfile_rotate(PG_FUNCTION_ARGS);
+extern Datum pg_logdir_ls(PG_FUNCTION_ARGS);

 /* not_in.c */
 extern Datum int4notin(PG_FUNCTION_ARGS);
/*
 * dbsize.c
 * object size functions
 *
 * Copyright (c) 2002-2005, PostgreSQL Global Development Group
 *
 * IDENTIFICATION
 *      $PostgreSQL: pgsql/contrib/dbsize/dbsize.c,v 1.17 2005/05/27 00:57:48 neilc Exp $
 *
 */

#include "postgres.h"

#include <sys/types.h>
#include <sys/stat.h>

#include "access/heapam.h"
#include "catalog/namespace.h"
#include "catalog/pg_tablespace.h"
#include "commands/dbcommands.h"
#include "miscadmin.h"
#include "storage/fd.h"
#include "utils/builtins.h"
#include "utils/syscache.h"


/* Return physical size of directory contents, or 0 if dir doesn't exist */
static int64
db_dir_size(const char *path)
{
    int64        dirsize = 0;
    struct dirent *direntry;
    DIR         *dirdesc;
    char filename[MAXPGPATH];

    dirdesc = AllocateDir(path);

    if (!dirdesc)
        return 0;

    while ((direntry = readdir(dirdesc)) != NULL)
    {
        struct stat fst;

        if (strcmp(direntry->d_name, ".") == 0 ||
            strcmp(direntry->d_name, "..") == 0)
            continue;

        snprintf(filename, MAXPGPATH, "%s/%s", path, direntry->d_name);

        if (stat(filename, &fst) < 0)
            ereport(ERROR,
                    (errcode_for_file_access(),
                     errmsg("could not stat \"%s\": %m", filename)));
        dirsize += fst.st_size;
    }

    FreeDir(dirdesc);
    return dirsize;
}


static int64
calculate_database_size(Oid dbOid)
{
    int64        totalsize = 0;
    DIR         *dirdesc;
    struct dirent *direntry;
    char pathname[MAXPGPATH];

    /* Shared storage in pg_global is not counted */

    /* Include pg_default storage */
    snprintf(pathname, MAXPGPATH, "%s/base/%u", DataDir, dbOid);
    totalsize += db_dir_size(pathname);

    /* Scan the non-default tablespaces */
    snprintf(pathname, MAXPGPATH, "%s/pg_tblspc", DataDir);
    dirdesc = AllocateDir(pathname);
    if (!dirdesc)
        ereport(ERROR,
                (errcode_for_file_access(),
                 errmsg("could not open tablespace directory \"%s\": %m",
                        pathname)));

    while ((direntry = readdir(dirdesc)) != NULL)
    {
        if (strcmp(direntry->d_name, ".") == 0 ||
            strcmp(direntry->d_name, "..") == 0)
            continue;

        snprintf(pathname, MAXPGPATH, "%s/pg_tblspc/%s/%u",
                 DataDir, direntry->d_name, dbOid);
        totalsize += db_dir_size(pathname);
    }

    FreeDir(dirdesc);

    /* Complain if we found no trace of the DB at all */
    if (!totalsize)
        ereport(ERROR,
                (ERRCODE_UNDEFINED_DATABASE,
                 errmsg("database with OID %u does not exist", dbOid)));

    return totalsize;
}

/*
 * calculate total size of tablespace
 */
Datum
pg_tablespace_size(PG_FUNCTION_ARGS)
{
    Oid tblspcOid = PG_GETARG_OID(0);
    char tblspcPath[MAXPGPATH];
    char pathname[MAXPGPATH];
    int64        totalsize=0;
    DIR         *dirdesc;
    struct dirent *direntry;

    if (tblspcOid == DEFAULTTABLESPACE_OID)
        snprintf(tblspcPath, MAXPGPATH, "%s/base", DataDir);
    else if (tblspcOid == GLOBALTABLESPACE_OID)
        snprintf(tblspcPath, MAXPGPATH, "%s/global", DataDir);
    else
        snprintf(tblspcPath, MAXPGPATH, "%s/pg_tblspc/%u", DataDir, tblspcOid);

    dirdesc = AllocateDir(tblspcPath);

    if (!dirdesc)
        ereport(ERROR,
                (errcode_for_file_access(),
                 errmsg("could not open tablespace directory \"%s\": %m",
                        tblspcPath)));

    while ((direntry = readdir(dirdesc)) != NULL)
    {
        struct stat fst;

        if (strcmp(direntry->d_name, ".") == 0 ||
            strcmp(direntry->d_name, "..") == 0)
            continue;

        snprintf(pathname, MAXPGPATH, "%s/%s", tblspcPath, direntry->d_name);

        if (stat(pathname, &fst) < 0)
            ereport(ERROR,
                    (errcode_for_file_access(),
                     errmsg("could not stat \"%s\": %m", pathname)));
        totalsize += fst.st_size;

        if (fst.st_mode & S_IFDIR)
            totalsize += db_dir_size(pathname);
    }

    FreeDir(dirdesc);

    PG_RETURN_INT64(totalsize);
}


/*
 * calculate size of database in all tablespaces
 */
Datum
pg_database_size(PG_FUNCTION_ARGS)
{
    Oid dbOid = PG_GETARG_OID(0);

    PG_RETURN_INT64(calculate_database_size(dbOid));
}


/* Calculate relation size given tablespace and relation OIDs */
static int64
calculate_relation_size(Oid tblspcOid, Oid relnodeOid)
{
    int64        totalsize=0;
    unsigned int segcount=0;
    char dirpath[MAXPGPATH];
    char pathname[MAXPGPATH];

    if (!tblspcOid)
        tblspcOid = MyDatabaseTableSpace;

    if (tblspcOid == DEFAULTTABLESPACE_OID)
        snprintf(dirpath, MAXPGPATH, "%s/base/%u", DataDir, MyDatabaseId);
    else if (tblspcOid == GLOBALTABLESPACE_OID)
        snprintf(dirpath, MAXPGPATH, "%s/global", DataDir);
    else
        snprintf(dirpath, MAXPGPATH, "%s/pg_tblspc/%u/%u",
                 DataDir, tblspcOid, MyDatabaseId);

    for (segcount = 0 ;; segcount++)
    {
        struct stat fst;

        if (segcount == 0)
            snprintf(pathname, MAXPGPATH, "%s/%u",
                     dirpath, relnodeOid);
        else
            snprintf(pathname, MAXPGPATH, "%s/%u.%u",
                     dirpath, relnodeOid, segcount);

        if (stat(pathname, &fst) < 0)
        {
            if (errno == ENOENT)
                break;
            else
                ereport(ERROR,
                        (errcode_for_file_access(),
                         errmsg("could not stat \"%s\": %m", pathname)));
        }
        totalsize += fst.st_size;
    }

    return totalsize;
}

/*
 * calculate size of relation
 */
Datum
pg_relation_size(PG_FUNCTION_ARGS)
{
    Oid         relOid=PG_GETARG_OID(0);
    HeapTuple   tuple;
    Form_pg_class pg_class;
    Oid            relnodeOid;
    Oid         tblspcOid;

    tuple = SearchSysCache(RELOID,
                           ObjectIdGetDatum(relOid),
                           0, 0, 0);
    if (!HeapTupleIsValid(tuple))
        ereport(ERROR,
                (ERRCODE_UNDEFINED_TABLE,
                 errmsg("relation with OID %u does not exist", relOid)));

    pg_class = (Form_pg_class) GETSTRUCT(tuple);
    relnodeOid = pg_class->relfilenode;
    tblspcOid = pg_class->reltablespace;

    ReleaseSysCache(tuple);

    PG_RETURN_INT64(calculate_relation_size(tblspcOid, relnodeOid));
}


/*
 * formatting with size units
 */
Datum
pg_size_pretty(PG_FUNCTION_ARGS)
{
    int64 size=PG_GETARG_INT64(0);
    char *result=palloc(50+VARHDRSZ);
    int64 limit = 10*1024;
    int64 mult=1;

    if (size < limit*mult)
        snprintf(VARDATA(result), 50, INT64_FORMAT" bytes",
                 size);
    else
    {
        mult *= 1024;
        if (size < limit*mult)
             snprintf(VARDATA(result), 50, INT64_FORMAT " kB",
                      (size+mult/2) / mult);
        else
        {
            mult *= 1024;
            if (size < limit*mult)
                snprintf(VARDATA(result), 50, INT64_FORMAT " MB",
                         (size+mult/2) / mult);
            else
            {
                mult *= 1024;
                if (size < limit*mult)
                    snprintf(VARDATA(result), 50, INT64_FORMAT " GB",
                             (size+mult/2) / mult);
                else
                {
                    mult *= 1024;
                    snprintf(VARDATA(result), 50, INT64_FORMAT " TB",
                             (size+mult/2) / mult);
                }
            }
        }
    }

    VARATT_SIZEP(result) = strlen(VARDATA(result)) + VARHDRSZ;

    PG_RETURN_TEXT_P(result);
}
/*-------------------------------------------------------------------------
 *
 * genfile.c
 *
 *
 * Copyright (c) 2004, PostgreSQL Global Development Group
 *
 * Author: Andreas Pflug <pgadmin@pse-consulting.de>
 *
 * IDENTIFICATION
 *      $PostgreSQL: $
 *
 *-------------------------------------------------------------------------
 */
#include "postgres.h"

#include <sys/file.h>
#include <sys/stat.h>
#include <unistd.h>
#include <dirent.h>

#include "utils/builtins.h"
#include "miscadmin.h"
#include "storage/fd.h"
#include "catalog/pg_type.h"
#include "funcapi.h"

#ifdef WIN32

/* we don't want *nix emulating retry stuff here, but the native behaviour */

#ifdef rename
#undef rename
#endif

#ifdef unlink
#undef unlink
#endif

#endif

extern  char *Log_directory;


typedef struct
{
    char *location;
    DIR *dirdesc;
} directory_fctx;

/*-----------------------
 * some helper functions
 */

/*
 * Return an absolute path. Argument may be absolute or
 * relative to the DataDir.
 */
static char *absClusterPath(text *arg, bool logAllowed)
{
    char *filename;
    int len=VARSIZE(arg) - VARHDRSZ;
    int dlen = strlen(DataDir);

    filename = palloc(len+1);
    memcpy(filename, VARDATA(arg), len);
    filename[len] = 0;

    if (strstr(filename, "..") != NULL)
      ereport(ERROR,
              (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
               (errmsg("No .. allowed in filenames"))));

    if (is_absolute_path(filename))
    {
        if (logAllowed && !strncmp(filename, Log_directory, strlen(Log_directory)))
            return filename;
        if (strncmp(filename, DataDir, dlen))
            ereport(ERROR,
                    (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
                     (errmsg("Absolute path not allowed"))));

        return filename;
    }
    else
    {
        char *absname = palloc(dlen+len+2);
        sprintf(absname, "%s/%s", DataDir, filename);
        pfree(filename);
        return absname;
    }
}


/*
 * check for superuser, bark if not.
 */
static void
requireSuperuser(void)
{
    if (!superuser())
        ereport(ERROR,
                (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
                 (errmsg("only superuser may access generic file functions"))));
}



/* ------------------------------------
 * generic file handling functions
 */


Datum pg_file_stat(PG_FUNCTION_ARGS)
{
    AttInMetadata *attinmeta = NULL;
    char *    filename = absClusterPath(PG_GETARG_TEXT_P(0), true);
    struct stat fst;
    int64 length;
    char lenbuf[30];
    char cbuf[30], abuf[30], mbuf[30], dbuf[]="f";
    char *values[5] =
       { lenbuf, cbuf, abuf, mbuf, dbuf };

    pg_time_t timestamp;
    HeapTuple tuple;

    if (attinmeta == NULL)
    {
        TupleDesc tupdesc = CreateTemplateTupleDesc(5, false);

        TupleDescInitEntry(tupdesc, (AttrNumber) 1, "length",
                           INT8OID, -1, 0);
        TupleDescInitEntry(tupdesc, (AttrNumber) 2, "ctime",
                           TIMESTAMPOID, -1, 0);
        TupleDescInitEntry(tupdesc, (AttrNumber) 3, "atime",
                           TIMESTAMPOID, -1, 0);
        TupleDescInitEntry(tupdesc, (AttrNumber) 4, "mtime",
                           TIMESTAMPOID, -1, 0);
        TupleDescInitEntry(tupdesc, (AttrNumber) 5, "isdir",
                           BOOLOID, -1, 0);

        attinmeta = TupleDescGetAttInMetadata(tupdesc);
    }

    if (stat(filename, &fst) < 0)
    {
        ereport(WARNING,
                (errcode_for_file_access(),
                 errmsg("could not stat file %s: %m", filename)));

        strcpy(lenbuf, "-1");
        strcpy(cbuf, "NULL");
        strcpy(abuf, "NULL");
        strcpy(mbuf, "NULL");
    }
    else
    {
        length = fst.st_size;
        snprintf(lenbuf, 30, INT64_FORMAT, length);

        timestamp = fst.st_ctime;
        pg_strftime(cbuf, 30, "%F %T", pg_localtime(×tamp, global_timezone));

        timestamp = fst.st_atime;
        pg_strftime(abuf, 30, "%F %T", pg_localtime(×tamp, global_timezone));

        timestamp = fst.st_mtime;
        pg_strftime(mbuf, 30, "%F %T", pg_localtime(×tamp, global_timezone));

        if (fst.st_mode & S_IFDIR)
          dbuf[0] = 't';
    }
    tuple = BuildTupleFromCStrings(attinmeta, values);

    PG_RETURN_DATUM(HeapTupleGetDatum(tuple));
}


Datum pg_file_read(PG_FUNCTION_ARGS)
{
    size_t size;
    char *buf=0;
    size_t nbytes;
    int64 pos;
    FILE *f;
    char *filename;

    requireSuperuser();

    filename = absClusterPath(PG_GETARG_TEXT_P(0), true);
    pos = PG_GETARG_INT64(1);
    size = PG_GETARG_INT64(2);

    f = fopen(filename, "rb");
    if (!f)
    {
        ereport(ERROR,
                (errcode_for_file_access(),
                 errmsg("could not open file %s for reading: %m", filename)));
        PG_RETURN_NULL();
    }

    if (pos >= 0)
        fseek(f, pos, SEEK_SET);
    else
        fseek(f, pos, SEEK_END);


    buf = palloc(size + VARHDRSZ);

    nbytes = fread(VARDATA(buf), 1, size, f);
    if (nbytes < 0)
    {
        ereport(ERROR,
                (errcode_for_file_access(),
                 errmsg("could not read file %s: %m", filename)));
        PG_RETURN_NULL();
    }
    VARATT_SIZEP(buf) = nbytes + VARHDRSZ;
    fclose(f);

    PG_RETURN_TEXT_P(buf);
}


Datum pg_file_write(PG_FUNCTION_ARGS)
{
    FILE *f;
    char *filename;
    text *data;
    int64 count = 0;

    requireSuperuser();

    filename = absClusterPath(PG_GETARG_TEXT_P(0), false);
    data = PG_GETARG_TEXT_P(1);

    if (PG_ARGISNULL(2) || !PG_GETARG_BOOL(2))
    {
        struct stat fst;
        if (stat(filename, &fst) >= 0)
            ereport(ERROR,
                    (ERRCODE_DUPLICATE_FILE,
                     errmsg("file %s exists", filename)));

        f = fopen(filename, "wb");
    }
    else
        f = fopen(filename, "ab");

    if (!f)
    {
        ereport(ERROR,
                (errcode_for_file_access(),
                 errmsg("could open file %s for writing: %m", filename)));
    }

    if (VARSIZE(data) != 0)
    {
        count = fwrite(VARDATA(data), 1, VARSIZE(data) - VARHDRSZ, f);

        if (count != VARSIZE(data) - VARHDRSZ)
            ereport(ERROR,
                    (errcode_for_file_access(),
                     errmsg("error writing file %s: %m", filename)));
    }
    fclose(f);

    PG_RETURN_INT64(count);
}


Datum pg_file_rename(PG_FUNCTION_ARGS)
{
    char *fn1, *fn2, *fn3;
    int rc;

    requireSuperuser();

    if (PG_ARGISNULL(0) || PG_ARGISNULL(1))
        PG_RETURN_NULL();

    fn1=absClusterPath(PG_GETARG_TEXT_P(0), false);
    fn2=absClusterPath(PG_GETARG_TEXT_P(1), false);
    if (PG_ARGISNULL(2))
        fn3=0;
    else
        fn3=absClusterPath(PG_GETARG_TEXT_P(2), false);

    if (access(fn1, W_OK) < 0)
    {
        ereport(WARNING,
                (errcode_for_file_access(),
                 errmsg("file %s not accessible: %m", fn1)));

        PG_RETURN_BOOL(false);
    }

    if (fn3 && access(fn2, W_OK) < 0)
    {
        ereport(WARNING,
                (errcode_for_file_access(),
                 errmsg("file %s not accessible: %m", fn2)));

        PG_RETURN_BOOL(false);
    }


    rc = access(fn3 ? fn3 : fn2, 2);
    if (rc >= 0 || errno != ENOENT)
    {
        ereport(ERROR,
                (ERRCODE_DUPLICATE_FILE,
                 errmsg("cannot rename to target file %s", fn3 ? fn3 : fn2)));
    }

    if (fn3)
    {
        if (rename(fn2, fn3) != 0)
        {
            ereport(ERROR,
                    (errcode_for_file_access(),
                     errmsg("could not rename %s to %s: %m", fn2, fn3)));
        }
        if (rename(fn1, fn2) != 0)
        {
            ereport(WARNING,
                    (errcode_for_file_access(),
                     errmsg("could not rename %s to %s: %m", fn1, fn2)));

            if (rename(fn3, fn2) != 0)
            {
                ereport(ERROR,
                        (errcode_for_file_access(),
                         errmsg("could not rename %s back to %s: %m", fn3, fn2)));
            }
            else
            {
                ereport(ERROR,
                        (ERRCODE_UNDEFINED_FILE,
                         errmsg("renaming %s to %s was reverted", fn2, fn3)));

            }
        }
    }
    else if (rename(fn1, fn2) != 0)
    {
            ereport(WARNING,
                    (errcode_for_file_access(),
                     errmsg("renaming %s to %s %m", fn1, fn2)));
        ereport(ERROR,
                (errcode_for_file_access(),
                 errmsg("could not rename %s to %s: %m", fn1, fn2)));
    }

    PG_RETURN_BOOL(true);
}


Datum pg_file_unlink(PG_FUNCTION_ARGS)
{
    char *filename;

    requireSuperuser();

    filename = absClusterPath(PG_GETARG_TEXT_P(0), false);

    if (access(filename, W_OK) < 0)
    {
        if (errno == ENOENT)
            PG_RETURN_BOOL(false);
        else
            ereport(ERROR,
                    (errcode_for_file_access(),
                     errmsg("file %s not accessible: %m", filename)));

    }

    if (unlink(filename) < 0)
    {
        ereport(WARNING,
                (errcode_for_file_access(),
                 errmsg("could not unlink file %s: %m", filename)));

        PG_RETURN_BOOL(false);
    }
    PG_RETURN_BOOL(true);
}


Datum pg_dir_ls(PG_FUNCTION_ARGS)
{
    FuncCallContext *funcctx;
    struct dirent *de;
    directory_fctx *fctx;

    requireSuperuser();

    if (SRF_IS_FIRSTCALL())
    {
        MemoryContext oldcontext;

        funcctx=SRF_FIRSTCALL_INIT();
        oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);

        fctx = palloc(sizeof(directory_fctx));
        fctx->location = absClusterPath(PG_GETARG_TEXT_P(0), false);

        fctx->dirdesc = AllocateDir(fctx->location);

        if (!fctx->dirdesc)
            ereport(ERROR,
                    (errcode_for_file_access(),
                     errmsg("%s is not browsable: %m", fctx->location)));

        if (PG_ARGISNULL(1) || !PG_GETARG_BOOL(1))
        {
            pfree(fctx->location);
            fctx->location = 0;
        }
        funcctx->user_fctx = fctx;
        MemoryContextSwitchTo(oldcontext);
    }

    funcctx=SRF_PERCALL_SETUP();
    fctx = (directory_fctx*) funcctx->user_fctx;

    if (!fctx->dirdesc)  /* not a readable directory  */
        SRF_RETURN_DONE(funcctx);

    while ((de = readdir(fctx->dirdesc)) != NULL)
    {
        char *name;
        text *result;
        int len;
        if (!strcmp(de->d_name, ".") || !strcmp(de->d_name, ".."))
            continue;
        if (fctx->location)
        {
            char *path=palloc(strlen(fctx->location) + strlen(de->d_name) +2);
            sprintf(path, "%s/%s", fctx->location, de->d_name);

            name = path;
        }
        else
            name = de->d_name;


        len = strlen(name);
        result = palloc(len + VARHDRSZ);
        VARATT_SIZEP(result) = len + VARHDRSZ;
        memcpy(VARDATA(result), name, len);

        SRF_RETURN_NEXT(funcctx, PointerGetDatum(result));
    }

    FreeDir(fctx->dirdesc);
    SRF_RETURN_DONE(funcctx);
}