There are many data sources external to the database that can provide interesting data. That data combined with data in the database creates a richer set of information.

In this example by David Fetter, we want to combine information from the system process table with the information in the view pg_stat_activity. pg_stat_activity shows information that PostgreSQL knows about active queries in the database installation. That information combined with the system process table can help you keep a better eye on your resources and any runaway queries.

Using information from external data sources requires that you use an untrusted language. Issue #32 discusses the differences between trusted and untrusted languages. Each have their benefits.

In this case, we will use plperlu with the module Proc::ProcessTable against an 8.0Beta3 server on SuSE Linux and take advantage of the view pg_stat_activity.

• 1. PostgreSQL was built or installed postgres --with-perl
• 2. Proc::ProcessTable is installed in the perl installation local to your database server.
• 3. createlang 'plperlu' has been issued by the Postgresql superuser (usually postgres).
• 4. In postgresql.conf set stats_command_string=true

The second issue is a bit more complex. The module Proc::ProcessTable creates a hash which has keynames that are the same as some functions in Perl. Instead of using the names as plain text, these functions were evaluated and the result of that call was used instead the key name. This is a bug that is fixed in CVSHEAD and will be available in 8.0Beta4. The alternative version of this script will run correctly in 8.0Beta3. The work around was to explicitly name (and unname) the key names in the hash.

You are running a beta version of 8.0 aren't you?

In general, the technique used to bring in external data is to read it in and construct a tuple or tuples from it. The tuples are returned as a ROWTYPE and then can be joined with database information.

In order to return a ROWTYPE, the type must be created in advance. We are going to bring in the process table information via the Proc::Process table perl module. It contains many fields and it is possible that the fields may vary across platforms. For this reason, we are using a function to generate the type description based on the information provided by Proc::ProcessType before using the function.

Generating the Type

Once the CREATE TYPE statement is constructed, it is executed. This function needs to be run once per database--on installation of the main function, pg_ps(). If a database is moved across platforms, it should be rerun in the moved database. See also perldoc proc::processtable for more information about Proc::ProcessTable

CREATE OR REPLACE FUNCTION init_ps ()
RETURNS VOID
LANGUAGE plperlu
AS $$
    use Proc::ProcessTable;
    my $t = new Proc::ProcessTable;
    my $sql = "CREATE TYPE process_table_type AS (\n";
    foreach my $field (@{[$t->fields]}) {
        next unless $field =~ /\S/;
        if ($field =~ /id$/) {
            $sql .= qq(  $field INTEGER,\n);
        } else {
            $sql .= qq(  $field TEXT,\n);
        }
    }   
    $sql =~ s/,\n$/\n);/m;
    my $rv = spi_exec_query($sql);
    return; 
$$;
    
COMMENT ON FUNCTION init_ps() IS $$
Copyright (c) 2004, David Fetter
All rights reserved.

Redistribution and use allowed under a BSD license.
$$;
SELECT init_ps();

Generating the Tuple

CREATE OR REPLACE FUNCTION get_ps ()
RETURNS SETOF process_table_type
LANGUAGE plperlu AS
$$
    use Proc::ProcessTable;
    my $proctab = new Proc::ProcessTable;
    return $proctab->table;
$$
;

COMMENT ON FUNCTION get_ps() IS $$
Copyright (c) 2004, David Fetter
All rights reserved.

Redistribution and use allowed under a BSD license.
$$;

Using get_ps() with pg_stat_activity

uid	gid	pid	ppid
pgrp	sess	priority	ttynum
flags	minflt	cminflt	majflt
cmajflt	utime	stime	cutime
cstime	time	ctime	size
rss	wchan	fname	start
pctcpu	state	pctmem	cmndline
exec	euid	suid	fuid
egid	sgid	fgid	cwd

datid	datname	procpid	usesysid
usename	current_query	query_start

select p.datname, p.procpid, p.usename, p.current_query, g.pctmem, g.pctcpu
from pg_stat_activity p JOIN get_ps() g ON (p.procpid = g.pid);

 datname | procpid | usename |            current_query             | pctmem | pctcpu 
---------+---------+---------+--------------------------------------+-----------+-----------+
 elein   |    8214 | elein   | <IDLE>                               | 5.00   | 0.47      
 elein   |    8235 | elein   | select * from pg_proc p1, pg_proc p2 | 0.00      | 56.60     
(2 rows)

Contributors: david at fetter.org, elein at varlena.com

New Minor Releases
[ANNOUNCE] PostgreSQL Security Release(s) for 7.2, 7.3 and 7.4	23-Oct-2004

In order to address a recent security report from iDefence, we have released 3 new "point" releases: 7.2.6, 7.3.8 and 7.4.6

Although rated only a Medium risk, according to their web site: "A vulnerability exists due to the insecure creation of temporary files, which could possibly let a malicious user overwrite arbitrary files."

Also in these releases is a potential 'data loss' bug that was recently identified:

• Repair possible failure to update hint bits on disk Under rare circumstances this oversight could lead to "could not access transaction status" failures, which qualifies it as a potential-data-loss bug.

Although not yet available via Bittorrent, these releases are available through ftp at all of the mirrors, and Devrim is currently working on RPMs for the various releases, which should be available soon.

For a listing of all currently available FTP mirrors, please see:

http://www.postgresql.org/mirrors-ftp.html

General Bits was missing in action last week due to time constraints. Although I strive to produce a column each and every week, sometimes it becomes impossible due to other commitments. Please bear with me as I juggle my time. And feel free to contribute items and ideas.

Thanks, --elein

Contributors: elein at varlena.com