Admin functions contrib - Mailing list pgsql-patches

These files add administrative functions to pgsql 7.5. All are used by
pgAdmin3 or will be used in the near future if available. This is meant
as contrib module, whilst IMHO all these functions should be integrated
into the backend.

Included are functions to

log file access
These where previously posted together with the logger subprocess patch
and might get committed to the backend code.

misc.
send SIGHUP to postmaster

generic file access functions
These are more restrictive than the previously posted: Write access is
necessary for files to rename and unlink, and paths are restricted to
PGDATA and the logdir.

size functions
These are 7.5 replacements for dbsize.

Regards,
Andreas
subdir = contrib/admin
top_builddir = ../..
include $(top_builddir)/src/Makefile.global

MODULE_big = admin
DATA_built = admin.sql
DOCS = README.admin
OBJS = size.o genfile.o misc.o
include $(top_srcdir)/contrib/contrib-global.mk
/* **********************************
 * Administrative functions
 * ********************************* */

/* database object size functions (admin.c) */

CREATE FUNCTION pg_tablespace_size(oid) RETURNS bigint
    AS 'MODULE_PATHNAME', 'pg_tablespace_size'
    LANGUAGE C STABLE STRICT;

CREATE FUNCTION pg_database_size(oid) RETURNS bigint
    AS 'MODULE_PATHNAME', 'pg_database_size'
    LANGUAGE C STABLE STRICT;

CREATE FUNCTION pg_relation_size(oid) RETURNS bigint
    AS 'MODULE_PATHNAME', 'pg_relation_size'
    LANGUAGE C STABLE STRICT;

CREATE FUNCTION pg_size_pretty(bigint) RETURNS text
    AS 'MODULE_PATHNAME', 'pg_size_pretty'
    LANGUAGE C STABLE STRICT;


/* generic file access functions (genfile.c) */

CREATE FUNCTION pg_file_stat(text) RETURNS record
   AS 'MODULE_PATHNAME', 'pg_file_stat'
    LANGUAGE C VOLATILE STRICT;

CREATE FUNCTION pg_file_length(text) RETURNS bigint
   AS 'SELECT len FROM pg_file_stat($1) AS s(len int8, c timestamp, a timestamp, m timestamp, i bool)'
    LANGUAGE SQL VOLATILE STRICT;

CREATE FUNCTION pg_file_read(text, bigint, bigint) RETURNS text
   AS 'MODULE_PATHNAME', 'pg_file_read'
    LANGUAGE C VOLATILE STRICT;

CREATE FUNCTION pg_file_write(text, text, bool) RETURNS bigint
   AS 'MODULE_PATHNAME', 'pg_file_write'
    LANGUAGE C VOLATILE STRICT;

CREATE FUNCTION pg_file_rename(text, text, text) RETURNS bool
   AS 'MODULE_PATHNAME', 'pg_file_rename'
    LANGUAGE C VOLATILE STRICT;

CREATE FUNCTION pg_file_unlink(text) RETURNS bool
   AS 'MODULE_PATHNAME', 'pg_file_unlink'
    LANGUAGE C VOLATILE STRICT;

CREATE FUNCTION pg_file_rename(text, text) RETURNS bool
   AS 'SELECT pg_file_rename($1, $2, NULL); '
    LANGUAGE SQL VOLATILE STRICT;

CREATE FUNCTION pg_dir_ls(text, bool) RETURNS setof text
   AS 'MODULE_PATHNAME', 'pg_dir_ls'
    LANGUAGE C VOLATILE STRICT;


/* Miscellaneous functions (misc.c) */

CREATE FUNCTION pg_reload_conf() RETURNS int4
   AS 'MODULE_PATHNAME', 'pg_reload_conf'
    LANGUAGE C STABLE STRICT;

CREATE FUNCTION pg_logfile_rotate() RETURNS bool
   AS 'MODULE_PATHNAME', 'pg_logfile_rotate'
    LANGUAGE C STABLE STRICT;

CREATE FUNCTION pg_logdir_ls() RETURNS setof record
   AS 'MODULE_PATHNAME', 'pg_logdir_ls'
    LANGUAGE C VOLATILE STRICT;

CREATE VIEW pg_logdir_ls AS
    SELECT *
    FROM pg_logdir_ls() AS A
    (filetime timestamp, pid int4, filename text);
/*-------------------------------------------------------------------------
 *
 * 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 <unistd.h>
#include <dirent.h>

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



Datum pg_file_stat(PG_FUNCTION_ARGS);
Datum pg_file_read(PG_FUNCTION_ARGS);
Datum pg_file_write(PG_FUNCTION_ARGS);
Datum pg_file_rename(PG_FUNCTION_ARGS);
Datum pg_file_unlink(PG_FUNCTION_ARGS);
Datum pg_dir_ls(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(pg_file_stat);
PG_FUNCTION_INFO_V1(pg_file_read);
PG_FUNCTION_INFO_V1(pg_file_write);
PG_FUNCTION_INFO_V1(pg_file_rename);
PG_FUNCTION_INFO_V1(pg_file_unlink);
PG_FUNCTION_INFO_V1(pg_dir_ls);

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

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

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

        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, "r");
    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, "w");
    }
    else
        f = fopen(filename, "a");

    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))
            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();

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

            }
        }
    }
    if (rename(fn1, fn2) != 0)
    {
        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);
}


/*
 * admin.c
 * miscellaneous administrative functions
 *
 * Copyright (c) 2004, PostgreSQL Global Development Group
 *
 * Author: Andreas Pflug <pgadmin@pse-consulting.de>
 *
 * IDENTIFICATION
 *      $PostgreSQL: $
 *
 */

#include "postgres.h"

#include <sys/file.h>
#include <signal.h>
#include <dirent.h>

#include "commands/dbcommands.h"
#include "miscadmin.h"
#include "storage/sinval.h"
#include "storage/fd.h"
#include "funcapi.h"
#include "catalog/pg_type.h"
#include "catalog/pg_tablespace.h"
#include "postmaster/syslogger.h"

Datum pg_reload_conf(PG_FUNCTION_ARGS);
Datum pg_logfile_rotate(PG_FUNCTION_ARGS);
Datum pg_logdir_ls(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(pg_reload_conf);
PG_FUNCTION_INFO_V1(pg_logfile_rotate);
PG_FUNCTION_INFO_V1(pg_logdir_ls);


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);
}


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


/*
 * logfile handling functions
 */


Datum pg_logfile_rotate(PG_FUNCTION_ARGS)
{
    if (!superuser())
        ereport(ERROR,
                (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
                 (errmsg("only superuser can issue a logfile rotation command"))));

    PG_RETURN_BOOL(LogFileRotate());
}


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 (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(3, false);
        TupleDescInitEntry(tupdesc, (AttrNumber) 1, "starttime",
                           TIMESTAMPOID, -1, 0);
        TupleDescInitEntry(tupdesc, (AttrNumber) 2, "pid",
                           INT4OID, -1, 0);
        TupleDescInitEntry(tupdesc, (AttrNumber) 3, "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[3];
        HeapTuple tuple;
        int prefixLen=strlen(Log_filename_prefix);

        char       *field[MAXDATEFIELDS];
        char        lowstr[MAXDATELEN + 1];
        int            dtype;
        int            nf, ftype[MAXDATEFIELDS];
        fsec_t        fsec;
        int            tz = 0;
        struct pg_tm date;
        int         i;

        /*
         * format as created in logfile_getname():
         *        prefix_YYYY-MM-DD_HHMMSS_PPPPP.log
         *   prefixLen   ^
         *                 prefixLen+17   ^
         *                       prefixLen+23   ^
         */

        if (strlen(de->d_name) != prefixLen + 27
            || memcmp(de->d_name, Log_filename_prefix, prefixLen)
            || de->d_name[prefixLen + 17] != '_'
            || strcmp(de->d_name + prefixLen + 23, ".log"))
              continue;

        values[2] = palloc(strlen(fctx->location) + strlen(de->d_name) + 2);
        sprintf(values[2], "%s/%s", fctx->location, de->d_name);

        values[0] = de->d_name + prefixLen;       /* timestamp */
        values[0][17] = 0;

        values[1] = de->d_name + prefixLen + 18;  /* pid */
        values[1][5] = 0;

        /* check if pid is purely numeric as expected */
        for (i = 0 ; i < 5 ; i++)
            if (!isdigit(values[0][i]))
                continue;

        /* parse and decode expected timestamp */
        if (ParseDateTime(values[0], 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);
}

/*
 * size.c
 * object size functions
 *
 * Copyright (c) 2004, PostgreSQL Global Development Group
 *
 * Author: Andreas Pflug <pgadmin@pse-consulting.de>
 *
 * IDENTIFICATION
 *      $PostgreSQL: $
 *
 */

#include "postgres.h"

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

#include "access/heapam.h"
#include "storage/fd.h"
#include "utils/syscache.h"
#include "catalog/pg_tablespace.h"
#include "miscadmin.h"

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

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

static int64
db_dir_size(char *path)
{
    int64 dirsize=0;
    struct dirent *direntry;
    DIR         *dirdesc;
    char filename[MAXPGPATH];

    dirdesc=AllocateDir(path);

    if (!dirdesc)
        return 0;

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

        if (!strcmp(direntry->d_name, ".") || !strcmp(direntry->d_name, ".."))
            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;
}

#include <signal.h>

/*
 * 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, 40, "%s/pg_tblspc/%u", DataDir, (unsigned)tblspcOid);

    dirdesc = AllocateDir(tblspcPath);

    if (!dirdesc)
        ereport(ERROR,
                (errcode_for_file_access(),
                 errmsg("No such tablespace OID: %u: %m", (unsigned)tblspcOid)));

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

        if (!strcmp(direntry->d_name, ".") || !strcmp(direntry->d_name, ".."))
            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 databases in all tablespaces
 */
Datum
pg_database_size(PG_FUNCTION_ARGS)
{
    Oid dbOid = PG_GETARG_OID(0);

    int64 totalsize=0;
    DIR         *dirdesc;
    struct dirent *direntry;
    char pathname[MAXPGPATH];

    snprintf(pathname, MAXPGPATH, "%s/global/%u", DataDir, (unsigned)dbOid);
    totalsize += db_dir_size(pathname);
    snprintf(pathname, MAXPGPATH, "%s/base/%u", DataDir, (unsigned)dbOid);
    totalsize += db_dir_size(pathname);

    snprintf(pathname, MAXPGPATH, "%s/pg_tblspc", DataDir);
    dirdesc = AllocateDir(pathname);

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

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

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

    FreeDir(dirdesc);

    if (!totalsize)
        ereport(ERROR,
                (ERRCODE_UNDEFINED_DATABASE,
                 errmsg("Database OID %u unknown.", (unsigned)dbOid)));

    PG_RETURN_INT64(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;
    char        relkind;
    int64        totalsize=0;
    unsigned int segcount=0;
    char dirpath[MAXPGPATH];
    char pathname[MAXPGPATH];


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

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

    ReleaseSysCache(tuple);

    switch(relkind)
    {
        case RELKIND_INDEX:
        case RELKIND_RELATION:
        case RELKIND_TOASTVALUE:
            break;
        default:
            ereport(ERROR,
                    (ERRCODE_WRONG_OBJECT_TYPE,
                     errmsg("Relation kind %d not supported", relkind)));
    }

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

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

        if (segcount == 0)
            snprintf(pathname, MAXPGPATH, "%s/%u", dirpath, (unsigned) relnodeOid);
        else
            snprintf(pathname, MAXPGPATH, "%s/%u.%u", dirpath, (unsigned) 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;
    }

    PG_RETURN_INT64(totalsize);
}


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);
}
Misc functions for administrative purposes

size.c:
-----------------------------
int8 pg_tablespace_size(oid)
int8 pg_database_size(oid)
int8 pg_relation_size(oid)
text pg_size_pretty(int8)


genfile.c (superuser only):
-----------------------------
record pg_file_stat(fname text)
int8 pg_file_length(fname text)
text pg_file_read(fname text, offs int8, len int8)
int8 pg_file_write(fname text, data text, append bool)
bool pg_file_rename(oldname text, newname text)
bool pg_file_rename(oldname text, newname text, archivname text)
bool pg_file_unlink(fname text)
setof text pg_dir_ls(dirname text, bool fullpath)


misc.c (superuser only):
-----------------------------
int4 pg_reload_conf()
bool pg_logfile_rotate()
setof record pg_logdir_ls()

VIEW pg_logdir_ls(filetime timestamp, pid int4, filename text)