Обсуждение: Admin functions contrib

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)

I talked to Tom about this today.  First, I want to apologize for
running you around in circles in this.  I don't think we are giving it
the attention it needs because of our schedule.  I also think the
functionality is drifting into the "new features" territory and this is
also part of the delay you are seeing.

I think you did a great thing by breaking the patch into two parts:  one
for logging, and the other for log reading and other stuff.  The logging
part is already in the patch queue.  As for the function below, I first
think the security issue brough up about them wasn't a valid concern
because as I stated someone could just load the plperl server-side
language and do anything to the OS.

In fact this might be the best solution for you.  Instead of trying to
code read/write/rename/unlink and other functions into the backend as
hardcoded, why not just have pgadmin load plperlu and as the super-user
you have access to that functionality, and much more, especially with
the new plperl in 7.5.  In fact, your goal of modifying the
postgresql.conf file is much more natural in perl than in the API you
supplied, and probably more reliable.

So, I suggest we get the logging code into the backend, and you can code
anything you want pgadmin to do in plperlu, and Win32 supports plperlu
too.  The big advantage is that you can improve the plperlu functions
with every release of pgadmin.

---------------------------------------------------------------------------

Andreas Pflug wrote:
> 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);




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

>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:
> I talked to Tom about this today.  First, I want to apologize for
> running you around in circles in this.  I don't think we are giving it
> the attention it needs because of our schedule.  I also think the
> functionality is drifting into the "new features" territory and this is
> also part of the delay you are seeing.
>
> I think you did a great thing by breaking the patch into two parts:  one
> for logging, and the other for log reading and other stuff.  The logging
> part is already in the patch queue.  As for the function below, I first
> think the security issue brough up about them wasn't a valid concern
> because as I stated someone could just load the plperl server-side
> language and do anything to the OS.
>
> In fact this might be the best solution for you.  Instead of trying to
> code read/write/rename/unlink and other functions into the backend as
> hardcoded, why not just have pgadmin load plperlu and as the super-user
> you have access to that functionality, and much more, especially with
> the new plperl in 7.5.  In fact, your goal of modifying the
> postgresql.conf file is much more natural in perl than in the API you
> supplied, and probably more reliable.
>
> So, I suggest we get the logging code into the backend, and you can code
> anything you want pgadmin to do in plperlu, and Win32 supports plperlu
> too.  The big advantage is that you can improve the plperlu functions
> with every release of pgadmin.

I do not agree on this. Administrative tools should require as few
additional backend packages as possible. What you're proposing is simply
a nightmare. Actually, IMHO all functions should be *backend* code, not
contrib code, even less arbitrary loadable language functions. Certainly
an external package relying on a loadable language is quite the
opposite, generating lots of support issues. It won't generate trust if
pgadmin documentation advises "install untrusted plperl to maintain your
machine".
Additionally, several of the functions are by no means new, but
replacements, did you notice pg_xxx_size? I posted this stuff as contrib
module to keep it off the feature freeze issue. If it still can't go
there, it must stay an external module which will be distributed as
pgadmin add-on. Reimplementing it as plperlu is crap.

Regards,
Andreas

Andreas Pflug wrote:
> > So, I suggest we get the logging code into the backend, and you can code
> > anything you want pgadmin to do in plperlu, and Win32 supports plperlu
> > too.  The big advantage is that you can improve the plperlu functions
> > with every release of pgadmin.
>
> I do not agree on this. Administrative tools should require as few
> additional backend packages as possible. What you're proposing is simply
> a nightmare. Actually, IMHO all functions should be *backend* code, not
> contrib code, even less arbitrary loadable language functions. Certainly
> an external package relying on a loadable language is quite the
> opposite, generating lots of support issues. It won't generate trust if
> pgadmin documentation advises "install untrusted plperl to maintain your
> machine".
> Additionally, several of the functions are by no means new, but
> replacements, did you notice pg_xxx_size? I posted this stuff as contrib
> module to keep it off the feature freeze issue. If it still can't go
> there, it must stay an external module which will be distributed as
> pgadmin add-on. Reimplementing it as plperlu is crap.

Well, if plperlu was always compiled by default we would be OK.  If it
isn't then you are right it would be a problem.  I see the --with-perl
option to configure so you are right it might be a problem.

Basically I think we are converging on an answer that we can't do any of
this for 7.5.  The scope has gone way beyond what we had at feature
freeze, and we can't even get it to work on Win32, which was one of the
big goals for easier Win32 administration, so I think we should just
shelve all this and get it right for 7.6 when we have time to address
this.

I have added this to the TODO list:

    * Allow server logs to be read using SQL commands
    * Allow server configuration parameters to be modified remotetly

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:

>
> Basically I think we are converging on an answer that we can't do any of
> this for 7.5.

If it's not going into the distribution as contrib or core, I'll package
that as additional admin pack. I'm quite sure I can convince the win32
installer packager guys to include that as default-on option as soon as
I'm able to prove them how it's working.

> The scope has gone way beyond what we had at feature
> freeze, and we can't even get it to work on Win32,

??? The functions will work for win32, are you talking about logging?
The win32 log issue isn't a serverlog rotation issue, as I stated also
the current stderr output is affected! I'd clearly see that as a fix,
whilst it might be more than 10 lines of code. I'll try to fix that
tomorrow. The very log_destination=file and rotation code will be more
or less the same as for ***x.

> I have added this to the TODO list:
>
>     * Allow server logs to be read using SQL commands
>     * Allow server configuration parameters to be modified remotetly

This is desirable in any case. But until 7.6 will be around, 1-1.5 years
and ???,000 installations will happen. Since the functionality is
available now or will be available VSN (certainly before 7.5 release),
there's no good reason suppress it.

Regards,
Andreas

Andreas Pflug wrote:
> Bruce Momjian wrote:
>
> >
> > Basically I think we are converging on an answer that we can't do any of
> > this for 7.5.
>
> If it's not going into the distribution as contrib or core, I'll package
> that as additional admin pack. I'm quite sure I can convince the win32
> installer packager guys to include that as default-on option as soon as
> I'm able to prove them how it's working.

Uh, but it isn't working on Win32, right?  How do you fix that?  During
beta?  That would be OK as an add-on.

> > The scope has gone way beyond what we had at feature
> > freeze, and we can't even get it to work on Win32,
>
> ??? The functions will work for win32, are you talking about logging?

I mean logging.

> The win32 log issue isn't a serverlog rotation issue, as I stated also
> the current stderr output is affected! I'd clearly see that as a fix,
> whilst it might be more than 10 lines of code. I'll try to fix that
> tomorrow. The very log_destination=file and rotation code will be more
> or less the same as for ***x.

But does it work on Win32 and Unix?  It has to.

> > I have added this to the TODO list:
> >
> >     * Allow server logs to be read using SQL commands
> >     * Allow server configuration parameters to be modified remotetly
>
> This is desirable in any case. But until 7.6 will be around, 1-1.5 years
> and ???,000 installations will happen. Since the functionality is
> available now or will be available VSN (certainly before 7.5 release),
> there's no good reason suppress it.

Yes, that is a problem but we have to cut or we will never get to beta.
Probably doing an add-on until 7.6 might be the best but you are working
on it so let's see.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Andreas Pflug <pgadmin@pse-consulting.de> writes:
> Bruce Momjian wrote:
>> So, I suggest we get the logging code into the backend, and you can code
>> anything you want pgadmin to do in plperlu, and Win32 supports plperlu
>> too.  The big advantage is that you can improve the plperlu functions
>> with every release of pgadmin.

> I do not agree on this. Administrative tools should require as few
> additional backend packages as possible.

This argument doesn't really hold water when the alternative is a
contrib package.  It's considerably more likely that the installation
will have plperl than that it will have some random contrib package.

Also, what happens if you find that the contrib package doesn't do
everything you need?  You'll be stuck for another PG release cycle,
whereas rejiggering a plperl function that pgadmin is defining for
itself is no problem.

I think that developing with plperl for a few months would be a good
path, because then you would have some actual field experience under
your belt to justify the specific design of the contrib or core backend
functions you want.  Right now they look mighty scattershot.

> It won't generate trust if pgadmin documentation advises "install
> untrusted plperl to maintain your machine".

Nonsense.  You are already expecting these people to give you superuser
access, no?  They had better trust you already.  (Actually, you wouldn't
even have to ask them --- you could just create plperlu for yourself
--- but I suppose that would seem impolite.)

> I posted this stuff as contrib
> module to keep it off the feature freeze issue.

Contrib does not have an exception for the feature freeze rule.  It's a
little looser maybe but that doesn't mean we'll accept an entire new
module after freeze.

> Reimplementing it as plperlu is crap.

I'm sorry you feel that way.  It seemed like a fairly attractive
alternative to me, especially for early development.

            regards, tom lane

Bruce Momjian wrote:
>>
>>If it's not going into the distribution as contrib or core, I'll package
>>that as additional admin pack. I'm quite sure I can convince the win32
>>installer packager guys to include that as default-on option as soon as
>>I'm able to prove them how it's working.
>
>
> Uh, but it isn't working on Win32, right?  How do you fix that?  During
> beta?  That would be OK as an add-on.

As I said, I'm going to fix the stderr issue. This will enable the
pending log rotation stuff more or less incidentially.

>
>
>>The win32 log issue isn't a serverlog rotation issue, as I stated also
>>the current stderr output is affected! I'd clearly see that as a fix,
>>whilst it might be more than 10 lines of code. I'll try to fix that
>>tomorrow. The very log_destination=file and rotation code will be more
>>or less the same as for ***x.
>
>
> But does it work on Win32 and Unix?  It has to.

I meant to implement that stderr handling stuff for win32, in order to
have a clean stderr output. Unix isn't affected, it works as posted.

>
> Yes, that is a problem but we have to cut or we will never get to beta.
> Probably doing an add-on until 7.6 might be the best but you are working
> on it so let's see.

The add-on are the functions, not the log rotation. Log rotation for
unix is beta-ready, and virtually unchanged for weeks now. It will be
available shortly for win32 too, as soon as the stderr piping issue is
solved. I'm a bit tired of arguing, that's why I didn't split the
functions in a part that was proposed together with the core
functionality before feature freeze and an additional part. I feel that
the majority of installations (win32) will have them anyway.


Regards,
Andreas

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: 29 July 2004 18:41
> To: Andreas Pflug
> Cc: Bruce Momjian; PostgreSQL Patches; Dave Page
> Subject: Re: [PATCHES] Admin functions contrib
>
> Andreas Pflug <pgadmin@pse-consulting.de> writes:
> > Bruce Momjian wrote:
> >> So, I suggest we get the logging code into the backend,
> and you can
> >> code anything you want pgadmin to do in plperlu, and Win32
> supports
> >> plperlu too.  The big advantage is that you can improve
> the plperlu
> >> functions with every release of pgadmin.
>
> > I do not agree on this. Administrative tools should require as few
> > additional backend packages as possible.
>
> This argument doesn't really hold water when the alternative
> is a contrib package.  It's considerably more likely that the
> installation will have plperl than that it will have some
> random contrib package.
>
> Also, what happens if you find that the contrib package
> doesn't do everything you need?  You'll be stuck for another
> PG release cycle, whereas rejiggering a plperl function that
> pgadmin is defining for itself is no problem.

pgAdmin I used to create helper functions and views on the server, and
not only were they a *real* pain in the neck to manage, but they were
also the most often complained about 'feature' of pgAdmin, to the extent
that when pgAdmin II was written, rule number #1 was 'it must offer 100%
functionality on a clean, standard database with no server side
objects'. In pga1 it even got to the stage that I wrote a cleanup wizard
to allow ppl to remove the stuff that was created. We also had problems
with people who had limited access to their servers (because they were
ISP hosted etc). I've not even persuaded hub.org to install pl/pgsql for
the postgresql.org sites for example - I can't imagine the response I'd
get if I asked for pl/perlu!!

This is primarily why we want to get the functionality into the backend.
Secondary to that, it will also allow phpPgAdmin and other tools to
offer the same functionality. It could be argued of course, that a
contrib module violates our standard database rule (which it does), but
it does at least allow us to get some standard code into the
distribution, in a way that /might/ be compatible with the feature
freeze, with a view to full integration in the next cycle. As Bruce has
seen, this is some pretty nice functionality that Andreas has added to
pga3, and is one of the few areas that we lag behind SQL Server etc. in
on the management front.

Regards, Dave.

Dave Page wrote:

> As Bruce has seen, this is some pretty nice functionality that
 > Andreas has added to pga3, and is one of the few areas that we
 > lag behind SQL Server etc. in on the management front.
>

If you're curious what Bruce has seen, it was this:

http://www.pse-consulting.de/pgadmin3/pgadmin3-serverlog.png

The size functions will offer this:
http://www.pse-consulting.de/pgadmin3/pgadmin3-tblspc-stat.png

Regards,
Andreas

Andreas Pflug wrote:
> Dave Page wrote:
>
> > As Bruce has seen, this is some pretty nice functionality that
>  > Andreas has added to pga3, and is one of the few areas that we
>  > lag behind SQL Server etc. in on the management front.
> >
>
> If you're curious what Bruce has seen, it was this:
>
> http://www.pse-consulting.de/pgadmin3/pgadmin3-serverlog.png
>
> The size functions will offer this:
> http://www.pse-consulting.de/pgadmin3/pgadmin3-tblspc-stat.png

Yep, that's what I saw.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Dave Page wrote:
> > Also, what happens if you find that the contrib package
> > doesn't do everything you need?  You'll be stuck for another
> > PG release cycle, whereas rejiggering a plperl function that
> > pgadmin is defining for itself is no problem.
>
> pgAdmin I used to create helper functions and views on the server, and
> not only were they a *real* pain in the neck to manage, but they were
> also the most often complained about 'feature' of pgAdmin, to the extent
> that when pgAdmin II was written, rule number #1 was 'it must offer 100%
> functionality on a clean, standard database with no server side
> objects'. In pga1 it even got to the stage that I wrote a cleanup wizard
> to allow ppl to remove the stuff that was created. We also had problems
> with people who had limited access to their servers (because they were
> ISP hosted etc). I've not even persuaded hub.org to install pl/pgsql for
> the postgresql.org sites for example - I can't imagine the response I'd
> get if I asked for pl/perlu!!
>
> This is primarily why we want to get the functionality into the backend.
> Secondary to that, it will also allow phpPgAdmin and other tools to
> offer the same functionality. It could be argued of course, that a
> contrib module violates our standard database rule (which it does), but
> it does at least allow us to get some standard code into the
> distribution, in a way that /might/ be compatible with the feature
> freeze, with a view to full integration in the next cycle. As Bruce has
> seen, this is some pretty nice functionality that Andreas has added to
> pga3, and is one of the few areas that we lag behind SQL Server etc. in
> on the management front.

Ouch, that is a powerful argument!  The big problem is that we are
designing features a month after feature freeze.  Not that the ideas
weren't around before feature freeze, but they are still not solid from
a community agreement perspective.  Let's get the logging working and
then figure out what to do, OK?

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: Fri 7/30/2004 5:34 PM
To: Dave Page
Cc: Tom Lane; Andreas Pflug; PostgreSQL Patches
Subject: Re: [PATCHES] Admin functions contrib

> Ouch, that is a powerful argument!  The big problem is that we are
> designing features a month after feature freeze.  Not that the ideas
> weren't around before feature freeze, but they are still not solid from
> a community agreement perspective.  Let's get the logging working and
> then figure out what to do, OK?

Sounds good to me :-)

Regards, Dave

Do people want the server file logging/rotating patch applied if it is
Unix-only?  Right now the patch is ifdef'ed so Win32 use of it is
disabled.

Andreas is asking.

---------------------------------------------------------------------------

Dave Page wrote:
>
>
> > -----Original Message-----
> > From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> > Sent: 29 July 2004 18:41
> > To: Andreas Pflug
> > Cc: Bruce Momjian; PostgreSQL Patches; Dave Page
> > Subject: Re: [PATCHES] Admin functions contrib
> >
> > Andreas Pflug <pgadmin@pse-consulting.de> writes:
> > > Bruce Momjian wrote:
> > >> So, I suggest we get the logging code into the backend,
> > and you can
> > >> code anything you want pgadmin to do in plperlu, and Win32
> > supports
> > >> plperlu too.  The big advantage is that you can improve
> > the plperlu
> > >> functions with every release of pgadmin.
> >
> > > I do not agree on this. Administrative tools should require as few
> > > additional backend packages as possible.
> >
> > This argument doesn't really hold water when the alternative
> > is a contrib package.  It's considerably more likely that the
> > installation will have plperl than that it will have some
> > random contrib package.
> >
> > Also, what happens if you find that the contrib package
> > doesn't do everything you need?  You'll be stuck for another
> > PG release cycle, whereas rejiggering a plperl function that
> > pgadmin is defining for itself is no problem.
>
> pgAdmin I used to create helper functions and views on the server, and
> not only were they a *real* pain in the neck to manage, but they were
> also the most often complained about 'feature' of pgAdmin, to the extent
> that when pgAdmin II was written, rule number #1 was 'it must offer 100%
> functionality on a clean, standard database with no server side
> objects'. In pga1 it even got to the stage that I wrote a cleanup wizard
> to allow ppl to remove the stuff that was created. We also had problems
> with people who had limited access to their servers (because they were
> ISP hosted etc). I've not even persuaded hub.org to install pl/pgsql for
> the postgresql.org sites for example - I can't imagine the response I'd
> get if I asked for pl/perlu!!
>
> This is primarily why we want to get the functionality into the backend.
> Secondary to that, it will also allow phpPgAdmin and other tools to
> offer the same functionality. It could be argued of course, that a
> contrib module violates our standard database rule (which it does), but
> it does at least allow us to get some standard code into the
> distribution, in a way that /might/ be compatible with the feature
> freeze, with a view to full integration in the next cycle. As Bruce has
> seen, this is some pretty nice functionality that Andreas has added to
> pga3, and is one of the few areas that we lag behind SQL Server etc. in
> on the management front.
>
> Regards, Dave.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:
> Do people want the server file logging/rotating patch applied if it is
> Unix-only?  Right now the patch is ifdef'ed so Win32 use of it is
> disabled.
>
> Andreas is asking.

Please commit ASAP. Is I stated several times, I'll do the win32 as soon
as I get a chance to. It's not a logger file issue, it's a *win32
stderr* problem.

If you believe that a feature must support all systems, tablespace must
be removed as well.

Regards,
Andreas

Bruce Momjian wrote:
> Do people want the server file logging/rotating patch applied if it
> is Unix-only?  Right now the patch is ifdef'ed so Win32 use of it is
> disabled.

How is logging typically handled on Windows?

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

It's better than no patch I think.

/D


-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: Sat 7/31/2004 5:33 AM
To: Dave Page
Cc: Tom Lane; Andreas Pflug; PostgreSQL Patches
Subject: Re: [PATCHES] Admin functions contrib


Do people want the server file logging/rotating patch applied if it is
Unix-only?  Right now the patch is ifdef'ed so Win32 use of it is
disabled.

Andreas is asking.

---------------------------------------------------------------------------

Dave Page wrote:
>
>
> > -----Original Message-----
> > From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> > Sent: 29 July 2004 18:41
> > To: Andreas Pflug
> > Cc: Bruce Momjian; PostgreSQL Patches; Dave Page
> > Subject: Re: [PATCHES] Admin functions contrib
> >
> > Andreas Pflug <pgadmin@pse-consulting.de> writes:
> > > Bruce Momjian wrote:
> > >> So, I suggest we get the logging code into the backend,
> > and you can
> > >> code anything you want pgadmin to do in plperlu, and Win32
> > supports
> > >> plperlu too.  The big advantage is that you can improve
> > the plperlu
> > >> functions with every release of pgadmin.
> >
> > > I do not agree on this. Administrative tools should require as few
> > > additional backend packages as possible.
> >
> > This argument doesn't really hold water when the alternative
> > is a contrib package.  It's considerably more likely that the
> > installation will have plperl than that it will have some
> > random contrib package.
> >
> > Also, what happens if you find that the contrib package
> > doesn't do everything you need?  You'll be stuck for another
> > PG release cycle, whereas rejiggering a plperl function that
> > pgadmin is defining for itself is no problem.
>
> pgAdmin I used to create helper functions and views on the server, and
> not only were they a *real* pain in the neck to manage, but they were
> also the most often complained about 'feature' of pgAdmin, to the extent
> that when pgAdmin II was written, rule number #1 was 'it must offer 100%
> functionality on a clean, standard database with no server side
> objects'. In pga1 it even got to the stage that I wrote a cleanup wizard
> to allow ppl to remove the stuff that was created. We also had problems
> with people who had limited access to their servers (because they were
> ISP hosted etc). I've not even persuaded hub.org to install pl/pgsql for
> the postgresql.org sites for example - I can't imagine the response I'd
> get if I asked for pl/perlu!!
>
> This is primarily why we want to get the functionality into the backend.
> Secondary to that, it will also allow phpPgAdmin and other tools to
> offer the same functionality. It could be argued of course, that a
> contrib module violates our standard database rule (which it does), but
> it does at least allow us to get some standard code into the
> distribution, in a way that /might/ be compatible with the feature
> freeze, with a view to full integration in the next cycle. As Bruce has
> seen, this is some pretty nice functionality that Andreas has added to
> pga3, and is one of the few areas that we lag behind SQL Server etc. in
> on the management front.
>
> Regards, Dave.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Peter Eisentraut wrote:
> Bruce Momjian wrote:
>
>>Do people want the server file logging/rotating patch applied if it
>>is Unix-only?  Right now the patch is ifdef'ed so Win32 use of it is
>>disabled.
>
>
> How is logging typically handled on Windows?

It is done using the eventlog service (which is supported as replacement
for syslog now) or in case of MSSQL as file logging.

MSSQL in more detail:
In eventlog only the most important MSSQL messages will appear
(start/stop, PANIC), while the rest is done in logfiles (retrievable
over client tools, rotatable using sp_cycle_errorlog) and may grow much
larger than eventlog (which will receive messages from all apps/services).

This handling is so throughout M$ apps, like webserver etc: fatals to
eventlog, the rest to files.

Regards,
Andreas

>> Do people want the server file logging/rotating patch applied if it is
>> Unix-only?  Right now the patch is ifdef'ed so Win32 use of it is
>> disabled.

I'm slightly worried that we might be painting ourselves into a corner,
ie implementing functionality that will never work on Windows.

Personally, of course, I won't care if it never works on Windows.  But
I suspect there are some out there who do care ;-).  It might be better
to wait till we're sure there's a reasonable implementation path for
Windows.

            regards, tom lane

Tom Lane wrote:
>>>Do people want the server file logging/rotating patch applied if it is
>>>Unix-only?  Right now the patch is ifdef'ed so Win32 use of it is
>>>disabled.
>
>
> I'm slightly worried that we might be painting ourselves into a corner,
> ie implementing functionality that will never work on Windows.
>
> Personally, of course, I won't care if it never works on Windows.  But
> I suspect there are some out there who do care ;-).  It might be better
> to wait till we're sure there's a reasonable implementation path for
> Windows.

Actually, I believe the implementation I did first (having all processes
append to the logfile themselves) would have worked for win32 too.
As long as you don't impose linux-centric limitations on win32
implementations, there certainly *are* solutions to the problem.

A very reasonable way would be to have the win32_signal_waiter thread
not only wait for the child terminating, but also checking the pipe.
This is certainly *the* recommended win32 way.

Regards,
Andreas

This logging issue has drained a lot of time during feature freeze when
we should be getting existing patches in and completed.

I am willing to apply a perfect patch but I am not willing to spend
anymore time on this than I have to.  This is all something that
shouldn't be happening during feature freeze.

I realize this patch is important to Andreas but every patch is
important to its author.

We have done just fine without this functionality for years so I don't
see the rush to get it in.  You can say Win32 will make it more
significant, but if it doesn't work on Win32 yet, the argument doesn't
work.

And I am not going to apply a patch with the promise that a more
complete one is coming later.

I am not saying I am going to reject the patch if it arrives but I am
not but I am not going to be upset if it doesn't make it in until 7.6.

---------------------------------------------------------------------------

Andreas Pflug wrote:
> Bruce Momjian wrote:
> > Do people want the server file logging/rotating patch applied if it is
> > Unix-only?  Right now the patch is ifdef'ed so Win32 use of it is
> > disabled.
> >
> > Andreas is asking.
>
> Please commit ASAP. Is I stated several times, I'll do the win32 as soon
> as I get a chance to. It's not a logger file issue, it's a *win32
> stderr* problem.
>
> If you believe that a feature must support all systems, tablespace must
> be removed as well.
>
> Regards,
> Andreas
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073