Greg's Postgres stuff

Version 2.6.0 of DBD::Pg, the Perl interface to Postgres, has just been released. I've been trying to keep to a "release early, release often" system for DBD::Pg this year. In addition to some fixes to make compiling on Strawberry Perl and other Perl Windows distributions happier, here's the major changes in the last few versions:

• pg_notifies is now a true database handle method, so you can simply use $dbh->pg_notifies();
• $dbh->column_info() now shows enum values (thanks to Dave Rolsky)
• Added three new statement handle attributes, mostly to support a (very cool) future project I'm working on:
  • $sth->{pg_bound} : returns a hash of all placeholders, indicating whether each one has been bound yet.
  • $sth->{pg_numbound} : returns the number of placeholders currently bound.
  • $sth->{pg_current_row} : returns the number of the last tuple fetched.
• Various performance improvements, mostly to streamline the do() and execute/prepare() calls as much as possible

Give it a whirl, and please consider using CPAN::Reporter, which is invaluable to Perl module authors. It's similar to the Postgres Build Farm in that it allows your software to be tested on a wide variety of platforms and versions. The current reports for DBD::Pg shows we're doing well, but need more testers.

Inspired by the old meme about the top shell commands, I thought I'd see what the most common SQL commands I run are. When you are using psql, (the command-line interface to Postgres), it keeps a history of all the commands you enter inside the file ~/.psql_history. It only keeps 500 lines by default, which I find way too low, so I adjust it by setting the HISTSIZE variable. Here's the usual contents of my .psqlrc file:

\pset null '\\N'
\set HISTSIZE 5000
\timing
\set G '\\x\\g\\x'

The first line gives a better visual output to nulls. The second tells psql to allow the .psql_history file to grow to 5000 lines, while the third line times all the commands you enter. The final line allows you to run a single command in expanded mode like so:

greg=# SELECT * FROM pg_class  :G

Interestingly, it looks like a variant of the above may make its way into the next version of psql...

On to the counting meme, however. Here's the command I used:

awk '{print $1}' ~/.psql_history | sort | uniq -c |sort -rn | head -10

The output on one of the production boxes I watch over gives:

   1356 select
    386 \d
    347 \q
    186 vacuum
    141 \c
    126 update
     84 \x
     59 SELECT
     46 begin;
     41 \dt

Note that older versions of psql did not wrap multi-line commands into a single line inside the file, so you may see odd results when using < 8.2

Although Postgres has already received some good proposals for the Google Summer of Code project, we'd like to see more. Google has extended the deadline for turning in student proposals to April 7, so please consider throwing an idea at us. Details on how to apply can be found on the Postgres Summer of Code page.

I've just released a new version of check_postgres.pl, an all-in-one Nagios plugin for Postgres that perform a number of checks. That number has just increased to 22 with the addition of:

• txn_idle - Checks the maximum "idle in transation" time
• txn_time - Checks the maximum open transaction time

Both of these require Postgres 8.3 or better, as they use the relatively new xact_start field of the pg_stat_activity view. The script can always be found at: bucardo.org/nagios, and the documentation can be viewed there as well (just a pod2html dump actually, as the docs are part of the script as well). Thank to Rob Treat for suggesting these two items.

I'm happy to announce that DBD::Pg, the Perl Postgres driver, is now up to version 2.2.1. The reason for the jump in the minor version (the second 2 in "2.2.1") is the addition of enhanced tracing flags, a relatively new DBI feature that DBD::Pg now takes full advantage of. Tracing allows you to see what's going on inside the code, and is very useful for debugging purposes. I'll show some examples below.