PostgreSQL Training,
Consulting & Support
General Bits
By A. Elein Mustain

25-Oct-2004 Issue: 90

Archives | General Tidbits | Google General Bits | Docs | Castellano | PortuguÍs | Subscriptions | Notifications | | Prev

General Bits is a column loosely based on the PostgreSQL mailing list pgsql-general.
To find out more about the pgsql-general list and PostgreSQL, see

System Process Table Information
[HACKERS] External Tabular Data Via SQL 03-Oct-2004

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
There are two important dependencies on 8.0 for this script. The first is that dollar quoting is used for this example. Dollar quoting was discussed in Issue #88 and is not available in 7.4 or earlier versions. You can changing the quoting conventions if you need this function in an earlier release.

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

This function queries the ProcessTable module in order to determine the name and type of each value. Using that information it constructs an appropriate CREATE TYPE statement for the module on this platform. The ProcessTable stores the values in arrayref of hash reference and the fields are conveniently named so that the fields ending with id are integers and the other fields are text fields.

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

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);
Copyright (c) 2004, David Fetter
All rights reserved.

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

Generating the Tuple

The function itself then becomes insanely simple. Proc::ProcessTable returns an arrayref of hashes which is exactly the form that plperlu requires to return a set of tuples. The data is created and returned.
RETURNS SETOF process_table_type
    use Proc::ProcessTable;
    my $proctab = new Proc::ProcessTable;
    return $proctab->table;

Copyright (c) 2004, David Fetter
All rights reserved.

Redistribution and use allowed under a BSD license.

Using get_ps() with pg_stat_activity

On my platform, the columns available with get_ps() are:
flagsminfltcminflt majflt
cmajfltutimestime cutime
cstime timectime size
rsswchanfname start
pctcpustatepctmem cmndline
execeuidsuid fuid
The columns available from pg_stat_activity are:
usenamecurrent_query query_start
That is a lot of data to select. Let us look at the command line, the percentage of memory and percentage of cpu along with some of the pg_stat_activity fields. Note that pg_stat_activity.procpid is the same as get_ps().pid so you can join ON (procid = pid).
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 =;

 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)
As you can see there is not a lot of activity on my machine at this time and I wrote a ridiculous query to ensure something costly was running (don't try this on production!).

Contributors: david at, elein at
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:
Contributors: Marc G. Fournier scrappy at
Missing Issues
Work and Money 23-Oct-2004

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

Comments and Corrections are welcome. Suggestions and contributions of items are also welcome. Send them in!
Copyright A. Elein Mustain 2003, 2004, 2005, 2006, 2007, 2008, 2009

Search General Bits & Search WWW