Perl Blog Archive
Protecting and auditing your secure PostgreSQL data
PostgreSQL functions can be written in many languages. These languages fall into two categories, 'trusted' and 'untrusted'. Trusted languages cannot do things "outside of the database", such as writing to local files, opening sockets, sending email, connecting to other systems, etc. Two such languages are PL/pgSQL and and PL/Perl. For "untrusted" languages, such as PL/PerlU, all bets are off, and they have no limitations placed on what they can do. Untrusted languages can be very powerful, and sometimes dangerous.
One of the reasons untrusted languages can be considered dangerous is that they can cause side effects outside of the normal transactional flow that cannot be rolled back. If your function writes to local disk, and the transaction then rolls back, the changes on disk are still there. Working around this is extremely difficult, as there is no way to detect when a transaction has rolled back at the level where you could, for example, undo your local disk changes.
However, there are times when this effect can be very useful. For example, in a recent thread on the PostgreSQL "general" mailing list (aka pgsql-general), somebody asked for a way to audit SELECT queries into a logging table that would survive someone doing a ROLLBACK. In other words, if you had a function named weapon_details() and wanted to have that function log all requests to it by inserting to a table, a user could simply run the query, read the data, and then rollback to thwart the auditing:
BEGIN;
SELECT weapon_details('BFG 9000'); -- also inserts to an audit table
ROLLBACK; -- inserts to the audit table are now gone!
Certainly there are other ways to track who is using this query, the most obvious being by enabling full Postgres logging (by setting log_statement = 'all' in your postgresql.conf file.) However, extracting that information from logs is no fun, so let's find a way to make that INSERT stick, even if the surrounding function was rolled back.
Stepping back for one second, we can see there are actually two problems here: restricting access to the data, and logging that access somewhere. The ultimate access restriction is to simply force everyone to go through your custom interface. However, in this example, we will assume that someone has psql access and needs to be able to run ad hoc SQL queries, as well as be able to BEGIN, ROLLBACK, COMMIT, etc.
Let's assume we have a table with some Very Important Data inside of it. Further, let's establish that regular users can only see some of that data, and that we need to know who asked for what data, and when. For this example, we will create a normal user named Alice:
postgres=> CREATE USER alice;
CREATE ROLE
We need a way to tell which rows are suitable for people like Alice to view. We will set up a quick classification scheme using the nifty ENUM feature of PostgreSQL:
postgres=> CREATE TYPE classification AS ENUM (
'unclassified',
'restricted',
'confidential',
'secret',
'top secret'
);
CREATE TYPE
Next, as a superuser, we create the table containing sensitive information, and populate it:
postgres=> CREATE TABLE weapon (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
cost TEXT NOT NULL,
security_level CLASSIFICATION NOT NULL,
description TEXT NOT NULL DEFAULT 'a fine weapon'
);
NOTICE: CREATE TABLE will create implicit sequence "weapon_id_seq" for serial column "weapon.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "weapon_pkey" for table "weapon"
CREATE TABLE
postgres=> INSERT INTO weapon (name,cost,security_level) VALUES
('Crowbar', 10, 'unclassified'),
('M9', 200, 'restricted'),
('M16A2', 300, 'restricted'),
('M4A1', 400, 'restricted'),
('FGM-148 Javelin', 700, 'confidential'),
('Pulse Rifle', 50000, 'secret'),
('Zero Point Energy Field Manipulator', 'unknown', 'top secret');
INSERT 0 7
We don't want anyone but ourselves to be able to access this table, so for safety, we make some explicit revocations. We'll examine the permissions before and after we do this:
postgres=> \dp weapon
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+--------+-------+-------------------+--------------------------
public | weapon | table | |
postgres=> REVOKE ALL ON TABLE weapon FROM public;
REVOKE
postgres=> \dp weapon
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+--------+-------+---------------------------+--------------------------
public | weapon | table | postgres=arwdDxt/postgres |
As you can see, what the REVOKE really does is remove the implicit "no permission" and grant explicit permissions to only the postgres user to view or modify the table. Let's confirm that Alice cannot do anything with that table:
postgres=> \c postgres alice
You are now connected to database "postgres" as user "alice".
postgres=> postgres=> SELECT * FROM weapon;
ERROR: permission denied for relation weapon
postgres=> postgres=> UPDATE weapon SET id = id;
ERROR: permission denied for relation weapon
Alice does need to have access to parts of this table, so we will create a "wrapper function" that will query the table for us and return some results. By declaring this function as SECURITY DEFINER, it will run as if the person who created the function invoked it - in this case, the postgres user. For this example, we'll be letting Alice see the "cost and description" of exactly one item at a time. Further, we are not going to let her (or anyone else using this function) view certain items. Only those items classified as "confidential" or lower can be viewed (i.e. "confidential", "restricted", or "unclassified"). Here's the first version of our function:
postgres=> CREATE LANGUAGE plperlu;
CREATE LANGUAGE
postgres=> CREATE OR REPLACE FUNCTION weapon_details(TEXT)
RETURNS TABLE (name TEXT, cost TEXT, description TEXT)
LANGUAGE plperlu
SECURITY DEFINER
AS $bc$
use strict;
use warnings;
## The item they are looking for
my $name = shift;
## We will be nice and ignore the case and any whitespace
$name =~ s{^\s*(\S+)\s*$}{lc $1}e;
## What is the maximum security_level that people who are
## calling this function can view?
my $seclevel = 'confidential';
## Query the table and pull back the matching row
## We need to differentiate between "not found" and "not allowed",
## by comparing a passed-in level to the security_level for that row.
my $SQL = q{
SELECT name,cost,description,
CASE WHEN security_level <= $1 THEN 1 ELSE 0 END AS allowed
FROM weapon
WHERE LOWER(name) = $2};
## Run the query, pull back the first row, as well as the allowed column value
my $sth = spi_prepare($SQL, 'CLASSIFICATION', 'TEXT');
my $rv = spi_exec_prepared($sth, $seclevel, $name);
my $row = $rv->{rows}[0];
my $allowed = delete $row->{allowed};
## Did we find anything? If not, simply return undef
if (! $rv->{processed}) {
return undef;
}
## Throw an exception if we are not allowed to view this row
if (! $allowed) {
die qq{Sorry, you are not allowed to view information on that weapon!\n};
}
## Return the requested data
return_next($row);
$bc$;
CREATE FUNCTION
The above should be fairly self-explanatory. We are using PL/Perl's built-in database access functions, such as spi_prepare, to do the actual querying. Let's confirm that this works as it should for Alice:
postgres=> \c postgres alice
You are now connected to database "postgres" as user "alice".
postgres=> SELECT * FROM weapon_details('crowbar');
name | cost | description
---------+------+---------------
Crowbar | 10 | a fine weapon
(1 row)
postgres=> SELECT * FROM weapon_details('anvil');
name | cost | description
------+------+-------------
(0 rows)
postgres=> SELECT * FROM weapon_details('pulse rifle');
ERROR: Sorry, you are not allowed to view information on that weapon!
CONTEXT: PL/Perl function "weapon_details"
Now that we have solved the restricted access problem, let's move on the auditing. We will create a simple table to hold information about who accessed what and when:
postgres=> CREATE TABLE data_audit (
tablename TEXT NOT NULL,
arguments TEXT NULL,
results INTEGER NULL,
status TEXT NOT NULL DEFAULT 'normal',
username TEXT NOT NULL DEFAULT session_user,
txntime TIMESTAMPTZ NOT NULL DEFAULT now(),
realtime TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
);
CREATE TABLE
The 'tablename' column simply records which table they are getting data from. The 'arguments' is a free-form field describing what they were looking for. The 'results' column shows how many matching rows were found. The 'status' column will be used primarily to log unusual requests, such as the case where Alice looks for a forbidden item. The 'username' column records the name of the user doing the searching. Because we are using functions with SECURITY DEFINER set, this needs to be session_user, not current_user, as the latter will switch to 'postgres' within the function, and we want to log the real caller (e.g. 'alice'). The final two columns tell us then the current transaction started, and the exact time when an entry was made inside of this table. As a first attempt, we'll have our function do some simple inserts to this new data_audit table:
postgres=> CREATE OR REPLACE FUNCTION weapon_details(TEXT)
RETURNS TABLE (name TEXT, cost TEXT, description TEXT)
LANGUAGE plperlu
SECURITY DEFINER
AS $bc$
use strict;
use warnings;
## The item they are looking for
my $name = shift;
## We will be nice and ignore the case and any whitespace
$name =~ s{^\s*(\S+)\s*$}{lc $1}e;
## What is the maximum security_level that people who are
## calling this function can view?
my $seclevel = 'confidential';
## Query the table and pull back the matching row
## We need to differentiate between "not found" and "not allowed",
## by comparing a passed-in level to the security_level for that row.
my $SQL = q{
SELECT name,cost,description,
CASE WHEN security_level <= $1 THEN 1 ELSE 0 END AS allowed
FROM weapon
WHERE LOWER(name) = $2};
## Run the query, pull back the first row, as well as the allowed column value
my $sth = spi_prepare($SQL, 'CLASSIFICATION', 'TEXT');
my $rv = spi_exec_prepared($sth, $seclevel, $name);
my $row = $rv->{rows}[0];
my $allowed = delete $row->{allowed};
## Log this request
$SQL = 'INSERT INTO data_audit(tablename,arguments,results,status)
VALUES ($1,$2,$3,$4)';
my $status = $rv->{rows}[0] ? $allowed ? 'normal' : 'forbidden' : 'na';
$sth = spi_prepare($SQL, 'TEXT', 'TEXT', 'INTEGER', 'TEXT');
spi_exec_prepared($sth, 'weapon', $name, $rv->{processed}, $status);
## Did we find anything? If not, simply return undef
if (! $rv->{processed}) {
return undef;
}
## Throw an exception if we are not allowed to view this row
if (! $allowed) {
die qq{Sorry, you are not allowed to view information on that weapon!\n};
}
## Return the requested data
return_next($row);
$bc$;
However, this fails the case pointed out in the original poster's email about viewing the data within a transaction that is then rolled back. It also fails to work at all when a forbidden item is requested, as that insert is rolled back by the die() call:
postgres=> \c postgres alice
You are now connected to database "postgres" as user "alice".
postgres=> SELECT * FROM weapon_details('crowbar');
name | cost | description
---------+------+---------------
Crowbar | 10 | a fine weapon
(1 row)
postgres=> SELECT * FROM weapon_details('pulse rifle');
ERROR: Sorry, you are not allowed to view information on that weapon!
CONTEXT: PL/Perl function "weapon_details"
postgres=> BEGIN;
BEGIN
postgres=> SELECT * FROM weapon_details('m9');
name | cost | description
------+------+---------------
M9 | 200 | a fine weapon
(1 row)
postgres=> ROLLBACK;
ROLLBACK
postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=> SELECT * FROM data_audit \x \g
Expanded display is on.
-[ RECORD 1 ]----------------------------
tablename | weapon
arguments | crowbar
results | 1
status | normal
username | alice
txntime | 2012-01-30 17:37:39.497491-05
realtime | 2012-01-30 17:37:39.545891-05
How do we get around this? We need a way to commit something that will survive the surrounding transaction's rollback. The closest thing Postgres has to such a thing at the moment is to connect back to the database with a new and entirely separate connection. Two such popular ways to do so are with the dblink program and the PL/PerlU language. Obviously, we are going to focus on the latter, but all of this could be done with dblink as well. Here are the additional steps to connect back to the database, do the insert, and then leave again:
postgres=> CREATE OR REPLACE FUNCTION weapon_details(TEXT)
RETURNS TABLE (name TEXT, cost TEXT, description TEXT)
LANGUAGE plperlu
SECURITY DEFINER
VOLATILE
AS $bc$
use strict;
use warnings;
use DBI;
## The item they are looking for
my $name = shift;
## We will be nice and ignore the case and any whitespace
$name =~ s{^\s*(\S+)\s*$}{lc $1}e;
## What is the maximum security_level that people who are
## calling this function can view?
my $seclevel = 'confidential';
## Query the table and pull back the matching row
## We need to differentiate between "not found" and "not allowed",
## by comparing a passed-in level to the security_level for that row.
my $SQL = q{
SELECT name,cost,description,
CASE WHEN security_level <= $1 THEN 1 ELSE 0 END AS allowed
FROM weapon
WHERE LOWER(name) = $2};
## Run the query, pull back the first row, as well as the allowed column value
my $sth = spi_prepare($SQL, 'CLASSIFICATION', 'TEXT');
my $rv = spi_exec_prepared($sth, $seclevel, $name);
my $row = $rv->{rows}[0];
my $allowed = defined $row ? delete $row->{allowed} : 1;
## Log this request
$SQL = 'INSERT INTO data_audit(username,tablename,arguments,results,status)
VALUES (?,?,?,?,?)';
my $status = $rv->{rows}[0] ? $allowed ? 'normal' : 'forbidden' : 'na';
my $dbh = DBI->connect('dbi:Pg:service=auditor', '', '',
{AutoCommit=>0, RaiseError=>1, PrintError=>0});
$sth = $dbh->prepare($SQL);
my $user = spi_exec_query('SELECT session_user')->{rows}[0]{session_user};
$sth->execute($user, 'weapon', $name, $rv->{processed}, $status);
$dbh->commit();
## Did we find anything? If not, simply return undef
if (! $rv->{processed}) {
return undef;
}
## Throw an exception if we are not allowed to view this row
if (! $allowed) {
die qq{Sorry, you are not allowed to view information on that weapon!\n};
}
## Return the requested data
return_next($row);
$bc$;
CREATE FUNCTION
Note that because we are making external changes, we marked the function as VOLATILE, which ensures that it will always be run every time it is called, and not cached in any form. We are also using a Postgres service file with the 'db:Pg:service=auditor'. This means that the connection information (username, password, database) is contained in an external file. This is not only tidier than hard-coding those values into this function, but safer as well, as the function itself can be viewed by Alice. Finally, note that we are passing the 'username' directly into the function this time, as we have a brand new connection which is no longer linked to the 'alice' user, so we have to derive it ourselves from "SELECT session_user" and then pass it along.
Once this new function is in place, and we re-run the same queries as we did before, we see three entries in our audit table:
postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
Expanded display is on.
-[ RECORD 1 ]----------------------------
tablename | weapon
arguments | crowbar
results | 1
status | normal
username | alice
txntime | 2012-01-30 17:56:01.544557-05
realtime | 2012-01-30 17:56:01.54569-05
-[ RECORD 2 ]----------------------------
tablename | weapon
arguments | pulse rifle
results | 1
status | forbidden
username | alice
txntime | 2012-01-30 17:56:01.559532-05
realtime | 2012-01-30 17:56:01.561225-05
-[ RECORD 3 ]----------------------------
tablename | weapon
arguments | m9
results | 1
status | normal
username | alice
txntime | 2012-01-30 17:56:01.573335-05
realtime | 2012-01-30 17:56:01.574989-05
So that's the basic premise of how to solve the auditing problem. For an actual production script, you would probably want to cache the database connection by sticking things inside of the special %_SHARED hash available to PL/Perl and Pl/PerlU. Note that each user gets their own version of that hash, so Alice will not be able to create a function and have access to the same %_SHARED hash that the postgres user has access to. It's probably a good idea to simply not let users like Alice use the language at all. Indeed, that's the default when we do the CREATE LANGUAGE call as above:
postgres=> \c postgres alice
You are now connected to database "postgres" as user "alice".
postgres=> CREATE FUNCTION showplatform()
RETURNS TEXT
LANGUAGE plperlu
AS $bc$
return $^O;
$bc$;
ERROR: permission denied for language plperlu
Further refinements to the actual script might include refactoring the logging bits to a separate function, writing some of the auditing data to a file on the local disk, recording the actual results returned to the user, and sending the data to another Postgres server entirely. For that matter, as we are using DBI, you could send it to other place entirely - such as a MySQL, Oracle, or DB2 database!
Another place for improvement would be associating each user with a security_level classification, such that any user could run the function and only see things at or below their level, rather than hard-coding the level as "confidential" as we have done here. Another nice refinement might be to always return undef (no matches) for items marked "top secret", to prevent the very existence of a top secret weapon from being deduced. :)
Our SoftLayer API tools
We do a lot of our hosting at SoftLayer, which seems to be one of the hosts with the most servers in the world -- they claim to have over 100,000 servers as of last month. More important for us than sheer size are many other fine attributes that SoftLayer has, in no particular order:
- a strong track record of reliability
- responsive support
- datacenters around the U.S. and some in Europe and Asia
- solid power backup
- well-connected redundant networks with multiple 10 Gbps uplinks
- gigabit Ethernet pipes all the way to the Internet
- first-class IPv6 support
- an internal private network with no data transfer charge
- Red Hat Enterprise Linux offered at no extra charge
- diverse dedicated server offerings at many price & performance points
- some disk partitioning options (though more flexibility here would be nice, especially with LVM for the /boot and / filesystems)
- fully automated provisioning, without salesman & quote hassles for standard offerings
- 3000 GB data transfer per month included standard with most servers
- month-to-month contracts
- reasonable prices (though we can of course always use lower prices, we'll take quality over cheapness for most of our hosting needs!)
- no arbitrary port blocks (some other providers rate-limit incoming TCP connections on port 22 to slow down ssh dictionary attacks, while others forbid IRC, etc.)
- a web service API for monitoring and controlling many aspects of our account via REST/JSON or SOAP
(No, they're not paying me for writing this! But they really have nice offerings.)
It is this last item, the SoftLayer API, that I want to elaborate on here.
The SoftLayer Development Network features API information and documentation and once you have an API account set up in the management website (quick and easy to do), you can start automating all sorts of tasks, from provisioning new hosts, monitoring your upcoming invoice or other accounting information, and much more.
I've released as open source two scripts we use: One is for managing secondary DNS domains in SoftLayer's DNS servers, from a primary name server running BIND 9. The other is a Nagios check script for monitoring monthly data transfer used and alerting when over a set threshold or over the monthly allotment.
See the GitHub repository of endpoint-softlayer-api if they would be useful to you, or to use as a starting point to interface with other SoftLayer APIs.
Interchange loops using DBI Slice
One day I was reading through the documentation on search.cpan.org for the DBI module and ran across an attribute that you can use with selectall_arrayref() that creates the proper data structure to be used with Interchange's object.mv_results loop attribute. The attribute is called Slice which causes selectall_arrayref() to return an array of hashrefs instead of an array of arrays. To use this you have to be working in global Perl modules as Safe.pm will not let you use the selectall_arrayref() method.
An example of what you could use this for is an easy way to generate a list of items in the same category. Inside the module, you would do like this:
my $results = $dbh->selectall_arrayref(
q{
SELECT
sku,
description,
price,
thumb,
category,
prod_group
FROM
products
WHERE
category = ?},
{ Slice => {} },
$category
);
$::Tag->tmpn("product_list", $results);In the actual HTML page, you would do this:
<table cellpadding=0 cellspacing=2 border=1>
<tr>
<th>Image</th>
<th>Description</th>
<th>Product Group</th>
<th>Category</th>
<th>Price</th>
</tr>
[loop object.mv_results=`$Scratch->{product_list}` prefix=plist]
[list]
<tr>
<td><a href="/cgi-bin/vlink/[plist-param sku].html"><img src="[plist-param thumb]"></a></td>
<td>[plist-param description]</td>
<td>[plist-param prod_group]</td>
<td>[plist-param category]</td>
<td>[plist-param price]</td>
</tr>
[/list]
[/loop]
</table>We normally use this when writing ActionMaps and using some template as our setting for mv_nextpage.
Sanitizing supposed UTF-8 data
As time passes, it's clear that Unicode has won the character set encoding wars, and UTF-8 is by far the most popular encoding, and the expected default. In a few more years we'll probably find discussion of different character set encodings to be arcane, relegated to "data historians" and people working with legacy systems.
But we're not there yet! There's still lots of migration to do before we can forget about everything that's not UTF-8.
Last week I again found myself converting data. This time I was taking data from a PostgreSQL database with no specified encoding (so-called "SQL_ASCII", really just raw bytes), and sending it via JSON to a remote web service. JSON uses UTF-8 by default, and that's what I needed here. Most of the source data was in either UTF-8, ISO Latin-1, or Windows-1252, but some was in non-Unicode Chinese or Japanese encodings, and some was just plain mangled.
At this point I need to remind you about one of the most unusual aspects of UTF-8: It has limited valid forms. Legacy encodings typically used all or most of the 255 code points in their 8-byte space (leaving point 0 for traditional ASCII NUL). While UTF-8 is compatible with 7-bit ASCII, it does not allow any possible 8-bit byte in any position. See the Wikipedia summary of invalid byte sequences to know what can be considered invalid.
We had no need to try to fix the truly broken data, but we wanted to convert everything possible to UTF-8 and at the very least guarantee no invalid UTF-8 strings appeared in what we sent.
I previously wrote about converting a PostgreSQL database dump to UTF-8, and used the Perl CPAN module IsUTF8.
I was going to use that again, but looked around and found an even better module, exactly targeting this use case: Encoding::FixLatin, by Grant McLean. Its documentation says it "takes mixed encoding input and produces UTF-8 output" and that's exactly what it does, focusing on input with mixed UTF-8, Latin-1, and Windows-1252.
It worked as advertised, very well. We would need to use a different module to convert some other legacy encodings, but in this case this was good enough and got the vast majority of the data right.
There's even a standalone fix_latin program designed specifically for processing Postgres pg_dump output from legacy encodings, with some nice examples of how to use it.
One gotcha is similar to a catch that David Christensen reported with the Encode module in a blog post here about a year ago: If the Perl string already has the UTF-8 flag set, Encoding::FixLatin immediately returns it, rather than trying to process it. So it's important that the incoming data be a pure byte stream, or that you otherwise turn off the UTF-8 flag, if you expect it to change anything.
Along the way I found some other CPAN modules that look useful for cases where I need more manual control than Encoding::FixLatin gives:
- Search::Tools::UTF8 - test for and/or fix bad ASCII, Latin-1, Windows-1252, and UTF-8 strings
- Encode::Detect - use Mozilla's universal charset detector and convert to UTF-8
- Unicode::Tussle - ridiculously comprehensive set of Unicode tools that has to be seen to be believed
Once again Perl's thriving open source/free software community made my day!
Changing postgresql.conf from a script
The modify_postgres_conf script for Postgres allows you to change your postgresql.conf file from the command line, via a cron job, or any time when you want to automate the process.
Postgres runs as a background daemon. The configuration parameters it runs with are stored in a file named postgresql.conf. To change the behavior of Postgres, one must usually edit this file, and then tell Postgres that you have made the changes. Sometimes all that is needed is to 'HUP' or reload Postgres. Most changes fall into this category. Other changes require a full restart of Postgres, which entails disconnecting all current clients.
Thus, to make a change, one must edit the file, find the item to change (the file consists of "name = value" lines), change it, then send a signal to the main Postgres process so it picks up the change. Finally, you should then connect to Postgres to make sure it is still running and has accepted the latest change.
Doing this automatically (such as via a cron script) is very difficult. One method, if you are doing something simple like toggling between two known configuration files, is to simply store copies of both files and replace them, like this example cronjob:
30 10 * * * cp -f conf/postgresql.conf.1 /etc/postgresql.conf; /etc/init.d/postgresql reload
50 10 * * * cp -f conf/postgresql.conf.2 /etc/postgresql.conf; /etc/init.d/postgresql reload
The major problem with that approach, as I quickly learned when I tried it, is that despite nobody making changes to the postgresql.conf file in *years*, a few days after I put the above change in place, someone decided to edit postgresql.conf. At 10:30AM the next day, their changes were blown away. A better way is to simply write a program to make the change for you. Thus, the modify_postgres_conf.pl script.
The basic usage is to tell the script where the conf file is, and list what changes you want to make. Here's an example that will change the random_page_cost to 2 on a Debian system:
./modify_postgres_conf.pl --pgconf /etc/postgresql/9.0/main/postgresql.conf --change random_page_cost=2
Here is exactly what the script does for the above statement:
- For each item to be changed, we:
- Ask the database what the current value is (and die if that parameter does not exist)
- If the current and new value are the same, do nothing
- Otherwise, open (and flock) the configuration file and change the parameter
- If no changes were made, exit
- Otherwise, close the configuration file
- Figure out the Postgres PID and send it a HUP signal
- Reconnect to the database and confirm each change has taken effect
By default, it adds a comment after the changed value as well, to help in tracking down who made the change. A diff of the postgresql.conf file after running the example above produces:
diff -r1.1 postgresql.conf
499c499
< random_page_cost = 4
---
> random_page_cost = 2 ## changed by modify_postgres_conf.pl on Wed Aug 10 13:31:34 2011
The addition of the comment can be stopped by added a --no-comment argument. If the script runs successfully, it also returns two items of information: the size and name of the current Postgres log file. This is useful so you can know exactly where in the log this change took place. Note that this only works for items that are already explicitly set in your configuration file. However, as discussed before, you should already have all the items that you may possibly change explicitly listed out at the bottom of the file already. Whitespace is preserved as well, for those (like me) who like to keep things lined up neatly inside the file (see examples in the link above).
Here are some more examples of the script in action:
$ ./modify_postgres_conf.pl --pgconf /etc/postgresql/9.0/main/postgresql.conf --change random_page_cost=2
114991 /var/log/postgres/postgres-2011-08-10.log
$ ./modify_postgres_conf.pl --pgconf /etc/postgresql/9.0/main/postgresql.conf --change random_page_cost=2
No change made: value of "random_page_cost" is already 2
$ ./modify_postgres_conf.pl --pgconf /etc/postgresql/9.0/main/postgresql.conf \
> --change random_page_cost=2 \
> --change log_statement=ddl \
> --change log_min_duration_statement=100
No change made: value of "random_page_cost" is already 2
118459 /var/log/postgres/postgres-2011-08-10.log
$ ./modify_postgres_conf.pl --pgconf /etc/postgresql/9.0/main/postgresql.conf \
> --change default_statitics_target=200 --no-comment
There is no Postgres variable named "default_statitics_target"!
$ ./modify_postgres_conf.pl --pgconf /etc/postgresql/9.0/main/postgresql.conf \
> --change default_statistics_target=200 --no-comment
123396 /var/log/postgres/postgres-2011-08-10.log
Note that we make no attempt to automatically check changes in to version control: as you will see in an upcoming blog post on a real-life use case, such a checkin is usually not wanted, as we are making temporary changes.
This is a fairly simple Perl script, but I thought I would put it out there in the hopes of helping others out (and preventing the reinventing of wheels). Of course, if you find a bug or want to write a patch for it, those are welcome additions at any time! The code can be found on github:
git clone git://git@github.com:bucardo/modify_postgres_config.git
DevCamps news
DevCamps is a system for managing development, integration, staging, and production environments. It was developed by End Point for, and with the help of, some of our ecommerce clients. It grew over the space of several years, and really started to become its own standalone project in 2007.
Camps are a behind-the-scenes workhorse of our web application development at End Point, and don't always get much attention because everyone's too busy using camps to get work done! But this summer a few things are happening.
In early July we unveiled a redesign of the devcamps.org website that features a more whimsical look, a better explanation of what camps are all about, and endorsements by business and developer users. Marko Bijelic of Hipinspire did the design. Take a look:
In less than two weeks, on August 17, I'm going to be giving a talk on camps at YAPC::EU in Riga, Latvia. YAPC::EU is Europe's annual Perl conference, and will be a nice place to talk about camps.
Many Perl developers are doing web applications, which is camps' main focus, so that's reason enough. But camps also started around the Interchange application server, which is written in Perl. And the camp system is currently implemented in Perl as well.
We've set up a lot of camp systems for Perl web applications. So even though we've also set up camp systems for web applications using Ruby on Rails, Sinatra, Django, and PHP, it's a nice homecoming to talk about camps to Perl enthusiasts.
DBD::Pg UTF-8 for PostgreSQL server_encoding
We are preparing to make a major version bump in DBD::Pg, the Perl interface for PostgreSQL, from the 2.x series to 3.x. This is due to a reworking of how we handle UTF-8. The change is not going to be backwards compatible, but will probably not affect many people. If you are using the pg_enable_utf8 flag, however, you definitely need to read on for the details.
The short version is that DBD::Pg is going return all strings from the Postgres server with the Perl utf8 flag on. The sole exception will be databases in which the server_encoding is SQL_ASCII, in which case the flag will never be turned on.
For backwards compatibility and fine-tuning control, there is a new attribute called pg_utf8_strings that can be set at connection time to override the decision above. For example, if you need your connection to return byte-soup, non-utf8-marked strings, despite coming from a UTF-8 Postgres database, you can say:
my $dsn = 'dbi:Pg:dbname=foobar';
my $dbh = DBI->connect($dsn, $dbuser, $dbpass,
{ AutoCommit => 0,
RaiseError => 0,
PrintError => 0,
pg_utf8_strings => 0,
}
);
Similarly, you can set pg_utf8_strings to 1 and it will force settings returned strings as utf8, even if the backend is SQL_ASCII. You should not be using SQL_ASCII of course, and certainly not forcing the strings returned from it to UTF-8. :)
All Perl variables (be they strings or otherwise) are actually Perl objects, with some internal attributes defined on them. One of those is the utf8 flag, which can be flipped on to indicate that the string should be treated as possibly containing multi-byte characters, or it can be left off, to indicate the string should always be treated on a byte-by-byte basis. This will affect things like the Perl length function, and the Perl \w regex flag. This is completely unrelated to the Perl pragma use utf8, which DBD::Pg has nothing at all to do with. Have I mentioned that UTF-8, and UTF-8 in Perl in particular, can be quite confusing?
There are a few exceptions as to what things DBD::Pg will mark as utf8. Integers and other numbers will not, boolean values will not, and no bytea data will ever have the flag set. When in doubt, assume that it is set.
The old attribute, pg_enable_utf8, will be deprecated, and have no effect. We thought about re-using that but it seemed clearer and cleaner to simply create a new variable (pg_utf8_strings), as the behavior has significantly changed.
A beta version of DBD::Pg (2.99.9_1) with these changes has been uploaded to CPAN for anyone to experiment with. Right now, none of this is set in stone, but we did want to get a working version out there to start the discussion and see how it interacts with applications that were making use of the pg_enable_utf8 flag. You can web search for "dbdpg" and look for the "Latest Dev. Release", or jump straight to the page for DBD::Pg 2.99.9_1. The trailing underscore is a CPAN convention that indicates this is a development version only, and thus will not replace the latest production version (2.18.1 as of this writing).
As a reminder, DBD::Pg has switched to using git, so you can follow along with the development with:
git clone git://bucardo.org/dbdpg.git
There is also a commits mailing list you can join to receive notifications of commits as they are pushed to the main repo. To sign up, send an email to dbd-pg-changes-subscribe@perl.org.
MongoDB replication from Postgres using Bucardo
One of the features of the upcoming version of Bucardo (a replication system for the PostgreSQL RDBMS) is the ability to replicate data to things other than PostgreSQL databases. One of those new targets is MongoDB, a non-relational 'document-based' database. (to be clear, we can only use MongoDB as a target, not as a source)
To see this in action, let's setup a quick example, modified from the earlier blog post on running Bucardo 5. We will create a Bucardo instance that replicates from two Postgres master databases to a Postgres database target and a MongoDB instance target. We will start by setting up the prerequisites:
sudo aptitude install postgresql-server \
perl-DBIx-Safe \
perl-DBD-Pg \
postgresql-contrib
Getting Postgres up and running is left as an exercise to the reader. If you have problems, the friendly folks at #postgresql on irc.freenode.net will be able to help you out.
Now for the MongoDB parts. First, we need the server itself. Your distro may have it already available, in which case it's as simple as:
aptitude install mongodb
For more installation information, follow the links from the MongoDB Quickstart page. For my test box, I ended up installing from source by following the directions at the Building for Linux page.
Once MongoDB is installed, we will need to start it up. First, create a place for MongoDB to store its data, and then launch the mongodb process:
$ mkdir /tmp/mongodata
$ mongod --dbpath=/tmp/mongodata --fork --logpath=/tmp/mongo.log
all output going to: /tmp/mongo.log
forked process: 428
You can perform a quick test that it is working by invoking the command-line shell for MongoDB (named "mongo" of course) Use quit() to exit:
$ mongo
MongoDB shell version: 1.8.1
Fri Jun 10 12:45:00
connecting to: test
> quit()
$
The other piece we need is a Perl driver so that Bucardo (which is written in Perl) can talk to the MongoDB server. Luckily, there is an excellent one available on CPAN named 'MongoDB'. We started the MongoDB server before doing this step because the driver we will install needs a running MongoDB instance to pass all of its tests. The module has very good documentation available on its CPAN page. Installation may be as easy as:
$ sudo cpan MongoDB
If that did not work for you (case matters!), there are more detailed directions on the Perl Language Center page.
Our next step is to grab the latest Bucardo, install it, and create a new Bucardo instance. See the previous blog post for more details about each step.
$ git clone git://bucardo.org/bucardo.git
Initialized empty Git repository...
$ cd bucardo
$ perl Makefile.PL
Checking if your kit is complete...
Looks good
Writing Makefile for Bucardo
$ make
cp bucardo.schema blib/share/bucardo.schema
cp Bucardo.pm blib/lib/Bucardo.pm
cp bucardo blib/script/bucardo
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/bucardo
Manifying blib/man1/bucardo.1pm
Manifying blib/man3/Bucardo.3pm
$ sudo make install
Installing /usr/local/lib/perl5/site_perl/5.10.0/Bucardo.pm
Installing /usr/local/share/bucardo/bucardo.schema
Installing /usr/local/bin/bucardo
Installing /usr/local/share/man/man1/bucardo.1pm
Installing /usr/local/share/man/man3/Bucardo.3pm
Appending installation info to /usr/lib/perl5/5.10.0/i386-linux-thread-multi/perllocal.pod
$ sudo mkdir /var/run/bucardo
$ sudo chown $USER /var/run/bucardo
$ bucardo install
This will install the bucardo database into an existing Postgres cluster.
...
Installation is now complete.
Now we create some test databases and populate with pgbench:
$ psql -c 'create database btest1'
CREATE DATABASE
$ pgbench -i btest1
NOTICE: table "pgbench_branches" does not exist, skipping
...
creating tables...
10000 tuples done.
20000 tuples done.
...
100000 tuples done.
$ psql -c 'create database btest2 template btest1'
CREATE DATABASE
$ psql -c 'create database btest3 template btest1'
CREATE DATABASE
$ psql btest3 -c 'truncate table pgbench_accounts'
TRUNCATE TABLE
$ bucardo add db t1 dbname=btest1
Added database "t1"
$ bucardo add db t2 dbname=btest2
Added database "t2"
$ bucardo add db t3 dbname=btest3
Added database "t3"
$ bucardo list dbs
Database: t1 Status: active Conn: psql -p 5432 -U bucardo -d btest1
Database: t2 Status: active Conn: psql -p 5432 -U bucardo -d btest2
Database: t3 Status: active Conn: psql -p 5432 -U bucardo -d btest3
$ bucardo add tables pgbench_accounts pgbench_branches pgbench_tellers herd=therd
Created herd "therd"
Added table "public.pgbench_accounts"
Added table "public.pgbench_branches"
Added table "public.pgbench_tellers"
$ bucardo list tables
Table: public.pgbench_accounts DB: t1 PK: aid (int4)
Table: public.pgbench_branches DB: t1 PK: bid (int4)
Table: public.pgbench_tellers DB: t1 PK: tid (int4)
The next step is to add in our MongoDB instance. The syntax is the same as the "add db" above, but we also tell it the type of database, as it is not the default of "postgres". We will also assign an arbitrary database name, "btest1", the same as the others. Everything else (such as the port and host) is default, so all we need to say is:
$ bucardo add db m1 dbname=btest1 type=mongo
Added database "m1"
$ bucardo list dbs
Database: m1 Type: mongo Status: active
Database: t1 Type: postgres Status: active Conn: psql -p 5432 -U bucardo -d btest1
Database: t2 Type: postgres Status: active Conn: psql -p 5432 -U bucardo -d btest2
Database: t3 Type: postgres Status: active Conn: psql -p 5432 -U bucardo -d btest3
Next we group our databases together and assign them roles:
$ bucardo add dbgroup tgroup t1:source t2:source t3:target m1:target
Created database group "tgroup"
Added database "t1" to group "tgroup" as source
Added database "t2" to group "tgroup" as source
Added database "t3" to group "tgroup" as target
Added database "m1" to group "tgroup" as target
Note that "target" is the default action, so we could shorten that to:
$ bucardo add dbgroup tgroup t1:source t2 t3 m1
However, I think it is best to be explicit, even if it does (incorrectly) hint that m1 could be anything *other* than a target. :)
We are almost ready to go. The final step is to create a sync (a basic replication event in Bucardo), then we can start up Bucardo, put some test data into the master databases, and 'kick' the sync:
$ bucardo add sync mongotest herd=therd dbs=tgroup ping=false
Added sync "mongotest"
$ bucardo start
Checking for existing processes
Starting Bucardo
$ pgbench -t 10000 btest1
starting vacuum...end.
transaction type: TPC-B (sort of)
number of transactions actually processed: 10000/10000
...
tps = 503.300595 (excluding connections establishing)
$ pgbench -t 10000 btest2
number of transactions actually processed: 10000/10000
...
tps = 408.059368 (excluding connections establishing)
$ bucardo kick mongotest
We'll give it a few seconds to replicate those changes (it took 18 seconds on my test box), and then check the output of bucardo status:
$ bucardo status
PID of Bucardo MCP: 3317
Name State Last good Time Last I/D/C Last bad Time
===========+========+============+=======+=============+===========+=======
mongotest | Good | 21:57:47 | 11s | 6/36234/898 | none |
Looks good, but what about the data in MongoDB? Let's get some counts from the Postgres masters and slave, and then look at the data inside MongoDB with the mongo command-line client:
$ psql btest1 -c 'SELECT count(*) FROM pgbench_accounts'
100000
$ psql btest2 -c 'SELECT count(*) FROM pgbench_accounts'
100000
$ psql btest3 -c 'SELECT count(*) FROM pgbench_accounts'
18106
$ psql btest1 -qc 'SELECT min(abalance),max(abalance) FROM pgbench_accounts'
-12071 | 13010
$ psql btest2 -qc 'SELECT min(abalance),max(abalance) FROM pgbench_accounts'
-12071 | 13010
$ psql btest3 -qc 'SELECT min(abalance),max(abalance) FROM pgbench_accounts'
-12071 | 13010
$ mongo btest1
MongoDB shell version: 1.8.1
Fri Jun 10 12:46:00
connecting to: btest1
> show collections
bucardo_status
pgbench_accounts
pgbench_branches
pgbench_tellers
system.indexes
> db.pgbench_accounts.count()
18106
> db.pgbench_accounts.find().sort({abalance:1}).limit(1).next()
{
"_id" : ObjectId("4df39bcb8795839660001de5"),
"abalance" : -12071,
"aid" : 84733,
"bid" : 1,
"filler" : " "
}
> db.pgbench_accounts.find().sort({abalance:-1}).limit(1).next()
{
"_id" : ObjectId("4df39bd08795839660002fb0"),
"abalance" : 13010,
"aid" : 45500,
"bid" : 1,
"filler" : " "
}
Why the difference in counts? We only started replicating after we populated the Postgres tables on the master databases with 100,000 rows, so the eighteen thousand is the number of rows that was changed during the subsequent pgbench run. (Note that pgbench uses randomness, so your numbers will be different than the above). In the future Bucardo will support the "onetimecopy" feature for MongoDB, but until then we can fully populate the pgbench_accounts collection simply by "touching' all the records on one of the masters:
$ psql btest1 -c 'UPDATE pgbench_accounts SET aid=aid'
UPDATE 100000
$ bucardo kick mongotest
Kicked sync mongotest
$ echo 'db.pgbench_accounts.count()' | mongo btest1
MongoDB shell version: 1.8.1
Fri Jun 10 12:47:00
connecting to: btest1
> 100000
> bye
A nice feature of MongoDB is its autovivification ability (aka dynamic schemas), which means unlike Postgres you do not have to create your tables first, but can simply ask MongoDB to do an insert, and it will create the table (or, in mongospeak, the collection) automatically for you.
Because MongoDB has no concept of transactions, and because Bucardo does not update, but does deletes plus inserts (for reasons I'll not get into today), there is one more trick Bucardo does when replicating to a MongoDB instance. A collection named 'bucardo_status' is created and updated at the start and the end of a sync (a replication event). Thus, your application can pause if it sees this table has a 'started' value, and wait until it sees 'complete' or 'failed'. Not foolproof by any means, but better than nothing :) You should, of course, carefully consider the way your app and Bucardo will coordinate things.
Feedback from Postgres or MongoDB folk is much appreciated: there are probably some rough edges, but as you can see from above, the basics are there are working. Feel free to email the bucardo-general mailing list or make a feature request / bug report on the Bucardo Bugzilla page.
Bucardo multi-master for PostgreSQL
The original BucardoThe next version of Bucardo, a replication system for Postgres, is almost complete. The scope of the changes required a major version bump, so this Bucardo will start at version 5.0.0. Much of the innards was rewritten, with the following goals:
Multi-master support
Where "multi" means "as many as you want"! There are no more pushdelta (master to slaves) or swap (master to master) syncs: there is simply one sync where you tell it which databases to use, and what role they play. See examples below.
Ease of use
The bucardo program (previously known as 'bucardo_ctl') has been greatly improved, making all the administrative tasks such as adding tables, creating syncs, etc. much easier.
Performance
Much of the underlying architecture was improved, and sometimes rewritten, to make things go much faster. Most striking is the difference between the old multi-master "swap syncs" and the new method, which has been described as "orders of magnitudes" faster by early testers. We use async database calls whenever possible, and no longer have the bottleneck of a single large bucardo_delta table.
Improved logging
Not only are more details provided, there is now the ability to control how verbose the logs are. Just set the log_level parameter to terse, normal, verbose, or debug. Those who had busy systems, which was the equivalent of a 'debug' firehose, will really appreciate this.
Different targets
Who says your slave (target) databases need to be Postgres? In addition to the ability to write text SQL files (for say, shipping to a different system), you can have Bucardo push to other systems as well. Stay tuned for more details on this. (Update: there is a blog post about using MongoDB as a target)
This new version is not quite at beta yet, but you can try out a demo of multi-master on Postgres quie easily. Let's see if we can do it in ten steps.
I. Download all prerequisites
To run Bucardo, you will need a Postgres database (obviously), the DBIx::Safe module, the DBI and DBD::Pg modules, and (for the purposes of this demo) the pgbench utility. Systems vary, but on aptitude-based systems, one can grab all of the above like this:
aptitude install postgresql-server \
perl-DBIx-Safe \
perl-DBD-Pg \
postgresql-contrib
II. Grab the latest Bucardo
git clone git://bucardo.org/bucardo.git
III. Install the program
cd bucardo
perl Makefile.PL
make
sudo make install
You can ignore any errors that come up about ExtUtils::MakeMaker not being recent.
IV. Setup an instance of Bucardo
This step assumes there is a running Postgres available to connect to.
sudo mkdir /var/run/bucardo
sudo chown $USER /var/run/bucardo
bucardo install
V. Use the pgbench program to create some test tables
psql -c 'CREATE DATABASE btest1'
pgbench -i btest1
psql -c 'CREATE DATABASE btest2 TEMPLATE btest1'
psql -c 'CREATE DATABASE btest3 TEMPLATE btest1'
psql -c 'CREATE DATABASE btest4 TEMPLATE btest1'
psql -c 'CREATE DATABASE btest5 TEMPLATE btest1'
VI. Tell Bucardo about the databases and tables you are going to use
bucardo add db t1 dbname=btest1
bucardo add db t2 dbname=btest2
bucardo add db t3 dbname=btest3
bucardo add db t4 dbname=btest4
bucardo add db t5 dbname=btest5
bucardo list dbs
bucardo add table pgbench_accounts pgbench_branches pgbench_tellers herd=therd
bucardo list tables
A herd is simply a logical grouping of tables. We did not add the other pgbench table, pgbench_history, because it has no primary key or unique index.
VII. Group the databases together and set their roles
bucardo add dbgroup tgroup t1:source t2:source t3:source t4:source t5:target
We've grouped all five databases together, and made four of them masters (aka source), and one of them a slave (aka target). You can any combination of master and slaves you want, as long as there is at least one master.
VII. Create the Bucardo sync
bucardo add sync foobar herd=therd dbs=tgroup ping=false
Here we simply create a new sync, which is a controllable replication event, telling it which tables we want to replicate, and which databases we are going to use. We also set ping to false, which means that we will not create triggers to automatically fire off replication on any changes, but will do it manually. In a real world scenario, you generally do want those triggers, or want to set Bucardo to check periodically.
VIII. Start up Bucardo
bucardo start
If all went well, you should see some information in the log.bucardo file in the current directory.
IX. Make a bunch of changes on all the source databases.
pgbench -t 10000 btest1
pgbench -t 10000 btest2
pgbench -t 10000 btest3
pgbench -t 10000 btest4
Here, we've told pgbench to run ten thousand transactions against each of the first four databases. Triggers on these tables have captured the changes.
X. Kick off the sync and watch the fun.
bucardo kick foobar
You can now tail the log.bucardo file to see the fun, or simply run:
bucardo status
...to see what it is doing, and the final counts when we are done. Don't forget to stop Bucardo when you are done testing:
bucardo stop
The output of bucardo status, after the sync has completed, should look like this:
bucardo status Name State Last good Time Last I/D/C Last bad Time ========+========+============+=======+====================+===========+======= foobar | Good | 17:58:37 | 3m2s | 131836/131836/4785 | none |
Here we see that this syncs has never failed ("Last bad"), the time of day of the last good run, how long ago it was from right now (3 minutes and 2 seconds), as well as details of the last successful run. Last I/D/C stands for number of inserts, deletes, and collisions across all databases for this syncs. This is just an overview of all syncs at a high level, but we can also give status an argument of a sync name to see more details like so:
bucardo status foobar Last good : Jun 02, 2011 17:57:47 (time to run: 42s) Rows deleted/inserted/conflicts : 131,836 / 131,836 / 4,785 Sync name : foobar Current state : Good Source herd/database : therd / t1 Tables in sync : 3 Status : active Check time : none Overdue time : 00:00:00 Expired time : 00:00:00 Stayalive/Kidsalive : yes / yes Rebuild index : 0 Ping : no Onetimecopy : 0 Post-copy analyze : Yes Last error: :
This gives us a little more information about the sync itself, as well as another important metric, how long the sync itself took to run, in this case, 42 seconds. That particular metric might make its way back to the overall "status" view above. Try things out and help us find bugs and improve Bucardo!
DBD::Pg and the libpq COPY bug
(image by kvanhorn)Version 2.18.1 of DBD::Pg, the Perl driver for Postgres, was just released. This was to fix a serious bug in which we were not properly clearing things out after performing a COPY. The only time the bug manifested, however, is if an asynchronous query was done immediately after a COPY finished. I discovered this while working on the new version of Bucardo. The failing code section was this (simplified):
## Prepare the source
my $srccmd = "COPY (SELECT * FROM $S.$T WHERE $pkcols IN ($pkvals)) TO STDOUT";
$fromdbh->do($srccmd);
## Prepare each target
for my $t (@$todb) {
my $tgtcmd = "COPY $S.$T FROM STDIN";
$t->{dbh}->do($tgtcmd);
}
## Pull a row from the source, and push it to each target
while ($fromdbh->pg_getcopydata($buffer) >= 0) {
for my $t (@$todb) {
$t->{dbh}->pg_putcopydata($buffer);
}
}
## Tell each target we are done with COPYing
for my $t (@$todb) {
$t->{dbh}->pg_putcopyend();
}
## Later on, run an asynchronous command on the source database
$sth{track}{$dbname}{$g} = $fromdbh->prepare($SQL, {pg_async => PG_ASYNC});
$sth{track}{$dbname}{$g}->execute();
This gave the error "another command is already in progress". This error did not come from Postgres or DBD::Pg, but from libpq, the underlying C library which DBD::Pg uses to talk to the database. Strangely enough, taking out the async part and running the exact same command produced no errors.
After tracking back through the libpq code, it turns out that DBD::Pg was only calling PQresult a single time after the copy ended. I can see why this was done: the docs for PQputCopyEnd state: "After successfully calling PQputCopyEnd, call PQgetResult to obtain the final result status of the COPY command. One can wait for this result to be available in the usual way. Then return to normal operation." What's not explicitly stated is that you need call PQgetResult again, and keep calling it, until it returns null, to "clear out the message queue". In this case, PQresult pulled back a 'c' message from Postgres, via the frontend/backend protocol, indicating that the copy command was complete. However, what it really needed was to call PQresult two more times, once to get back a 'C' (indicating the COPY statement was complete), and a 'Z' (indicating the backend was ready for a new query). Technically, there was nothing stopping libpq from sending a fresh query except that its own internal flag, conn->asyncStatus, is not reset on a simple end of copy, but only when 'Z' is encountered. Thus, DBD::Pg 2.18.1 now calls PQresult until it returns null.
If your application is encountering this bug and you cannot upgrade to 2.18.1 yet, the solution is simple: perform a non-asynchronous query between the end of the copy and the start of the asynchronous query. It can be any query at all, so the above code could be cured with:
...
## Tell each target we are done with COPYing
for my $t (@$todb) {
$t->{dbh}->pg_putcopyend();
$t->{dbh}->do('SELECT 123');
}
## Later on, run an asynchronous command on the source database
$fromdbh->do('SELECT 123');
$sth{track}{$dbname}{$g} = $fromdbh->prepare($SQL, {pg_async => PG_ASYNC});
$sth{track}{$dbname}{$g}->execute();
Why does the non-asynchronous command work? Doesn't it check the conn->asyncStatus as well? The secret is that PQexecstart has this bit of code in it:
/*
* Silently discard any prior query result that application didn't eat.
* This is probably poor design, but it's here for backward compatibility.
*/
while ((result = PQgetResult(conn)) != NULL)
Wow, that code looks familiar! So it turns out that the only reason this was not spotted earlier is that non-asynchronous commands (e.g. those using PQexec) were silently clearing out the message queue, kind of as a little favor from libpq to the driver. The async function, PQsendQuery, is not as nice, so it does the correct thing and fails right away with the error seen above (via PQsendQueryStart).
Benchmarking in Perl: Map versus For Loop
Last week, I was coding in Perl for an Interchange project. I've been in and out of Perl and Ruby a lot lately. While I was working on the project, I came across the following bit of code and wanted to finally sit down and figure out how to use the map function in Perl on this bit of code.
my @options;
for my $obj (@$things) {
push @options, {
value => $obj->{a},
label => $obj->{b}
};
}
return \@options;
I'm a big fan of Ruby's inject method and in general a fan of the Enumerable Module, but I have a brain block when it comes to using the map method in both Perl and Ruby. I spent a little time investigating and working on a small local Perl script to test the implementation of the map method. I came up with the following:
return [ map {
{
value => $_->{a},
label => $_->{b}
}
} @$things ];
After that, I wanted to make sure the code change was justified. The Interchange application that is the source of this code is built for performance, so I wanted to ensure this change didn't hinder performance. It's been a while since I've done benchmarking in Perl, so I also had to refresh my memory regarding using the Benchmark module. I came up with:
#!/usr/bin/perl
use Benchmark;
my $count = 1000000;
my $things = [
{'a' => 123, 'b' => 456, 'c' => 789 },
{'a' => 456, 'b' => 789, 'c' => 123 }
];
#Test definitions as methods to mimic use in application
my $test1 = sub {
my @options;
for my $obj (@$things) {
push @options, {
value => $obj->{a},
label => $obj->{b}
};
}
return \@options;
};
my $test2 = sub {
return [ map {
{
value => $_->{a},
label => $_->{b}
}
} @$things ];
};
#Benchmark tests & results.
$t0 = Benchmark->new;
$test1->() for(1..$count);
$t1 = Benchmark->new;
$td = timediff($t1, $t0);
print "the code for test 1 took:",timestr($td),"\n";
$t0 = Benchmark->new;
$test2->() for(1..$count);
$t1 = Benchmark->new;
$td = timediff($t1, $t0);
print "the code for test 2 took:",timestr($td),"\n";
The results were:
| Test # | Before (For Loop) | After (Map) |
| 1 | 5 sec | 4 sec |
| 2 | 5 sec | 4 sec |
| 3 | 5 sec | 5 sec |
| 4 | 5 sec | 5 sec |
| 5 | 6 sec | 4 sec |
| 6 | 6 sec | 4 sec |
| 7 | 6 sec | 4 sec |
| 8 | 5 sec | 5 sec |
| 9 | 5 sec | 4 sec |
| 10 | 5 sec | 4 sec |
| Average | 5.3 sec | 4.3 sec |
In this case, replacing the imperative programming style here with Functional programming (via map) yielded a small performance improvement, but the script executed each method 1,000,000 times, so the performance gain yielded by just one method call is very small. I doubt it's worth it go on a code cleanup rampage to update and test this, but it's good to keep in mind moving forward as small bits of the code are touched. I also wonder if the performance will vary when the size of $things changes — something I didn't test here. It was nice to practice using Perl's map method and Benchmark module. Yippee.
Postgres query caching with DBIx::Cache
A few years back, I started working on a module named DBIx::Cache which would add a caching layer at the database driver level. The project that was driving it got put on hold indefinitely, so it's been on my long-term todo list to release what I did have to the public in the hope that someone else may find it useful. Hence, I've just released version 1.0.1 of DBIx::Cache. Consider it the closest thing Postgres has at the moment for query caching. :) The canonical webpage:
http://bucardo.org/wiki/DBIx-Cache
You can also grab it via git, either directly:
git clone git://bucardo.org/dbixcache.git/
or through the indispensable github:
https://github.com/bucardo/dbixcache
So, what does it do exactly? Well, the idea is that certain queries that are either repeated often and/or are very expensive to run should be cached somewhere, such that the database does not have to redo all the same work, just to return the same results over and over to the client application. Currently, the best you can hope for with Postgres is that things are in RAM from being run recently. DBIx::Cache changes this by caching the results somewhere else. The default destination is memcached.
DBIx::Cache acts as a transparent layer around your DBI calls. You can control which queries, or classes of queries get cached. Most of the basic DBI methods are overridden so that rather than query Postgres, they actually query memcached as needed (or other caching layer - could even query back into Postgres itself!). Let's look at a simple example:
use strict;
use warnings;
use Data::Dumper;
use DBIx::Cache;
use Cache::Memcached::Fast;
## Connect to an existing memcached server,
## and establish a default namespace
my $mc = Cache::Memcached::Fast->new(
{
servers => [ { address => 'localhost:11211' } ],
namespace => 'joy',
});
## Rather than DBI->connect, use DBIx->connect
## Tell it what to use as our caching source
## (the memcached server above)
my $dbh = DBIx::Cache->connect('', '', '',
{ RaiseError => 1,
dxc_cachehandle => $mc
});
## This is an expensive query, that takes 30 seconds to run:
my $SQL = 'SELECT * FROM analyze_sales_data()';
## Prepare this query
my $sth = $dbh->prepare($SQL);
## Run it ten times in a row.
## The first time takes 30 seconds, the other nine return instantly.
for (1..10) {
my $count = $sth->execute();
my $info = $sth->fetchall_arrayref({});
print Dumper $info;
}
In the above, the prepare($SQL) is actually calling the DBIx::Class::prepare method. This parses the query and tries to determine if it is cacheable or not, then stores that decision internally. Regardless of the result, it calls DBI::prepare (which is techincally DBD::Pg::prepare), and returns the result.The magic comes in the call to execute() later on. As you might imagine, this is also actually the DBIx::Class::execute() method. If the query is not cacheable, it simply runs it as normal and returns. If it is cacheable, and this is the first time it is run, DBIx::Class runs an EXPLAIN EXECUTE on the original statement, and parses out a list of all tables that are used in this query. Then it caches all of this information into memcached, so that subsequent runs using the same list of arguments to execute() don't need to do that work again.
Finally, we come to fetchall_arrayref(). The first time it is run, we simply call the parent methods and get the data back. Then we build unique keys and store the results of the query into memcached. Finally, we mark the execute() as fully cached. Thus, on subsequent calls to execute(), we don't actually execute anything on the database server, but simply return the count as stashed inside of memcached (in the case of execute, this is the number of affected rows). For the various fetch() methods, we do the same thing - rather than fetch things from the database (via DBI, DBD::Pg, and libpq), we get the results from memcached (frozen via Data::Dumper), and then unpack and return them. Since we don't actually need to do any work against the database, everything returns as fast as we can query memcached - which is in general very fast indeed.
Most of the above is working, but the piece that is not written is the cache invalidation. DBIx::Cache knows which tables go to which queries, so in theory you could have (for example), an UPDATE/INSERT/DELETE trigger on table X which calls DBIx::Cache and tells it to invalidate all items related to table X, so that the next call to prepare() or execute() or fetch() will not find any memcached matches and re-run the whole query and store the results. You could also simply handle that in your application, of course, and have it decide when to invalidate items.
It's been a while since I've really looked at the code, but as far as I can tell it is close to being able to actually use somewhere. :) Patches and questions welcome!
Use ZIP+4, except when you shouldn't
The USPS provides a handy API for looking up postal rates on the fly. Recently it started failing for code that had been working for a while, so I investigated. I found a couple of different problems with it:
- First, the "service description" field had been "augmented" by including copyright symbols via HTML mark-up. That meant internal comparisons started to fail, so I "canonicalized" all the responses by stripping out various things from both sides of my comparison.
$string =~ s{&(?:[a-z/;&])+}{}gis; $string =~ s/[^a-z]//gis; $string =~ s/^\s+//; $string =~ s/\s+$//; $string =~ s/\s+/ /gis; - Second, I found that the API inexplicably rejects 9-digit ZIP codes, the "ZIP+4" format. That's right, you can't look up a domestic shipping rate for a 9-digit ZIP. The documentation linked above specifically calls for 5-digit ZIPs. If you pass a 9-digit ZIP to the API, it doesn't smartly recognize that you've given it too much info and just use what it needs. Instead, it throws an error.
So the API got too clever in one regard, and not clever enough where it counts.
DBD::Pg query cancelling in Postgres
A new version of DBD::Pg, the Perl driver for PostgreSQL, has just been released. In addition to fixing some memory leaks and other minor bugs, this release (version 2.18.0) introduces support for the DBI method known as cancel(). A giant thanks to Eric Simon, who wrote this new feature. The new method is similar to the existing pg_cancel() method, except it works on synchronous rather than asynchronous queries. I'll show an example of both below.
DBD::Pg has been able to handle asynchronous queries for a while now. Basically, that means you don't have to wait around for the database to finish a query. Your application can do other things while the query runs, then check back later to see if it has completed and grab the results. The way to cancel an already kicked-off asynchronous query is with the pg_cancel() method (the other asynchronous methods are pg_ready and pg_result, which have no synchronous equivalents).
The prefix "pg_" is used because there is no corresponding built-in DBI method to override, and the convention is to prefix everything custom to a driver with the driver's prefix, in our case 'pg'. Here's an example showing one possible use of asynchronous queries using DBD::Pg in some Perl code:
## We are connecting to two servers and running expensive
## queries on both. We kick both off right away, then wait
## for them both to finish. Our total wait time is thus
## max(server1,server2) rather than sum(server1,server2)
use strict;
use warnings;
use DBI;
use DBD::Pg qw{ :async };
my $dsn1 = 'dbi:Pg:dbname=sales;host=example1.com';
my $dsn2 = 'dbi:Pg:dbname=sales;host=example2.com';
my $dbh1 = DBI->connect($dsn1, '', '', {AutoCommit=>0, RaiseError=>1});
my $dbh2 = DBI->connect($dsn2, '', '', {AutoCommit=>0, RaiseError=>1});
my $SQL = 'SELECT gather_yearly_sales_data()';
print "Kicking off a long, expensive query on database one\n";
## Normally, a do() will not return until the query is complete
## However, the async flag causes it to return immediately
$dbh1->do($SQL, {pg_async => PG_ASYNC});
print "Kicking off a long, expensive query on database two\n";
$dbh2->do($SQL, {pg_async => PG_ASYNC});
## Both queries are running in the 'background'
## We have to wait for both, so it doesn't matter which one we wait for here
## However, if it's been over 2 minutes, we'll cancel both and quit
my $time = 0;
while ( ! $dbh1->pg_ready() ) {
sleep 1;
if ($time++ > 120) {
print "Taking too long, let's cancel the queries\n";
$dbh1->pg_cancel();
$dbh2->pg_cancel();
$dbh1->rollback();
$dbh2->rollback();
die "No sales data was retrieved\n";
}
}
## We know that database 1 has finished, so we read in the results
my $rows1 = $dbh1->pg_result();
## We then grab results from database 2
## This will block until done, which is okay
my $rows2 = $dbh2->pg_result();
The new method, simply known as cancel(), will kill any synchronously running query. One of the main uses for this is to timeout a query by using the builtin Perl alarm function. However, since the builtin alarm function has some quirks, we will instead use the much safer POSIX::SigAction method. Another example:
## We are running a series of queries against a database, but if
## the whole thing is taking over 30 seconds, we want to cancel
## the currently running query and move on to something else.
use strict;
use warnings;
use DBI;
use DBD::Pg qw{ :async };
my $dsn = 'dbi:Pg:dbname=dq';
my $dbh = DBI->connect($dsn, '', '', {AutoCommit=>0, RaiseError=>1});
## Setup all the POSIX alarm plumbing
my $mask = POSIX::SigSet->new(SIGALRM);
my $action = POSIX::SigAction->new(
sub { die "TIMEOUT\n" },
$mask,
);
my $oldaction = POSIX::SigAction->new();
sigaction( SIGALRM, $action, $oldaction );
## Prepare the queries
my $upd = $dbh->prepare('UPDATE foobar SET x=? WHERE y=?');
my $inv = $dbh->prepare('SELECT refresh_inventory(?)');
## Yes, a double eval. Async is looking better all the time :)
eval {
eval {
alarm 30;
for my $y (12,24,48) {
print "Adjusting widget #$y\n";
$upd->execute(555,$y);
print "Recalculating inventory\n";
$inv->execute($y);
}
};
alarm 0; ## Turn off our alarm
die "$@\n" if $@; ## Bubble the error to the outer eval
};
if ($@) { ## Something went wrong
if ($@ =~ /TIMEOUT/) {
print "Queries are taking too long! Cancelling\n";
## We don't know which one is still running, and don't care
## It's safe to cancel a non-active statement handle
$upd->cancel() or die qq{Failed to cancel the query!\n};
$inv->cancel() or die qq{Failed to cancel the query!\n};
$dbh->rollback();
die "Who has time to wait 30 seconds anymore?";
}
## Some other non-alarm error, so we simply:
die $@;
}
print "Updates are complete\n";
$dbh->commit();
exit;
Got an interesting use case for asynchronous queries or the new $dbh‑>cancel()? Let me know!
Managing Perl environments with perlbrew
As a Perl hobbyist, I've gotten used to the methodical evolution of Perl 5 over the years. Perl has always been a reliable language, not without its faults, but with a high level of flexibility in syntactical expression and even deployment options. Even neophytes quickly learn how to install their own Perl distribution and CPAN libraries in $HOME. But the process can become unwieldy, particularly if you want to test across a variety of Perl versions.
To contrast, Ruby core development frequently experiences ABI breakages, even between minor releases. In spite of the wide adoption of Ruby as a Web development language (thanks to Ruby on Rails), Ruby developers are able to plod along unconcerned, where these incompatibilities would almost certainly lead to major bickering within the Perl or PHP communities. How do they do it? The Ruby Version Manager.
Ruby Version Manager (RVM) allows users to install Ruby and RubyGems within their own self-contained environment. This allows each user to install all (or only) the software that their particular application requires. Particularly for Ruby developers, this provides them with the flexibility to quickly test upgrades for regressions, ABI changes and enhancements without impacting system-wide stability. Thankfully a lot of the ideas in RVM have made their way over to the Perl landscape, in the form of perlbrew.
Perlbrew offers many of the same features found in RVM for Ruby. It's easy to install. It isolates different Perl versions and CPAN installations in your $HOME and helps you switch between them. It automates your environment setup and teardown. And most importantly, using perlbrew means not having to clutter your default system Perl with application-specific CPAN dependencies.
Getting started with perlbrew couldn't be easier. A quick one-liner is all it takes to install perlbrew in your home directory.
$ curl -L http://xrl.us/perlbrewinstall | bash
If you need to install perlbrew somewhere other than your home directory, just download the installer and pass it the PERLBREW_ROOT environment variable.
$ curl -LO http://xrl.us/perlbrew $ chmod +x perlbrew $ PERLBREW_ROOT=/mnt/perlbrew ./perlbrew install
Follow the instructions on screen and you'll be ready to use perlbrew in no time. The perlbrew binary will be installed in ~/perl5/perlbrew/bin, so make sure to adjust your login $PATH accordingly.
Once you're done installing perlbrew there are a couple commands you'll want to run before installing your own Perl versions or CPAN modules. The perlbrew init command is mandatory; this initializes your perlbrew directory. It can also be used later if you need to modify your PERLBREW_ROOT setting. The perlbrew mirror is optional (but recommended) to help you select a preferred CPAN mirror.
$ perlbrew init $ perlbrew mirror
Next comes the fun part. Start off by verifying the Perl version(s) that perlbrew sees.
$ perlbrew list * /usr/bin/perl (5.10.1)
Install a newer version of Perl.
$ perlbrew install 5.12.3
Now switch to the newer Perl.
$ perlbrew list * /usr/bin/perl (5.10.1) perl-5.12.3 $ perlbrew switch perl-5.12.3 $ perlbrew list /usr/bin/perl (5.10.1) * perl-5.12.3 $ perl -v This is perl 5, version 12, subversion 3 (v5.12.3) built for x86_64-linux Copyright 1987-2010, Larry Wall Perl may be copied only under the terms of either the Artistic License or the GNU General Public License, which may be found in the Perl 5 source kit. Complete documentation for Perl, including FAQ lists, should be found on this system using "man perl" or "perldoc perl". If you have access to the Internet, point your browser at http://www.perl.org/, the Perl Home Page.
Alternatively, if you only want to test a different Perl version, try the perlbrew use command (note: this only works in bash and zsh). Unlike the switch command, use is only active for the current shell.
$ perlbrew use system $ perlbrew list * /usr/bin/perl (5.10.1) perl-5.12.3
A quick peek behind the curtain reveals much of the simplicity behind perlbrew.
$ ls -l ~/perl5/perlbrew/ total 2680 -rw-r--r-- 1 testy users 408 Feb 10 23:58 Conf.pm drwxr-xr-x 2 testy users 512 Feb 10 23:46 bin drwxr-xr-x 4 testy users 512 Feb 11 09:59 build -rw-r--r-- 1 testy users 1333196 Feb 11 10:33 build.log drwxr-xr-x 2 testy users 512 Feb 11 09:59 dists drwxr-xr-x 2 testy users 512 Feb 10 23:47 etc drwxr-xr-x 4 testy users 512 Feb 11 10:32 perls $ ls -l ~/perl5/perlbrew/perls/ total 8 drwxr-xr-x 5 testy users 512 Feb 11 00:38 perl-5.12.3 drwxr-xr-x 5 testy users 512 Feb 11 10:32 perl-5.13.6
If you're a Perl developer, the perlbrew project may help alleviate a lot of the pain associated with team development or multi-tenant programming environments. Suddenly it becomes much easier to manage your own software requirements, resulting in faster development and testing cycles for you, and fewer headaches for your System Administrators.
JSON pretty-printer
The other day Sonny and I were troubleshooting some YUI JavaScript code and looking at some fairly complex JSON. It would obviously be a lot easier to read if each nested data structure were indented, and spacing standardized.
I threw together a little Perl program based on the JSON man page:
It took all of 2 or 3 minutes and I even left out strictures and warnings. Living on the edge!
It turns a mess like this (sample from json.org):
{"glossary":{"title":"example glossary","GlossDiv":{"title":"S","GlossList":
{"GlossEntry":{"ID":"SGML","SortAs":"SGML","GlossTerm":"Standard Generalized Markup Language",
"Acronym":"SGML","Abbrev":"ISO 8879:1986","GlossDef":{"para":
"A meta-markup language,used to create markup languages such as DocBook.",
"GlossSeeAlso":["GML","XML"]},"GlossSee":"markup"}}}}}
into this much more readable specimen:
{
"glossary" : {
"GlossDiv" : {
"GlossList" : {
"GlossEntry" : {
"GlossDef" : {
"para" : "A meta-markup language,used to create markup languages such as DocBook.",
"GlossSeeAlso" : [
"GML",
"XML"
]
},
"GlossTerm" : "Standard Generalized Markup Language",
"ID" : "SGML",
"SortAs" : "SGML",
"Acronym" : "SGML",
"Abbrev" : "ISO 8879:1986",
"GlossSee" : "markup"
}
},
"title" : "S"
},
"title" : "example glossary"
}
}
But today I thought back to that and figured surely something like that must already be at hand if I'd just looked for it. Sure enough, there are many easy options that work conveniently from the shell, similarly to that script:
- json_xs (Perl JSON::XS)
- python -mjson.tool (Python 2.6+)
- prettify_json.rb (Ruby json gem)
And those were just the ones that were likely already on the machine I was using! Hooray for convenience.
Character encoding in perl: decode_utf8() vs decode('utf8')
When doing some recent encoding-based work in Perl, I found myself in a situation which seemed fairly unexplainable. I had a function which used some data which was encoded as UTF-8, ran Encode::decode_utf8() on said data to convert to Perl's internal character format, then converted the "wide" characters to the numeric entity using HTML::Entities::encode_entities_numeric(). Logging/printing of the data on input confirmed that the data was properly formatted UTF-8, as did running `iconv -f utf8 -t utf8 output.log >/dev/null` for the purposes of review.
However when I ended up processing the data, it was as if I had not run the decode function at all. In this case, the character in question was € (unicode code point U+20AC). The expected behavior from encode_entities_numeric() would be to turn any of the hi-bit characters in the perl string (i.e. all Unicode code points > 0x80) into the corresponding numeric entity (€ - € in this case). However instead of that specific character's numeric entity appearing in the output, the entities which appeared were: € i.e., the raw UTF-8 encoded value for €, with each octet being treated as an independent character instead of part of the whole encoded value.
What was particularly confusing was that extracting the relevant parts from the script in question resulted in the expected answer, so it was clearly not an issue of HTML::Entities not being able to deal with Unicode characters, as this code snippet demonstrates:
$ perl -MHTML::Entities+encode_entities_numeric -MEncode -e '$c=qq{\xE2\x82\xAC}; print encode_entities_numeric(decode_utf8($c))'
--> €
In the actual non-extracted version of the code, I was scratching my head. This was exhibiting the signs of doubly-encoded data, however I couldn't see how that could be the case. There were no PerlIO layers (e.g., :utf8 or :encoding) at play, the data I was outputting to a log file for verification purposes was being written via a brand new filehandle from a bare open(); I verified in multiple ways that the raw octets being passed in to the function were not doubly-encoded (printing the raw character points, counting lengths of the runs of octets and verifying that these matched the length of the UTF-8 encoded value for the represented characters, etc). The more things I tried the more puzzled I got. Finally, I changed the Encode::decode_utf8() call to a Encode::decode('utf8') one, providing the encoding explicitly. At this point, the processing pipeline started working as expected, and hi-bit characters were being output as their full numeric entities.
Since the documentation for decode_utf8 indicated that it should be identical to decode('utf8'), I resorted to the code to find out why it worked with the version that specified the encoding explicitly. I found that decode_utf8() does one additional thing that the regular decode('utf8') does not, and that is that before processing via the regular decode() function, decode_utf8 first checks the UTF-8 flag of the data that is being passed in, and if it is set it returns the data without further decoding*. My best guess is that this is to prevent errors if someone attempts to decode UTF-8 data in a string which is already in Perl's internal format, so in most cases this will provide a caller-friendly interface that will DWYM in many expected cases.
Armed with this knowledge, I verified that for some reason, the data that was being passed into the function had the UTF-8 flag set, so using the explicit decode('utf8') in lieu of decode_utf8() fixed the issue for me. (Tracing down the reason for the UTF-8 flag being set on this data was out of scope for this exercise, but is the true fix.) And just to verify that this was in fact the cause of the issue at hand, here's our example, modified slightly (we use the utf8::upgrade function to turn the UTF-8 flag on in the data and treat as actual encoded characters instead of raw octets):
$ perl -l -MHTML::Entities+encode_entities_numeric -MEncode -Mutf8 -e '$c=qq{\xE2\x82\xAC}; utf8::upgrade($c); print encode_entities_numeric(decode_utf8($c))'
--> €
* The UTF-8 flag is more-or-less an implementation detail of how Perl is able to deal with legacy 8-bit binary data in no particular encoding (i.e., raw octets, which it treats as latin-1) as well as the full range of Unicode data, and deal with both efficiently and in a backwards-compatible manner.
Using "diff" and "git" to locate original revision/source of externally modified files
I recently ran into an issue where I had a source file of unknown version which had been substantially modified from its original form, and I wanted to find the version of the originating software that it had originally come from to compare the changes. This file could have come from any number of the 100 tagged releases in the repository, so obviously a hand-review approach was out of the question. While there were certainly clues in the source file (i.e., copyright dates to narrow down the range of commits to review) I thought up and used this technique:
Here are our considerations:
- We know that the number of changes to the original file is likely small compared to the size of the file overall.
- Since we're trying to uncover a likely match for the purposes of reviewing, exactness is not required; i.e., if there are lines in common with future releases, we're interested in the changes, so a revision with the fewest number of changes is preferred over finding the *exact* version of the file that this was originally based on.
The basic thought, then, is that we want to take the content of the unversioned file (i.e., the file that was changed) and find the revision of the corresponding file in the repository with the least number of changes, which we'll measure as the count of the lines in the source code diff. This struck me as similar to the copy detection that git does, insofar as it can detect content that is similar to some source content with a certain amount of tolerance for changes from the base. The difference in this case is that we're comparing content across a number of refs rather than across all of the blobs in a single ref. This recipe distilled down to the following bash command:
for ref in $(git tag);
do
echo -n $ref;
diff -w <(git show $ref:/path/to/versioned/file 2>/dev/null) modified_file | wc -l;
done | sort -k2 -n
The results of running this command is a list of the tags in the repository ordered by how similar they are to the target content (most similar first). A few comments:
- We iterate through all tags in the project; while there could indeed be changes to the relevant file in intermediate versions, due to the way the release worked it's likely the original file was based on a released (aka tagged) version.
- We're using diff's -w option, as the content may have changed spaces to tabs or vice versa, depending on the editor/editing habits of the original user. This helps us ensure that the changes that we're focusing on are the ones that change something substantial.
- We're doing a numeric sort so the lines with the least number of changes show up at the top.
- For the specific case I used this technique with, there were a number of revisions that had the least number of changed lines. Upon reviewing this smaller set of revisions (using the git diff rev1 rev2 -- path/to/content syntax), it turns out that the file in question had remained unchanged in each of these revisions, so any one of them was useful for my purposes.
- The flexibility in the version detection works in this case because this was an isolated part of the system that did not have any changes or dependencies. If there had been important changes to the system as a whole independent of the changes to this file (but which had an affect on the operation of this specific part), we would need to have a more exact method of identifying the file.
Utah Open Source Conference 2010 part 1
It's been about a little over a month since the 2010 Utah Open Source Conference, and I decided to take a few minutes to review talks I enjoyed and link to my own talk slides.
Magento: Mac Newbold of Code Greene spoke on the Magento ecommerce framework for PHP. I've somewhat familiar with Magento, but a few things stood out:
- He finds the Magento Enterprise edition kind of problematic because Varien won't support you if you have any unsupported extensions. Some of his customers had problems with Varien support and went back to the community edition.
- Magento is now up to around 30 MB of PHP files!
- As I've heard elsewhere, serious customization has a steep learning curve.
- The Magento data model is an EAV (Entity-Attribute-Value) model. To get 50 columns of output requires 50+ joins between 8 tables (one EAV table for each value datatype).
- There are 120 tables total in default install -- many core features don't use the EAV tables for performance reasons.
- Another observation I've heard in pretty much every conversation about Magento: It is very resource intensive. Shared hosting is not recommended. Virtual servers should have a minimum of 1/2 to 1 GB RAM. Fast disk & database help most. APC cache recommended with at least 128 MB.
- A lot of front-end things are highly adjustable from simple back-end admin options.
- Saved credit cards are stored in the database, and the key is on the server. I didn't get a chance to ask for more details about this. I hope it's only the public part of a public/secret keypair!
It was a good overview for someone wanting to go beyond marketing feature lists.
Node.js: Shane Hansen of Backcountry.com spoke on Node, comparing it to Tornado and Twisted in Python. He calls JavaScript "Lisp in C's clothing", and says its culture of asynchronous, callback-driven code patterns makes Node a natural fit.
Performance and parallel processing are clearly strong incentives to look into Node. The echo server does 20K requests/sec. There are 2000+ Node projects on GitHub and 500+ packages in npm (Node Package Manager), including database drivers, web frameworks, parsers, testing frameworks, payment gateway integrations, and web analytics.
A few packages worth looking into further:
- express - web microframework like Sinatra
- Socket-IO - Web Sockets now; falls back to other things if no Web Sockets available
- hummingbird - web analytics, used by Gilt.com
- bespin - "cloud JavaScript editor"
- yui3 - build HTML via DOM, eventbus, etc.
- connect - like Ruby's Rack
I haven't played with Node at all yet, and this got me much more interested.
Metasploit: Jason Wood spoke on Metasploit, a penetration testing (or just plain penetrating!) tool. It was originally in Perl, and now is in Ruby. It comes with 590 exploits and has a text-based interactive control console.
Metasploit uses several external apps: nmap, Maltego (proprietary reconnaissance tool), Nessus (no longer open source, but GPL version and OpenVAS fork still available), Nexpose, Ratproxy, Karma.
The reconnaissance modules include DNS enumeration, and an email address collector that uses the big search engines.
It can backdoor PuTTY, PDFs, audio, and more.
This is clearly something you've got to experiment with to appreciate. Jason posted his Metasploit talk slides which have more detail.
So Many Choices: Web App Deployment with Perl, Python, and Ruby: This was my talk, and it was a lot of fun to prepare for, as I got to take time to see some new happenings I'd missed in these three languages communities' web server and framework space over the past several years.
The slides give pointers to a lot of interesting projects and topics to check out.
My summary was this. We have an embarrassment of riches in the open source web application world. Perl, Python, and Ruby all have very nice modern frameworks for developing web applications. They also have several equivalent solid options for deploying web applications. If you haven't tried the following, check them out:
That's about half of my notes on talks, but all I have time for now. I'll cover more in a later post.
Providing Database Handle for Interchange Testing
I've recently begun using the test driven development approach to my projects using Perl's Test::More module. Most of my projects lately have been with Interchange which has some hurdles to get around as far as test driven development is concerned. Primarily this is because Interchange runs as a daemon and provides some readily available utilites like the database handle. This method is not available to our tests, so they need to be made available as discussed below.
I develop Usertags, GlobalSubs and ActionMaps where applicable as it helps keep the separation of business logic and views clear. I generally organize these to call a function within a Perl module so they can be tested properly. Most of these tags involve some sort of connection with the database to present information to the user in which I uses the Interchange ::database_exists_ref method.
When it comes to testing I want to ensure that the test script invokes the same method. Otherwise, your script will not be testing the code as its used in production.
Let's say you are building a Perl module that looks something like this:
package YourMagic;
use strict;
sub do_something {
my ($opt) = @_;
# some code
my $dbh = ::database_exists_ref($opt->{table})->dbh
or return undef;
# ... more code
return $output;
}
1;
The ::database_exists_ref() method will not be available for a test script and needs to be defined. It should return an object to the dbh method in the test script as it does within Interchange. There is no need to test the method itself, as it is not part of the "what" that is being developed. The following code needs to be added to the test script so it can handle the correct type of database reference returned by Interchange.
use lib '/home/user/interchange/custom/lib';
use Test::More tests => 2;
use DBI;
# Here are the methods to provide proper reference to our database handle
################################
sub ::database_exists_ref {
my $table = shift;
return undef if !$table;
# return an object with a dbh method
return bless({}, __PACKAGE__);
}
sub dbh {
# define a dbh method
my $db = DBI->connect('dsn, 'user', 'pass');
return $db;
}
##################################
use YourMagic;
is(
YourMagic::do_something(),
undef,
'do_something() returns undef when called with no arguments',
);
is(
YourMagic::do_something(\%opt),
undef,
'do_something() returns ...',
);
It is also worthwhile to note that you'll need to use the ::database_exists_ref method to look up some information from the existing table that is valuable to test against. Now the do_something() method will call ::database_exists_ref() when invoked.
This approach allows us to use, reuse, and add new tests without worrying about mock data during the intial development. You can be sure that the existing test scripts will function properly against the latest data that is available.
I will cover some other topics regarding Interchange Test Driven Development in future posts. For more information regarding Unit Testing in general see this post by Ethan.
Perl Testing - stopping the firehose
I maintain a large number of Perl modules and scripts, and one thing they all have in common is a test suite, which is basically a collection of scripts inside a "t" subdirectory used to thoroughly test the behavior of the program. When using Perl, this means you are using the awesome Test::More module, which uses the Test Anything Protocol (TAP). While I love Test::More, I often find myself needing to stop the testing entirely after a certain number of failures (usually one). This is the solution I came up with.
Normally tests are run as a group, by invoking all files named t/*.t; each file has numerous tests inside of it, and these individual tests issue a pass or a fail. At the end of each file, a summary is output stating how many tests passed and how many failed. So why is stopping after a failed test even needed? The reasons below mostly relate to the tests I write for the Bucardo program, which has a fairly large and complex test suite. Some of the reasons I like having fine-grained control of when to stop are:
- Scrolling back through screens and screens of failing tests to find the point where the test began to fail is not just annoying, but a very unproductive use of my time.
- Tests are very often dependent. If test #23 fails, it means there is a very good chance that most if not all of the subsequent tests are going to fail as well, and it makes no sense for me to look at fixing anything but test #23 first.
- Tests can take a very long time to run, and I can't wait around for the errors to start appearing and hit ctrl-c. I need to kick them off, go do something else, and then come back and have the tests stop running immediately after the first failed test. Bucardo tests, for example, create and startup four different Postgres clusters, populates the databases inside each cluster with test data, installs a fresh copy of Bucardo, and *then* begins the real testing. No way I'm going to wait around for that to happen.
- Debugging is greatly aided by having the tests stop where I want them to. Often tests after the failing one will modify data and otherwise destroy the "state" such that I cannot manually duplicate the error right then and there, and thus fix it easily.
For now, my solution is to override some of the methods from Test::More. I have a standard script that does this, and I 'use' this script after I 'use Test::More' inside my test scripts. For example, a test script might look like this:
#!/usr/bin/env perl
use strict;
use warnings;
use Data::Dumper;
use Test::More tests => 356;
use TestOverride;
sub some_function {
my $arr = [];
push @$arr => 4,9;
return [$arr];
}
my $t = q{Function some_function() returns correct value when called with 'foo'};
my $value = some_function('foo');
my $res = [[3],[5]];
is_deeply( $value, $res, $t);
...
$t = q{Value of baz is 123};
is ($baz, 123, $t);
...
In turn, the TestOverride file contains this:
...
use Data::Dumper;
$Data::Dumper::Indent = 1;
$Data::Dumper::Terse = 1;
$Data::Dumper::Pad = '|';
use base 'Exporter';
our @EXPORT = qw{ is_deeply like pass is isa_ok ok };
my $bail_on_error = $ENV{TESTBAIL} || 0;
my $total_errors = 0;
sub is_deeply {
# Return right away if the test passes
my $rv = Test::More::is_deeply(@_);
return $rv if $rv;
if ($bail_on_error and ++$total_errors >= $bail_on_error) {
my ($file,$line) = (caller)[1,2];
Test::More::diag("GOT: ".Dumper $_[0]);
Test::More::diag("EXPECTED: ".Dumper $_[1]);
Test::More::BAIL_OUT "Stopping on a failed 'is_deeply' test from line $line of $file.";
}
return;
} ## end of is_deeply
sub is {
my $rv = Test::More::is(@_);
return $rv if $rv;
if ($bail_on_error and ++$total_errors >= $bail_on_error) {
my ($file,$line) = (caller)[1,2];
Test::More::BAIL_OUT "Stopping on a failed 'is' test from line $line of $file.";
}
return;
} ## end of is
The is_deeply compares two arbitrary Perl structures (such as the arrayref here, but it can do hashes as well), and points out if they differ, and where. The "deeply" is because it will walk through the entire structure to find any differences. Good stuff.
Some things to note about the new is_deeply function: first, we simply pass in our parameters to the "real" is_deeply subroutine - the one found inside the Test::More package. If this passes (by returning true), we simply pass that truth back to the caller, and it's completely as if is_deeply had not been overwritten at all. However, if the test fails, Test::More::is_deeply will output a failure notice, but we check to see if the total number of failures for this test script ($total_errors) is greater than or equal to the threshold ($bail_on_error) that we set via then environment variable TESTBAIL. (Having it as an environment variable that defaults to zero allows the traditional behavior to be easily changed without editing any files).
If the number of failed tests is over our threshhold, we call the BAIL_OUT method from Test::More, which not only stops the current test script from running any more scripts, but stops any subsequent test files from running as well.
Before calling BAIL_OUT however, we also take advantage of the overriding to provide a little more detail about the failure. We output the line and file the test came from (because Test::More::is_deeply only sees that we are calling it from within the TestOverride.pm file). Most importantly, we output a complete dump of the expected and actual structures passed to is_deeply to be compared. The regular is_deeply only describes where the first mismatch occurs, but I often need to see the entire surrounding object. So rather than normal output looking like this:
1..356
not ok 1 - Function some_function() returns correct value when called with 'foo'
# Failed test 'Function some_function() returns correct value when called with 'foo''
# at test1.t line 18.
# Structures begin differing at:
# $got->[0] = '4'
# $expected->[0] = '3'
# Looks like you planned 356 tests but ran 1.
# Looks like you failed 1 test of 1 run.
The new output looks like this:
1..356
not ok 1 - Function some_function() returns correct value when called with 'foo'
# Failed test 'Function some_function() returns correct value when called with 'foo''
# at TestOverride.pm line 23.
# Structures begin differing at:
# $got->[0] = '4'
# $expected->[0] = '3'
# GOT: |[
# | 4,
# | [
# | 9
# | ]
# |]
# EXPECTED: |[
# | 3
# |]
Bail out! Stopping on a failed 'is_deeply' test from line 17 of test1.t.
Yes, the Test::Most module does some similar things, but I don't use it because it's yet another module dependency, it doesn't allow me to control the number of acceptable failures before bailing, and it doesn't show pretty output for is_deeply.
Guidelines for Interchange site migrations
I'm involved at End Point often with Interchange site migrations. These migrations can be due to a new client coming to us and needing hosting or migrating from one server to another within our own infrastructure.
There are many different ways to do a migration, in the end though we need to hit on certain points to make sure that the migration goes smoothly. Below you will find steps which you can adapt for your specific migration.
During the start of the migration it might be a good time to introduce git for source control. You can do this by creating the repository and cloning it to /home/account/live, setting up .gitignore files for logs, counter files, gdbm files. Then commit the changes back to the repo and you've now introduced source control without much effort, improving the ability to make changes to the site in the future. This is also helpful to document the changes you make to the code base along the way during the migration in case you need to merge changes from the current production site before completing the migration.
- Export all of the gdbm databases to their text file equivalents on the production server
- Take a backup from production of the database, catalog, interchange server, htdocs
- Setup an account
- Create the database and user
- Restore the database, catalog, interchange server and htdocs
- Update the paths in interchange/bin for each script to point at the new location
- Grep the restored code for hard coded paths and update those paths to the new locations. Better yet move these paths out to a catalog_local.cfg where environment specific information can go.
- Grep the restored code for hard coded urls and use the [area] tag to generate the urls
- Update the urls in products/variable.txt to point at the test domain
- Update the sql settings in products/variable.txt to point at the new database using the new user
- Remove the gdbm databases so they will be recreated on startup from the source text files
- Install a local Perl if it's not already installed (./configure -des will compile and install Perl locally)
- Install Bundle::InterchangeKitchenSink
- Install the DBD module for MySQL or PostgreSQL
- Review the code base looking for use statements in custom code and Require module settings in interchange.cfg. Install the Perl modules found into the local Perl.
- Setup a non ssl and ssl virtual host using a temporary domain. Configure the temporary domain to use the SSL certificate from the production domain.
- Firewall or password protect the virtual host so it is not accessible to the public
- Generate a vlink using interchange/bin/compile and copy it into the cgi-bin directory and name it properly
- Startup the new Interchange
- Review error messages and resolve until Interchange will start properly
- Test the site thoroughly, resolving issues as they appear. Make sure that checkout, charging credit cards, sending of emails, using the admin, etc all function.
- Migrate any cron jobs running on the current production site, such as session expiration scripts
- Setup logrotation for the new logs that will be created
- Verify that you have access to make DNS changes
- Set the TTL for the domain to a low value such as 5 minutes
- Modify the new production site to respond to the production url, test by updating your hosts file to manually set the IP address of the domain
- Shutdown the new Interchange
- Restore a copy of the original backup for Interchange, the catalog and htdocs to /tmp on the production server
- Shutdown the production Interchange, put up a maintenance note on the production site.
- Take a backup of the production database and restore on the new server
- Diff the Interchange, catalog and htdocs directory between /tmp and the current production locations, making note of the files that have changed since we took the original copy.
- Copy the files that have changed, making sure to merge with any changes we have made on the new production site. Making sure to copy over all .counter and .autonumber files to the new production site.
- Start Interchange on the new production server
- Test the site thoroughly on the new production server, using the production url. Make sure that checkout with charging the credit card functions properly.
- Resolve any remaining issues found during the testing
- Setup the Interchange daemon to start at boot for this site in /etc/rc.d/rc.local or in cron using @reboot
- Update DNS to point at the new production IP address
- Update the TTL of the domain to a longer value
- Open the site to the public by opening the firewall or removing the password protection
- Keep an eye on the error logs for any issues that might crop up
This will hopefully give you a solid guide for performing an Interchange site migration from one server to another and some of the things to watch out for that might cause issues during the migrations.
Tail_n_mail and the log_line_prefix curse
One of the problems I had when writing tail_n_mail (a program that parses log files and mails interesting lines to you) was getting the program to understand the format of the Postgres log files. There are quite a few options inside of postgresql.conf that control where the logging goes, and what it looks like. The basic three options are to send it to a rotating logfile with a custom prefix at the start of each line, to use syslog, or to write it in CSV format. I'll save a discussion of all the logging parameters for another time, but the important one for this story is log_line_prefix. This is what gets prepended to each log line when using 'stderr' mode (e.g. regular log files and not syslog or csvlog). By default, log_line_prefix is an empty string. This is a very useless default.
What you can put in the log_line_prefix parameter is a string of sprintf style escapes, which Postgres will expand for you as it writes the log. There are a large number of escapes, but only a few are commonly used or useful. Here's a log_line_prefix I commonly use:
log_line_prefix = '%t [%p] %u@%d '
This tells Postgres to print out the timestamp, the PID aka process id (inside of square brackets), the current username and database name, and finally a single space to help separate the prefix visually from the rest of the line. The above will generate lines that look like this:
2010-08-06 09:24:57.714 EDT [7229] joy@joymail LOG: execute dbdpg_p7228_5: SELECT count(id) FROM joymail WHERE folder = $1
2010-08-06 09:24:57.714 EDT [7229] joy@joymail DETAIL: parameters: $1 = '4'
As you might imagine, the customizability of log_line_prefix makes parsing the log files all but impossible without some prior knowledge. I didn't want to go the pgfouine route and make people change their log_line_prefix to a specific setting. I think it's kind of rude to force your database to change its logging to accommodate your tools :). The original quick solution I came up with was to have a set of predefined regular expressions and the user would pick one that most closely matched their logs. For tail_n_mail to work properly, it needs to pick up at least the PID so it can tell when one statement ends a new one begins. For example, if you chose "regex #1", the log parsing regex would look like this:
(\d\d\d\d\-\d\d\-\d\d \d\d:\d\d:\d\d).+?(\d+)
This works fine on the example above, and gets us the timestamp and the PID from each line. The stock regexes worked for many different log_line_prefixes I came across that our clients were using, but I was never very happy with this solution. Not only was it susceptible to failing completely when a client was using a log_line_prefix not fitting into the current list of regexes, but there was no way to know exactly where the prefix ended and the statement began, which is important for the formatting of the output and the canonicaliztion of similar queries.
Enter the current solution: building a regex on the fly. Since we don't have a connection to the database at all, merely to the the log files, this requires that the user enter in their current log_line_prefix. This is a simple entry into the tailnmailrc file that looks just like the entry in postgresql.conf, e.g.:
log_line_prefix = '%t [%p] %u@%d '
The tail_n_mail script uses that variable to build a custom regex specifically tailored to that log_line_prefix and thus to the Postgres logs being used. Not only can we grab whatever bits we want (currently we only care about the timestamp (%t and %m) and the PID (%p)), but we can now cleanly break apart each line in the log into the prefix and the actual statement. This means the canonicalization/flattening of the queries is more effective, and allows us to only output the prefix information once. The output of tail_n_mail looks something like this:
Date: Fri Aug 6 11:01:03 2010 UTC
Host: whale.example.com
Unique items: 7
Total matches: 85
Matches from [A] /var/log/pg_log/postgresql-2010-08-05.log: 61
Matches from [B] /var/log/pg_log/postgresql-2010-08-06.log: 24
[1] From files A to B (between lines 14,205 of A and 527 of B, occurs 64 times)
First: [A] 2010-08-05 16:52:11 UTC [1602] postgres@mydb
Last: [B] 2010-08-06 01:18:14 UTC [20981] postgres@mydb
ERROR: syntax error at or near ")"
STATEMENT: INSERT INTO mytable (id, foo, bar) VALUES (?,?,?))
-
ERROR: syntax error at or near ")"
STATEMENT: INSERT INTO mytable (id, foo, bar) VALUES (123,'chocolate','donut'));
[2] From file A (line 12,172)
2010-08-05 12:27:48 UTC [2906] bob@otherdb
ERROR: invalid input syntax for type date: "May"
STATEMENT: UPDATE personnel SET birthdate='May' WHERE id = 1234;
(plus five other entries)
For the entry in the above example, we are able to show the complete prefix of the log lines where the error first occurred and where it most recently occurred. The next two lines show the "flattened" version of the query that tail_n_mail uses to group together similar errors. We then show a non-flattened example of an actual query from that group. In this case, someone added an extra closing paren in their application somewhere, which gives the same error each time, although the exact output changes depending on the values used. In the second example, because there is only one match, we don't bother to show the flattened version at all.
So in theory tail_n_mail should be now be able to handle any Postgres log you care to throw at it (yes, it can read syslog and csvlog format as well). As my coworker pointed out, parsing log files in this way is something that should probably be abstracted into a common module so other tools like pgsi can take advantage of it as well.
Localize $@ in DESTROY
I have been conditioned now for many years in Perl to trust the relationship of $@ to its preceding eval. The relationship goes something like this: if you have string or block eval, immediately after its execution, $@ will either be false or it will contain the die message of that eval (or the generic "Died at ..." message if none is provided). Implicit here is that evals contained within an eval have their effects on $@ concealed, unless the containing eval "passes on" the inner eval's die.
To quickly demonstrate:
1 use strict;
2 use warnings;
3
4 eval {
5 print "Some stuff\n";
6 eval {
7 die 'Oops. Bad inner eval';
8 };
9
10 printf '$@ in outer eval: %s', $@;
11 };
12
13 printf '$@ after outer eval: %s', $@;
14 print $/;
produces the following output:
[mark@sokt ~]$ perl demo.pl Some stuff $@ in outer eval: Oops. Bad inner eval at demo.pl line 7. $@ after outer eval: [mark@sokt ~]$
Only if the containing eval itself dies do we find any data in $@:
1 use strict;
2 use warnings;
3
4 eval {
5 print "Some stuff\n";
6 eval {
7 die 'Oops. Bad inner eval';
8 };
9
10 printf '$@ in outer eval: %s', $@;
11 die 'Uh oh. Bad outer eval, too';
12 };
13
14 printf '$@ after outer eval: %s', $@;
15 print $/;
which produces:
[mark@sokt ~]$ perl demo.pl Some stuff $@ in outer eval: Oops. Bad inner eval at demo.pl line 7. $@ after outer eval: Uh oh. Bad outer eval, too at demo.pl line 11. [mark@sokt ~]$
Why am I covering this, well known to any serious Perl programmer? Because I was caught off guard troubleshooting for a client last week when the result of an inner eval "leaked" through, affecting $@ of the containing eval. Because I was so conditioned to the stated relationship between eval and $@, it took me quite some time before I even opened up to the possibility.
It turned out the hitch had to do with garbage collection. The key was that the inner eval in question was initially called from a routine within an object's DESTROY method. As I discovered, at least in Perl 5.10, if a containing eval dies, causing an object to go out of scope, and that object's DESTROY itself executes an eval, $@ reflects the exit condition of the eval from within DESTROY, and not that of the containing eval. Even more strange, this is only true if the containing eval dies. If instead the containing eval completes, then that same dying eval within DESTROY does not affect the condition of $@ after the containing eval. It will still be false, as (IMO) it should be.
So, some code demonstrating each situation. We have 3 conditions:
- Containing eval dies, eval within DESTROY dies
- Containing eval dies, eval within DESTROY does not die
- Containing eval does not die, and eval is called within DESTROY, die or not.
Sample code demonstrating 1st condition:
1 use strict;
2 use warnings;
3
4 package Obj;
5
6 sub DESTROY {
7 eval { die 'in DESTROY' };
8 }
9
10 package main;
11
12 eval {
13 my $obj = {};
14
15 bless $obj, 'Obj';
16 die 'in main eval';
17
18 print "Super important stuff that must finish or we really need to know about it!\n";
19
20 return 1;
21 };
22
23 if ($@) {
24 printf '$@ comes from code %s', $@;
25 }
26 else {
27 print "Happy days! Our eval code ran to completion. Woot!\n";
28 }
Output as follows:
[mark@sokt ~]$ perl test1.pl $@ comes from code in DESTROY at test1.pl line 7. [mark@sokt ~]$
Demo of 2nd condition:
1 use strict;
2 use warnings;
3
4 package Obj;
5
6 sub DESTROY {
7 eval { 1 };
8 }
9
10 package main;
11
12 eval {
13 my $obj = {};
14
15 bless $obj, 'Obj';
16 die 'in main eval';
17
18 print "Super important stuff that must finish or we really need to know about it!\n";
19
20 return 1;
21 };
22
23 if ($@) {
24 printf '$@ comes from code %s', $@;
25 }
26 else {
27 print "Happy days! Our eval code ran to completion. Woot!\n";
28 }
Output as follows:
[mark@sokt ~]$ perl test2.pl Happy days! Our eval code ran to completion. Woot! [mark@sokt ~]$
Notice how particularly insidious the above is. We not only don't know what the error was from the eval block that immediately precedes the evaluation of $@, but we actually think it succeeds!
Finally, the 3rd condition:
1 use strict;
2 use warnings;
3
4 package Obj;
5
6 sub DESTROY {
7 eval { die 'in DESTROY' };
8 }
9
10 package main;
11
12 eval {
13 my $obj = {};
14
15 bless $obj, 'Obj';
16
17 print "Super important stuff that must finish or we really need to know about it!\n";
18
19 return 1;
20 };
21
22 if ($@) {
23 printf '$@ comes from code %s', $@;
24 }
25 else {
26 print "Happy days! Our eval code ran to completion. Woot!\n";
27 }
Output as follows:
[mark@sokt ~]$ perl test3.pl Super important stuff that must finish or we really need to know about it! Happy days! Our eval code ran to completion. Woot! [mark@sokt ~]$
So, fortunately, case 3 contains the leak when the outer eval completes successfully. We don't introduce the worst possible situation: a successful eval that is subsequently treated as a failure. However, cases 1, and especially 2, are bad enough.
Now that I know all this, the solution is thankfully simple. When constructing objects, if they include a supplied DESTROY, always localize $@. It doesn't matter whether I execute any evals or not; if the code calls any other routines that do, anywhere in the stack, the problem is introduced. A local $@ provides full protection.
A rerun of test1 but with localization provides a much more expected result:
1 use strict;
2 use warnings;
3
4 package Obj;
5
6 sub DESTROY {
7 local $@;
8 eval { die 'in DESTROY' };
9 }
10
11 package main;
12
13 eval {
14 my $obj = {};
15
16 bless $obj, 'Obj';
17 die 'in main eval';
18
19 print "Super important stuff that must finish or we really need to know about it!\n";
20
21 return 1;
22 };
23
24 if ($@) {
25 printf '$@ comes from code %s', $@;
26 }
27 else {
28 print "Happy days! Our eval code ran to completion. Woot!\n";
29 }
Output as follows:
[mark@sokt ~]$ perl test1.pl $@ comes from code in main eval at test1.pl line 17. [mark@sokt ~]$
and test2, which now doesn't lie to us about the success of the eval of interest:
1 use strict;
2 use warnings;
3
4 package Obj;
5
6 sub DESTROY {
7 local $@;
8 eval { 1 };
9 }
10
11 package main;
12
13 eval {
14 my $obj = {};
15
16 bless $obj, 'Obj';
17 die 'in main eval';
18
19 print "Super important stuff that must finish or we really need to know about it!\n";
20
21 return 1;
22 };
23
24 if ($@) {
25 printf '$@ comes from code %s', $@;
26 }
27 else {
28 print "Happy days! Our eval code ran to completion. Woot!\n";
29 }
Output as follows:
[mark@sokt ~]$ perl test2.pl $@ comes from code in main eval at test2.pl line 17. [mark@sokt ~]$
Mock Testing with Perl
I'll start by saying that I probably should have started with Test::MockObject and saved myself all of this trouble. But sometimes things don't work out that way.
So, I'm building unit tests in Perl the hard way. By the hard way, I mean that I am constructing ever more elaborate, interdependent, complex, and brittle test data sets to test the functions that I am hacking on. The data model is moderately complex, so there really isn't any way around it (since I'm doing it the hard way, after all).
At one point, one function (which I am not testing) returns a result that I need for the function I am testing. The problem is that it reaches pretty far away into a section of the data model that I'd rather not set up test data for at the moment just to get that one value. This is where I'm sitting there wishing I had mock objects more than usual, since this would be a perfect place to mock the method. Since I couldn't be bothered to see if someone had written such a handy module, I looked for a hard way to do it. Turns out that there is one.
It's not actually hard, but it could be considered complex if you are not familiar with typeglobs and the workings of the symbol table in Perl. A good discussion can be found in the Perl Cookbook in ch10.14.
In the following example, the function Base::Shipping::Package::weight is called at some point in create_shipment. Being able to call it is imperative to completing create_shipment. In my case, I have to have a successful result from create_shipment in order to test process_shipment.
{
local *Base::Shipping::Package::weight = \&test_weight
my $shipment = $class->create_shipment($shipment);
my $result = $class->process_shipment($shipment);
test $result;
}
sub test_weight { 4.0 }
In here then, the local call redefines the weight function inside the scope of the block. This turns out to be fairly convenient given that I already had the structure in place to test things this way. There are possibly other cases where something like this might make more sense than using Test::MockObject in the first place although I am somewhat skeptical.
PostgreSQL UTF-8 Conversion
It's becoming increasingly common for me to be involved in conversion of an old version of PostgreSQL to a new one, and at the same time, from an old "SQL_ASCII" encoding (that is, undeclared, unvalidated byte soup) to UTF-8.
Common ways to do this are to run pg_dumpall and then pipe the output through iconv or recode. When your source encoding is all pure ASCII, you don't need to do even that. When it's really all Windows-1252 (a superset of Latin-1 aka ISO-8859-1) it's easy.
But often, the data is stored in various unknown encodings from several sources over the course of years, including some that's already in UTF-8. When you convert with iconv, it dies with an error at the first problem, whereas recode will let you ignore encoding problems, but that leaves you with junk in your output.
The case I'm often encountering is fairly easy, but not perfect: Lots of ASCII, some Windows-1252, and some UTF-8. Since both pure ASCII and UTF-8 can be mechanistically detected, I put together this script to do the detection. It's Perl and uses the nice IsUTF8 module to do its character encoding detection:
Pipe input to the script. It handles one line at a time. When run with any arguments (such as --test) it will swallow pure ASCII lines, write lines it thinks are valid UTF-8 to stderr, and will convert the remaining presumed Windows-1252 lines to stdout, for manual examination.
If its guesses look correct, run it again with no arguments, and it will write all 3 types of encoding to stdout, ready for input to psql in your new UTF-8 encoded database.
(Don't forget to munge your pg_dump file to remove any hardcoded declarations of "SQL_ASCII" encoding from CREATE DATABASE statements, or otherwise make sure your database actually is created with UTF-8 encoding!)
Splitting Postgres pg_dump into pre and post data files
I've just released a small Perl script that has helped me solve a specific problem with Postgres dump files. When you use pg_dump or pg_dumpall, it outputs things in the following order, per database:
- schema creation commands (e.g. CREATE TABLE)
- data loading command (e.g. COPY tablename FROM STDIN)
- post-data schema commands (e.g. CREATE INDEX)
The problem is that using the --schema-only flag outputs the first and third sections into a single file. Hence, if you load the file and then load a separate --data-only dump, it can be very slow as all the constraints, indexes, and triggers are already in place. The split_postgres_dump script breaks the dump file into two segments, a "pre" and a "post". (It doesn't handle a file with a data section yet, only a --schema-only version)
Why would you need to do this instead of just using a full dump? Some reasons I've found include:
- When you need to load the data more than once, such as debugging a data load error.
- When you want to stop after the data load step (which you can't do with a full dump)
- When you need to make adjustments to the schema before the data is loaded (seen quite a bit on major version upgrades)
Usage is simply ./split_postgres_dump.pl yourdumpfile.pg, which will then create two new files, yourdumpfile.pg.pre and yourdumpfile.pg.post. It doesn't produce perfectly formatted files, but it gets the job done!
It's a small script, so it has no bug tracker, git repo, etc. but it does have a small wiki page at http://bucardo.org/wiki/Split_postgres_dump from which you can download the latest version.
Future versions of pg_dump will allow you to break things into pre and post data sections with flags, but until then, I hope somebody finds this script useful.
Update: There is now a git repo: git clone git://bucardo.org/split_postgres_dump.git
Gathering server information with boxinfo
I've just publicly released another Postgres-related script, this one called "boxinfo". Basically, it gathers information about a box (server), hence the catchy and original name. It outputs the information it finds into an HTML page, or into a MediaWiki formatted page.
The goal of boxinfo is to have a simple, single script that quickly gathers important information about a server into a web page, so that you can get a quick overview of what is installed on the server and how things are configured. It's also useful as a reference page when you are trying to remember which server was it that had Bucardo version 4.5.0 installed and was running pgbouncer.
As we use MediaWiki internally here at End Point (running with a Postgres backend, naturally), the original (and default) format is HTML with some MediaWiki specific items inside of it.
Because it is meant to run on a wide a range of boxes as possible, it's written in Perl. While we've run into a few boxes over the years that did not have Perl installed, the number that had any other language you choose (except perhaps sh) is much greater. It requires no other Perl modules, and simply makes a lot of system calls.
Various information about the box is gathered. System wide things such as mount points, disk space, schedulers, packaging systems are gathered first, along with versions of many common Unix utilities. We also gather information on some programs where more than just the version number is important, such as puppet, heartbeat, and lifekeeper. Of course, we also go into a great amount of detail about all the installed Postgres clusters on the box as well.
The program tries its best to locate every active Postgres cluster on the box, and then gathers information about it, such as where pg_xlog is linked to, any contrib modules installed, any interesting configuration variables from postgresql.conf, the size of each database, and lots of detailed information about any Slony or Bucardo configurations it finds.
The main page for it is on the Bucardo wiki at http://bucardo.org/wiki/Boxinfo. That page details the various command line options and should be considered the canonical documentation for the script. The latest version of boxinfo can be downloaded from that page as well. For any enhancement requests or problems to report, please visit the bug tracker at http://bucardo.org/bugzilla/.
What exactly does the output look like? We've got an example on the wiki showing the sample output from a run against my laptop. Some of the items were removed, but it should give you an idea of what the script can do, particularly with regards to the Postgres information: http://bucardo.org/wiki/Boxinfo/Example
The script is still a little rough, so we welcome any patches, bug reports, requests, or comments. The development version can be obtained by running: git clone git://bucardo.org/boxinfo.git
Monitoring Postgres log files with tail_n_mail
We've just publically released a useful script named tail_n_mail that keeps an eye on your Postgres log files and mails interesting lines to one or more addresses. It's released under a BSD license and is available at:
http://bucardo.org/wiki/Tail_n_mail
Complete documentation is available at the above, but here's a quick overview. First, it figures out the current log file (it actually works for any file, but it's primarily aimed at Postgres log files). Then, it finds any lines that match based on the INCLUDE lines in the config file, and finally removes any that do not match the EXCLUDE lines in the config files. It summarizes the results and sends a report to one or more emails.
To use, just specify a a configuration file as the first argument. Typically, the script is run from cron, either for instant reports (e.g. FATAL or PANIC errors), or for daily reports (e.g. all interesting ERRORs in the last 24 hours).
Here's what a typical config file looks like. In this example, we'll look for any FATAL or PANIC notices from Postgres, while ignoring a few known errors that we don't care about.
## Config file for the tail_n_mail.pl program ## This file is automatically updated EMAIL: greg@endpoint.com, postgres@endpoint.com FILE: /var/log/pg_log/postgres-%Y-%m-%d.log INCLUDE: FATAL: INCLUDE: PANIC: EXCLUDE: database ".+" does not exist EXCLUDE: database "template0" is not currently accepting connections MAILSUBJECT: HOST Postgres fatal errors (FILE)
It should be setup to run often from cron:
*/5 * * * * perl bin/tail_n_mail.pl bin/tnm/tnm.fatals.config
The resulting mail message will look like this:
Matches from /var/log/pg_log/postgres-2010-01-01.log: 42 Date: Fri Jan 1 10:34:00 2010 Host: pollo [1] Between lines 123005 and 147976, occurs 39 times. First: Jan 1 00:00:01 rojogrande postgres[4306] Last: Jan 1 10:30:00 rojogrande postgres[16854] Statement: user=root,db=rojogrande FATAL: password authentication failed for user "root" [2] Between lines 147999 and 148213, occurs 2 times. First: Jan 1 10:31:01 rojogrande postgres[3561] Last: Jan 1 10:31:10 rojogrande postgres[15312] Statement: FATAL main: write to worker pipe failed -(9) Bad file descriptor [3] (from line 152341) PANIC: could not locate a valid checkpoint record
There may be false positives, but it's not designed to be a complete log parser. There are some other command line flags and options for the config file: see the documentation for the full list. This script has been watching over a number of production systems for a while now, but improvements, ideas, and patches are always welcome. It's tracked via git; you can clone it by running:
git clone git://bucardo.org/tail_n_mail.git
Bugs and feature requests can be filed and tracked at:
ImageMagick EPS bug workaround
Sometimes software is buggy, and even with the malleability of open source software, upgrading to fix a problem may not be an immediate option due to lack of time, risks to production stability, or problems caused by other incompatible changes in a newer version of the software.
ImageMagick is a widely used open source library and set of programs for manipulating images in many ways. It's very useful and I'm grateful it exists and has become so powerful. However, many longtime ImageMagick users like me can attest that it has had a fair number of bugs, and upgrades sometimes don't go very smoothly as APIs change, or new bugs creep in.
Recently my co-worker, Jeff Boes, had the misfortune, or opportunity, of encountering just such a scenario. Our friends at CityPass have several site features that use ImageMagick for resizing, rotating, and otherwise manipulating or gathering data about images.
The environment specifics (skip if you're not troubleshooting an ImageMagick problem of your own!): RHEL 5 with its standard RPM of ImageMagick-6.2.8.0-4.el5_1.1.x86_64. The application server is Interchange, running on our local-perl-5.10.0 nonthreaded Perl build, using the local-ImageMagick-perl-6.2.8.0-4.1 library. Those custom builds are available in the packages.endpoint.com endpoint Yum repository.
CityPass reported problems with some EPS (Encapsulated PostScript) images failing to process correctly by ImageMagick. In fact, the bug prevented any subsequent image processing jobs from completing in the same OS process. Upgrading ImageMagick would fix the bug, but we can't currently do that on the production server due to other compatibility problems.
After some trial and error, Jeff determined that the ImageMagick bug only kicks in when the first image processed is an EPS file. If it's any other image type, it works fine. This explained why code that had been unchanged in a year or so suddenly stopped working: Before now, no EPS file had happened to come first.
At first Jeff hacked the system to process the non-EPS files first, then sorted the results as originally desired. Then we realized there may be some rare scenarios where no non-EPS files at all were in the batch, which would trigger the bug. Jeff then had ImageMagick always first process a trivial small JPEG file which was known to work.
That worked, but Jeff then came across the idea of processing an empty image file so we didn't have a dependency on an image that might later be deleted. He tinkered a bit and came up with something suprising but even better. This is his Perl code:
my $first_im = Image::Magick->new;
$first_im->read('');
# (then process all images in any order as originally intended)
I wouldn't have expected an initial read of an empty string filename to solve the problem, but it did. Accompanied by a suitable comment noting the history of the kludge for future software archaeologists, closed the case.
Software's funny, but it's nice when there's a simple -- if counterintuitive -- solution to work around a bug. And I think Jeff has mostly recovered his sanity in the meantime!
Migrating Postgres with Bucardo 4
Bucardo just released a major version (4). The latest version, 4.0.3, can be found at the Bucardo website. The complete list of changes is available on the new Bucardo wiki.
One of the neat tricks you can do with Bucardo is an in-place upgrade of Postgres. While it still requires application downtime, you can minimize your downtime to a very, very small window by using Bucardo. We'll work through an example below, but for the impatient, the basic process is this:
- Install Bucardo and add large tables to a pushdelta sync
- Copy the tables to the new server (e.g. with pg_dump)
- Start up Bucardo and catch things up (e.g. copy all rows changes since step 2)
- Stop your application from writing to the original database
- Do a final Bucardo sync, and copy over non-replicated tables
- Point the application to the new server
With this, you can migrate very large databases from one server to another (or from Postgres 8.2 to 8.4, for example) with a downtime measured in minutes, not hours or days. This is possible because Bucardo supports replicating a "pre-warmed" database - one in which most of the data is already there.
Let's test out this process, using the handy pgbench utility to create a database. We'll go from PostgreSQL 8.2 (the original database, called "A") to PostgreSQL 8.4 (the new database, called "B"). The first step is to create and populate database A:
initdb -D testA echo port=5555 >> testA/postgresql.conf pg_ctl -D testA -l a.log start createdb -p 5555 alpha pgbench -p 5555 -i alpha psql -p 5555 -c 'create user bucardo superuser'
At this point, we have four tables:
$ psql -p 5555 -d alpha -c '\d+'
List of relations
Schema | Name | Type | Owner | Size | Description
--------+----------+-------+----------+------------+-------------
public | accounts | table | postgres | 13 MB |
public | branches | table | postgres | 8192 bytes |
public | history | table | postgres | 0 bytes |
public | tellers | table | postgres | 8192 bytes |
For the purposes of this example, let's make believe that accounts table is actually 13 TB. :) The next step is to prepare the 8.4 database:
initdb -D testB echo port=5566 >> testB/postgresql.conf pg_ctl -D testB -l b.log start
We'll copy everything except the data itself to the new server:
pg_dumpall --schema-only -p 5555 | psql -p 5566 -f -
Because the other tables are very small, we're only going to use Bucardo to copy over the large "accounts" table. So let's install Bucardo and add a sync to do just that:
sudo yum install perl-DBIx-Safe tar xvf Bucardo-4.0.3.tar.gz cd Bucardo-4.0.3 perl Makefile.PL sudo make install
(That's a very quick overview - see the Installation page for more information.)
Let's install bucardo on the new database:
mkdir /tmp/bctest bucardo_ctl install --dbport=5566 --piddir=/tmp/bctest
Set the port so we don't have to keep typing it in:
echo dbport=5566 > .bucardorc
Now teach Bucardo about both databases:
bucardo_ctl add db alpha name=oldalpha port=5555 bucardo_ctl add db alpha name=newalpha port=5566
Finally, create a sync to copy from old to new:
bucardo_ctl add sync pepper type=pushdelta source=oldalpha targetdb=newalpha tables=accounts ping=false
This adds a new sync named "pepper" which is of type pushdelta (master-slave: copy changes from the source table to the target(s).). The source is our old server, named "oldalpha" by Bucardo. The target database is our new server, named "newalpha". The only table in this sync is "accounts", and we set ping as false, which means that we do NOT create a trigger on this table to signal Bucardo that a change has been made, as we will be kicking the sync manually.
At this point, the accounts table has a trigger on it that is capturing which rows have been changed. The next step is to copy the existing table from the old database to the new database. There are many ways to do this, such as a NetApp snapshot, using ZFS, etc., but we'll use the traditional way of a slow but effective pg_dump:
pg_dump alpha -p 5555 --data-only -t accounts | psql -p 5566 -d alpha -f -
This can take as long as it needs to. Reads and writes can still happen against the old server, and changes can be made to the accounts tables. Once that is done, here's the situation:
- The old server is still in production
- The new server has a full but outdated copy of 'accounts'
- The new server has empty tables for everything but 'accounts'
- All changes to the accounts table on the old server are being logged.
Our next step is to start up Bucardo, and let it "catch up" the new server with all changes that have occurred since we created the sync:
bucardo_ctl start
You can keep track of how far along the sync is by tailing the log file (syslog and ./log.bucardo by default) or by checking on the sync itself:
bucardo_ctl status pepper
Once it has caught up (how long depends on how busy the accounts table is, of course), the only disparity should be any rows that have changed since the sync last ran. You can kick off the sync again if you want:
bucardo_ctl kick pepper 0
The final 0 there will allow you to see when the sync has finished.
For the final step, we'll need to move the remainder of the data over. This begins our production downtime window. First, stop the app from writing to the database (reading is okay). Next, once you've confirmed nothing is making changes to the database, make a final kick:
bucardo_ctl kick pepper 0
Next, copy over the other data that was not replicated by Bucardo. This should be small tables that will copy quickly. In our case, we can do it like this:
pg_dump alpha -p 5555 --data-only -T accounts -N bucardo | psql -p 5566 -d alpha -f -
Note that we excluded the schema bucardo, and copied all tables *except* the 'accounts' one.
That's it! You can now point your application to the new server. There are no Bucardo triggers or other artifacts on the new server to clean up. At this point, you can shutdown Bucardo itself:
bucardo_ctl stop
Then shutdown your old Postgres and start enjoying your new 8.4 server!
File test comparison table for shell, Perl, Ruby, and Python
A few days ago, my co-worker Richard asked how in Python you would do the -x Bourne shell and Perl file test that checks whether a file is executable. This is (for me, at least) a really commonly used function but one I hadn't needed to do yet in Python, so I looked it up.
That wasn't so hard to find, but then I wondered about the other shell and Perl file tests that I use all the time. Finding equivalents for those was harder than I expected. A web search didn't turn much up aside from language holy wars and limited answers, but I didn't find any exhaustive list.
So I made my own. Below is a table comparing file test operators in the original Bourne shell-compatibles bash, ksh, and zsh; Perl's expanded set; Ruby's which was derived first from Perl; and equivalent Python code.
There are still some blanks where I didn't find a good equivalent. Of course I'm sure it's possible with enough custom logic to achieve the same end, but I have tried to stick with relatively simple formulations using built-in functions for now. I'll be happy to fill in the blanks if any readers make suggestions.
Performance notes on avoiding multiple stats of the same file:
- Starting with Perl 5.9.1, file tests can be "stacked" and will use a single stat for all tests, e.g. -f -x file. In older versions of Perl you can do -f file && -x _ instead.
- Ruby's File::Stat class can be used to cache a stat for multiple tests.
- Python's os.stat(file).st_mode can be stored and used for multiple tests.
Unless otherwise specified, these tests follow symbolic links and operate on the target of the link, rather than the link itself.
All tests return boolean true or false unless otherwise noted.
| Test | bash/ksh/zsh | Perl | Ruby | Python |
|---|---|---|---|---|
| File is readable by effective uid/gid | -r 'file' | test ?r, 'file' | ||
| File.readable?('file') | ||||
| File is writable by effective uid/gid | -w 'file' | test ?w, 'file' | ||
| File.writable?('file') | ||||
| File is executable by effective uid/gid | -x 'file' | test ?x, 'file' | ||
| File.executable?('file') | ||||
| File is owned by effective uid | -O file | -o 'file' | test ?o, 'file' | os.stat('file').st_uid == os.geteuid() |
| File.owned?('file') | ||||
| File is owned by the effective gid | -G file | (stat('file'))[5] == $) | test ?G, 'file' | os.stat('file').st_gid == os.getegid() |
| File.grpowned?('file') | ||||
| File is readable by real uid/gid | -r file | -R 'file' | test ?R, 'file' | os.access('file', os.R_OK) |
| File.readable_real?('file') | ||||
| File is writable by real uid/gid | -w file | -W 'file' | test ?W, 'file' | os.access('file', os.W_OK) |
| File.writable_real?('file') | ||||
| File is executable by real uid/gid | -x file | -X 'file' | test ?X, 'file' | os.access('file', os.X_OK) |
| File.executable_real?('file') | ||||
| File is owned by real uid | -O 'file' | test ?O, 'file' | os.stat('file').st_uid == os.getuid() | |
| File exists | -e file | -e 'file' | test ?e, 'file' | os.path.exists('file') |
| -a file | File.exist?('file') | |||
| File has zero size (is empty) | -f file -a ! -s file | -z 'file' | test ?z, 'file' | os.path.getsize('file') == 0 |
| File.zero?('file') | os.stat('file').st_size == 0 | |||
| File exists and has size greater than zero | -s file | -s 'file' (boolean and returns size in bytes) | test ?s, 'file' (boolean: returns nil if doesn't exist or has zero size, size of the file otherwise) | os.path.getsize('file') > 0 |
| File.size?('file') (same) | os.stat('file').st_size > 0 | |||
| File exists, return size in bytes | -s 'file' | File.size('file') | os.path.getsize('file') | |
| os.stat('file').st_size | ||||
| File is a plain file | -f file | -f 'file' | test ?f, 'file' | os.path.isfile('file') |
| File.file?('file') | stat.S_ISREG(os.stat('file').st_mode) | |||
| File is a directory | -d file | -d 'file' | test ?d, 'file' | os.path.isdir('file') |
| File.directory?('file') | stat.S_ISDIR(os.stat('file').st_mode) | |||
| File is a symbolic link | -h file | -l 'file' | test ?l, 'file' | os.path.islink('file') |
| -L file | File.symlink?('file') | stat.S_ISLNK(os.lstat('file').st_mode) | ||
| File is a named pipe (FIFO) | -p file | -p 'file' (can also be used on a filehandle) | test ?p, 'file' | stat.S_ISFIFO(os.stat('file').st_mode) |
| File.pipe?('file') | ||||
| File is a socket | -S file | -S 'file' | test ?S, 'file' | stat.S_ISSOCK(os.stat('file').st_mode) |
| File.socket?('file') | ||||
| File is a block special file | -b file | -b 'file' | test ?b, 'file' | stat.S_ISBLK(os.stat('file').st_mode) |
| File.blockdev?('file') | ||||
| File is a character special file | -c file | -c 'file' | test ?c, 'file' | stat.S_ISCHR(os.stat('file').st_mode) |
| File.chardev?('file') | ||||
| File type (returns string 'file', 'directory', 'characterSpecial', 'blockSpecial', 'fifo', 'link', 'socket', or 'unknown' | File.ftype('file') | |||
| Filehandle or descriptor is opened to a tty | -t fd | -t $fh | fd.isatty | os.isatty(fd) |
| fd.tty? | ||||
| File has setuid bit set | -u file | -u 'file' | test ?u, 'file' | os.stat('file').st_mode & stat.S_ISGID |
| File.setuid?('file') | ||||
| File has setgid bit set | -g file | -g 'file' | test ?g, 'file' | os.stat('file').st_mode & stat.S_ISUID |
| File.setgid?('file') | ||||
| File has sticky bit set | -k file | -k 'file' | test ?k, 'file' | os.stat('file').st_mode & stat.S_ISVTX |
| File.sticky?('file') | ||||
| File is an ASCII text file (heuristic guess) | -T 'file' | |||
| File is a "binary" file (opposite of -T) | -B 'file' | |||
| File modification time | (stat('file'))[9] | test ?M, 'file' (returns Time object) | os.stat('file').st_mtime | |
| -M 'file' (script start time minus file modification time, in days) | File.mtime('file') (same) | |||
| File access time | (stat('file'))[8] | test ?A, 'file' (returns Time object) | os.stat('file').st_atime | |
| -A 'file' (script start time minus file access time, in days) | File.atime('file') (same) | |||
| Inode change time (Unix) | (stat('file'))[10] | test ?C, 'file' (returns Time object) | os.stat('file').st_ctime | |
| -C 'file' (script start time minus inode change time, in days) | File.ctime('file') (same) | |||
| File has been modified since it was last read | -N file | |||
| file1 is newer (according to modification date) than file2, or if file1 exists and file2 does not | file1 -nt file2 | (stat('file1'))[9] > (stat('file2'))[9] | test ?>, 'file1', 'file2' | os.path.exists('file1') and (not os.path.exists('file2') or os.stat('file1').st_mtime > os.stat('file2').st_mtime) |
| file1 is older than file2, or if file2 exists and file1 does not | file1 -ot file2 | (stat('file1'))[9] < (stat('file2'))[9] | test ?<, 'file1', 'file2' | os.path.exists('file2') and (not os.path.exists('file1') or os.stat('file1').st_mtime < os.stat('file2').st_mtime) |
| file1 and file2 refer to the same device and inode numbers | file1 -ef file2 | join(':', (stat('file1'))[0,1]) eq join(':', (stat('file2'))[0,1]) | test ?-, 'file1', 'file2' | os.path.samefile('file1', 'file2') |
| file1 and file2 have the same modification times | (stat('file1'))[9] == (stat('file2'))[9] | test ?=, 'file1', 'file2' | os.stat('file1').st_mtime == os.stat('file2').st_mtime |
Complete details are in the manuals for each language:
- bash: `man bash` and search for "CONDITIONAL EXPRESSIONS"
- ksh: `man pdksh` and search for "test expression"
- zsh: `man zshmisc` and search for "CONDITIONAL EXPRESSIONS"
- Perl: `perldoc -f -f`, filetest pragma
- Ruby: File class (summary version) and test() built-in, IO class docs
- Python: os.stat, stat() results, os.path
Perl's Scalar::Util::dualvar
I just came across this fun Perl function that I can't think of a (good) use for, but have to share.
In the Scalar::Util module is the function dualvar:
Returns a scalar that has the value NUM in a numeric context and the value STRING in a string context.
$foo = dualvar 10, "Hello";
$num = $foo + 2; # 12
$str = $foo . " world"; # Hello world
Using that in the right place could lead a future programmer down some fun debugging paths!
Text sequences
Somebody recently asked on the Postgres mailing list about "Generating random unique alphanumeric IDs". While there were some interesting solutions given, from a simple Pl/pgsql function to using mathematical transformations, I'd like to lay out a simple and powerful solution using Pl/PerlU
First, to paraphrase the original request, the poster needed a table to have a text column be its primary key, and to have a five-character alphanumeric string used as that key. Let's knock out a quick function using Pl/PerlU that solves the generation part of the question:
DROP FUNCTION IF EXISTS nextvalalpha(TEXT);
CREATE FUNCTION nextvalalpha(TEXT)
RETURNS TEXT
LANGUAGE plperlu
AS $_$
use strict;
my $numchars = 5;
my @chars = split // => qw/abcdefghijkmnpqrstwxyzABCDEFGHJKLMNPQRSTWXYZ23456789/;
my $value = join '' => @chars[map{rand @chars}(1..$numchars)];
return $value;
$_$;
Pretty simple: it simply pulls a number of random characters from a string (with some commonly confused letters and number removed) and returns a string:
greg=# SELECT nextvalalpha('foo'); nextvalalpha -------------- MChNf (1 row) greg=# SELECT nextvalalpha('foo'); nextvalalpha -------------- q4jHm (1 row)
So let's set up our test table. Since Postgres can use many things column DEFAULTS, including user-defined functions, this is pretty straightforward:
DROP TABLE IF EXISTS seq_test;
CREATE TABLE seq_test (
id VARCHAR(5) NOT NULL DEFAULT nextvalalpha('foo'),
city TEXT,
state TEXT
);
A quick test shows that the id column is auto-propagated with some random values:
greg=# PREPARE abc(TEXT,TEXT) AS INSERT INTO seq_test(city,state) greg-# VALUES($1,$2) RETURNING id; greg=# EXECUTE abc('King of Prussia', 'Pennsylvania'); id ------- 9zbsd (1 row) INSERT 0 1 greg=# EXECUTE abc('Buzzards Bay', 'Massachusetts'); id ------- 4jJ5D (1 row) INSERT 0 1
So far so good. But while those returned values are random, they are not in any way unique, which a primary key needs to be. First, let's create a helper table to keep track of which values we've already seen. We'll also track the 'name' of the sequence as well, to allow for more than one unique set of sequences at a time:
DROP TABLE IF EXISTS alpha_sequence; CREATE TABLE alpha_sequence ( sname TEXT, value TEXT ); CREATE UNIQUE INDEX alpha_sequence_unique_value ON alpha_sequence(sname,value);
Now we tweak the original function to use this new table.
CREATE OR REPLACE FUNCTION nextvalalpha(TEXT)
RETURNS TEXT
SECURITY DEFINER
LANGUAGE plperlu
AS $_$
use strict;
my $sname = shift;
my @chars = split // => qw/abcdefghijkmnpqrstwxyzABCDEFGHJKLMNPQRSTWXYZ23456789/;
my $numchars = 5;
my $toomanyloops = 10000; ## Completely arbitrary pick
my $loops = 0;
my $SQL = 'SELECT 1 FROM alpha_sequence WHERE sname = $1 AND value = $2';
my $sth = spi_prepare($SQL, 'text', 'text');
my $value = '';
SEARCHING:
{
## Safety valve
if ($loops++ >= $toomanyloops) {
die "Could not find a unique value, even after $toomanyloops tries!\n";
}
## Build a new value, then test it out
$value = join '' => @chars[map{rand @chars}(1..$numchars)];
my $count = spi_exec_prepared($sth,$sname,$value)->{processed};
redo if $count >= 1;
}
## Store it and commit the change
$SQL = 'INSERT INTO alpha_sequence VALUES ($1,$2)';
$sth = spi_prepare($SQL, 'text', 'text');
spi_exec_prepared($sth,$sname,$value);
return $value;
$_$;
Alright, that seems to work well, and prevents duplicate values. Or does it? Recall that one of the properties of sequences in Postgres is that they live outside of the normal MVCC rules. In other words, once you get a number via a call to nextval(), nobody else can get that number again (even you!) - regardless of whether you commit or rollback. Thus, sequences are guaranteed unique across all transactions and sessions, even if used for more than one table, called manually, etc. Can we do the same with our text sequence? Yes!
For this trick, we'll need to ensure that we only return a new value if we are 100% sure it is unique. We also need to record the value returned, even if the transaction that calls it rolls back. In other words, we need to make a small 'subtransaction' that commits, regardless of the rest of the transaction. Here's the solution:
CREATE OR REPLACE FUNCTION nextvalalpha(TEXT)
RETURNS TEXT
SECURITY DEFINER
LANGUAGE plperlu
AS $_$
use strict;
use DBI;
my $sname = shift;
my @chars = split // => qw/abcdefghijkmnpqrstwxyzABCDEFGHJKLMNPQRSTWXYZ23456789/;
my $numchars = 5;
my $toomanyloops = 10000;
my $loops = 0;
## Connect to this very database, but with a new session
my $port = spi_exec_query('SHOW port')->{rows}[0]{port};
my $dbname = spi_exec_query('SELECT current_database()')->{rows}[0]{current_database};
my $dbuser = spi_exec_query('SELECT current_user')->{rows}[0]{current_user};
my $dsn = "dbi:Pg:dbname=$dbname;port=$port";
my $dbh = DBI->connect($dsn, $dbuser, '', {AutoCommit=>1,RaiseError=>1,PrintError=>0});
my $SQL = 'SELECT 1 FROM alpha_sequence WHERE sname = ? AND value = ?';
my $sth = $dbh->prepare($SQL);
my $value = '';
SEARCHING:
{
## Safety valve
if ($loops++ >= $toomanyloops) {
die "Could not find a unique value, even after $toomanyloops tries!\n";
}
## Build a new value, then test it out
$value = join '' => @chars[map{rand @chars}(1..$numchars)];
my $count = $sth->execute($sname,$value);
$sth->finish();
redo if $count >= 1;
}
## Store it and commit the change
$SQL = 'INSERT INTO alpha_sequence VALUES (?,?)';
$sth = $dbh->prepare($SQL);
$sth->execute($sname,$value); ## Does a commit
## Only now do we return the value to the caller
return $value;
$_$;
What's the big difference between this one and the previous version? Rather than examine the alpha_sequence table in our /current/ session, we figure out who and where we are, and make a completely separate connection to the same database using DBI. Then we find an unused value, INSERT that value into the alpha_sequence table, and commit that outside of our current transaction.Only then can we return the value to the caller.
Postgres sequences also have a currval() function, which returns the last value returned via a nextval() in the current session. The lastval() function is similar, but it returns the last call to nextval(), regardless of the name used. We can make a version of these easy enough, because Pl/Perl functions have a built-in shared hash named '%_SHARED'. Thus, we'll add two new lines to the end of the function above:
...
$sth->execute($sname,$value); ## Does a commit
$_SHARED{nva_currval}{$sname} = $value;
$_SHARED{nva_lastval} = $value;
...
Then we create a simple function to display that value, as well as throw an error if called too early - just like nextval() does:
DROP FUNCTION IF EXISTS currvalalpha(TEXT)
CREATE FUNCTION currvalalpha(TEXT)
RETURNS TEXT
SECURITY DEFINER
LANGUAGE plperlu
AS $_$
my $sname = shift;
if (exists $_SHARED{nva_currval}{$sname}) {
return $_SHARED{nva_currval}{$sname};
}
else {
die qq{currval of text sequence "$sname" is not yet defined in this session\n};
}
$_$;
Now the lastval() version:
DROP FUNCTION IF EXISTS lastvalalpha();
CREATE FUNCTION lastvalalpha()
RETURNS TEXT
SECURITY DEFINER
LANGUAGE plperlu
AS $_$
if (exists $_SHARED{nva_lastval}) {
return $_SHARED{nva_lastval};
}
else {
die qq{lastval (text) is not yet defined in this session\n};
}
$_$;
For the next tests, we'll create a normal (integer) sequence, and see how it acts compared to our newly created text sequence:
DROP SEQUENCE IF EXISTS newint; CREATE SEQUENCE newint STARTS WITH 42; greg=# SELECT lastval(); ERROR: lastval is not yet defined in this session greg=# SELECT currval('newint'); ERROR: currval of sequence "newint" is not yet defined in this session greg=# SELECT nextval('newint'); nextval --------- 42 (1 row) greg=# SELECT currval('newint'); currval --------- 42 greg=# SELECT lastval(); lastval --------- 42
greg=# SELECT lastvalalpha(); ERROR: error from Perl function "lastvalalpha": lastval (text) is not yet defined in this session greg=# SELECT currvalalpha('newtext'); ERROR: error from Perl function "currvalalpha": currval of text sequence "newtext" is not yet defined in this session greg=# SELECT nextvalalpha('newtext'); nextvalalpha -------------- rRwJ6 greg=# SELECT currvalalpha('newtext'); currvalalpha -------------- rRwJ6 greg=# SELECT lastvalalpha(); lastvalalpha -------------- rRwJ6
There is one more quick optimization we could make. Since the %_SHARED hash is available across our session, there is no need to do anything in the function more than once if we can cache it away. In this case, we'll cache away the server information we look up, the database handle, and the prepares. Our final function looks like this:
CREATE OR REPLACE FUNCTION nextvalalpha(TEXT)
RETURNS TEXT
SECURITY DEFINER
LANGUAGE plperlu
AS $_$
use strict;
use DBI;
my $sname = shift;
my @chars = split // => qw/abcdefghijkmnpqrstwxyzABCDEFGHJKLMNPQRSTWXYZ23456789/;
my $numchars = 5;
my $toomanyloops = 10000;
my $loops = 0;
## Connect to this very database, but with a new session
if (! exists $_SHARED{nva_dbi}) {
my $port = spi_exec_query('SHOW port')->{rows}[0]{port};
my $dbname = spi_exec_query('SELECT current_database()')->{rows}[0]{current_database};
my $dbuser = spi_exec_query('SELECT current_user')->{rows}[0]{current_user};
my $dsn = "dbi:Pg:dbname=$dbname;port=$port";
$_SHARED{nva_dbi} = DBI->connect($dsn, $dbuser, '', {AutoCommit=>1,RaiseError=>1,PrintError=>0});
my $dbh = $_SHARED{nva_dbi};
my $SQL = 'SELECT 1 FROM alpha_sequence WHERE sname = ? AND value = ?';
$_SHARED{nva_sth_check} = $dbh->prepare($SQL);
$SQL = 'INSERT INTO alpha_sequence VALUES (?,?)';
$_SHARED{nva_sth_add} = $dbh->prepare($SQL);
}
my $value = '';
SEARCHING:
{
## Safety valve
if ($loops++ >= $toomanyloops) {
die "Could not find a unique value, even after $toomanyloops tries!\n";
}
## Build a new value, then test it out
$value = join '' => @chars[map{rand @chars}(1..$numchars)];
my $count = $_SHARED{nva_sth_check}->execute($sname,$value);
$_SHARED{nva_sth_check}->finish();
redo if $count >= 1;
}
## Store it and commit the change
$_SHARED{nva_sth_add}->execute($sname,$value); ## Does a commit
$_SHARED{nva_currval}{$sname} = $value;
$_SHARED{nva_lastval} = $value;
return $value;
$_$;
Having the ability to reach outside the database in Pl/PerlU - even if simply to go back in again! - can be a powerful tool, and allows us to do things that might otherwise seem impossible.
Debugging prepared statements
I was recently tasked with the all-too-familiar task for DBAs of "why is this script running so slow?". After figuring out exactly which script and where it was running from, I narrowed down the large number of SQL commands it was issuing to one particularly slow one, that looked something like this in the pg_stat_activity view:
current_query ------------- SELECT DISTINCT id FROM containers WHERE code LIKE $1
Although the query ran too quick to really measure a finite time just by watching pg_stat_activity, it did show up quite often. So it was likely slow *and* being called many times in a loop somewhere. The use of 'LIKE' always throws a yellow flag, so those factors encouraged me look closer into the query.
While the table in question did have an index on the 'code' column, it was not being used. This is because LIKE (on non-C locale databases) cannot work against normal indexes - it needs a simpler character by character index. In Postgres, you can achieve this by using some of the built in operator classes when creating an index. More details can be found at the documentation on operator classes. What I ended up doing was using text_pattern_ops:
SET maintenance_work_mem = '2GB'; CREATE INDEX CONCURRENTLY containers_code_textops ON containers (code text_pattern_ops);
Since this was on a production system (yes, I tested on a QA box first!), the CONCURRENTLY phrase ensured that the index did not block any reads or writes on the table while the index was being built. Details on this awesome option can be found in the docs on CREATE INDEX.
After the index was created, the following test query went from 800ms to 0.134ms!:
EXPLAIN ANALYZE SELECT * FROM containers WHERE code LIKE 'foobar%';
I then created a copy of the original script, stripped out any parts that made changes to the database, added a rollback to the end of it, and tested the speed. Still slow! Recall that the original query looked like this:
SELECT DISTINCT id FROM containers WHERE code LIKE $1
The $1 indicates that this is a prepared query. This leads us to the most important lesson of this post: whenever you see that a prepared statement is being used, it's not enough to test with a normal EXPLAIN or EXPLAIN ANALYZE. You must emulate what the script (e.g. the database driver) is really doing. So from psql, I did the following:
PREPARE foobar(text) AS SELECT DISTINCT id FROM containers WHERE code LIKE $1;
EXPLAIN ANALYZE EXECUTE('foobar%');
Bingo! This time, the new index was *not* being used. This is the great trade-off of prepared statements - while it allows you to prepare and rewrite the query only once, the planner cannot anticipate what you might pass in as a possible argument, so it makes the best generic plan possible. Thus, your EXPLAIN of the same query using literals or placeholders via PREPARE may look very different.
While it's possible to make workarounds at the database level for the problem of prepared statements using the "wrong" plan, in this case it was simply easier to tell the existing script not to use prepared statements at all for this one query. As the script was using DBD::Pg, the solution was to simply use the pg_server_prepare attribute like so:
$dbh->{pg_server_prepare} = 0;
my $sth = $dbh->prepare('SELECT DISTINCT id FROM containers WHERE code LIKE ?');
$dbh->{pg_server_prepare} = 1;
The effect of this inside of DBD::Pg is that instead of using PQprepare and then PQexecPrepared for each call to $sth->execute(), DBD::Pg will, for every call to $sth->execute(), quote the parameter itself, build a string containing the original SQL statement and the quoted literal, and send it to the backend via PQexec. Normally not something you want to do, but the slight overhead of doing it that way was completely overshadowed by the speedup of using the new index.
The final result: the script that used to take over 6 hours to run now only takes about 9 minutes to complete. Not only are the people using the script much happier, but it means less load on the database.
Perl+Postgres: changes in DBD::Pg 2.15.1
DBD::Pg, the Perl interface to Postgres, recently released version 2.15.1. The last two weeks has seen a quick flurry of releases: 2.14.0, 2.14.1, 2.15.0, and 2.15.1. Per the usual versioning convention, the numbers on the far right (in this case the "dot one" releases) were simply bug fixes, while 2.14.0 and 2.15.0 introduced API and/or major internal changes. Some of these changes are explained below.
From the Changes file for 2.15.0:
CHANGE: - Allow execute_array and bind_param_array to take oddly numbered items, such that DBI will make missing entries undef/null (CPAN bug #39829) [GSM]
The Perl Database Interface (DBI) has a neat feature to allow you to execute many sets of items at one time, known as execute_array. The basic format is to pass in an list of arrays, in which each array contains the placeholders needed to execute the query. For example:
## Create a simple test table with two columns
$dbh->do('DROP TABLE IF EXISTS people');
$dbh->do('CREATE TABLE people (id int, fname text)');
## Pass in all ids as a single array
my @numbers = (1,2,3);
## Pass in all names as a single array
my @names = ("Garrett", "Viktoria", "Basso");
## Prepare the statement
my $sth = $dbh->prepare('INSERT INTO people VALUES (?, ?)');
## Execute the statement multiple times (three times in this case)
$sth->execute_array(undef, \@numbers, \@names);
## (the first argument is an optional argument hash which we don't use here)
## Pull back and display the rows from our new table
$SQL = 'SELECT id, fname FROM people ORDER BY fname';
for my $row (@{$dbh->selectall_arrayref($SQL)}) {
print "Found: $row->[0] : $row->[1]\n";
}
$ perl testscript.pl
Found: 3 : Basso
Found: 1 : Garrett
Found: 2 : Viktoria
In 2.15.0, we loosened the requirement that the number of placeholders in each array match up with the expected number. Per the DBI spec, any "missing" items are considered undef, which maps to a SQL NULL. Thus:
$dbh->do('DROP TABLE IF EXISTS people');
$dbh->do('CREATE TABLE people (id int, fname text)');
## Note that this time there are only two ids given, not three:
my @numbers = (1,2);
my @names = ("Garrett", "Viktoria", "Basso");
my $sth = $dbh->prepare("INSERT INTO people VALUES (?, ?)");
$sth->execute_array(undef, \@numbers, \@names);
## Show a question mark for any null ids
$SQL = q{
SELECT CASE WHEN id IS NULL THEN '?' ELSE id::text END, fname
FROM people ORDER BY fname
};
for my $row (@{$dbh->selectall_arrayref($SQL)}) {
print "Found: $row->[0] : $row->[1]\n";
}
$ perl testscript2.pl
Found: ? : Basso
Found: 1 : Garrett
Found: 2 : Viktoria
Also note that bind_param_array is an alternate way to add the list of arrays before the execute is called. This is similar in concept to a regular execute: if you bind the values first, you can call execute without any arguments:
... $sth->bind_param_array(1, \@numbers); $sth->bind_param_array(2, \@names); $sth->execute_array(undef); ...
CHANGE: - Use PQexecPrepared even when no placeholders (CPAN bug #48155) [GSM]
Sending queries to Postgres via DBD::Pg usually involves two steps: prepare and execute. The prepare is done one time, while the execute can be called many times, often times with different arguments. Previously, DBD::Pg would call PQexec for queries that had no placeholders. However, the ability to handle placeholders smoothly is only one advantage of using server-side prepares in Postgres. The other advantage is that Postgres only has to parse the query a single time, in the initial prepare. In 2.15.0, we use PQexecPrepared for all queries, whether they have placeholders or not. The upshot of this is that multiple calls to the execute() function will be a little bit faster, and that we only use PQexec when we really have to.
CHANGE: - Fix quoting of booleans to respect more Perlish variants (CPAN bug #41565) [GSM]
In previous versions, the mapping of Perl vars to booleans was very simple, and did only simple 0/1 mapping. However, Perl's values of "truth" is richer than that. We can now do things like this:
for my $name ('0', '1', '0E0', '0 but true', 'F', 'T', 'TRUE', 'false') {
printf qq{Value '%s' is %s\n}, $name, $dbh->quote($name, {pg_type => PG_BOOL});
}
$ perl testscript3.pl
Value '0' is FALSE
Value '1' is TRUE
Value '0E0' is TRUE
Value '0 but true' is TRUE
Value 'F' is FALSE
Value 'T' is TRUE
Value 'TRUE' is TRUE
Value 'false' is FALSE
CHANGE:
- Return ints and bools-cast-to-number from the db as true Perlish numbers.
(CPAN bug #47619) [GSM]
This one is a little more subtle. When a value is returned from the database, it gets mapped back to a string. So even if the value in the database came from an INTEGER column, by the time it made it's way back to your Perl script it was a string that happened to hold an integer value. DBD::Pg now attempts to cast some types to their Perl equivalent. This is normally hard to see without peering inside Perl internals, but using Data::Dumper can show you the difference:
## Ask Postgres to return a string and an integer
$SQL = 'SELECT 123::text, 123::integer';
$info = $dbh->selectall_arrayref($SQL)->[0];
print Dumper $info;
## Older versions of DBD::Pg give:
$VAR1 = [
'123',
'123'
];
## The new and improved version gives:
$VAR1 = [
'123',
123
];
A small difference, but not unimportant - this change came about through a bug request, as it was causing problems when DBD::Pg was interacting with JSON::XS. Special thanks to Tim Bunce, (author of DBI, maintainer of the amazing NYTProf, and all around Perl guru) who found an important bug regarding this solution in 2.14.0, which led to the quick release of 2.14.1. Lesson learned: don't try converting ints to floats via sv_setnv.
Most of the other changes to 2.14 and 2.15 are bug fixes of one sort or another. To keep up on the changes or to talk about the project more, please join the mailing list
Comparing databases with check_postgres
One of the more recent additions to check_postgres, the all-singing, all-dancing Postgres monitoring tool, is the "same_schema" action. This was necessitated by clients who wanted to make sure that their schemas were identical across different servers. The two use cases I've seen are servers that are being replicated by Bucardo or Slony, and servers that are doing horizontal sharding (e.g. same schema and database on different servers: which server you go to depends on (for example) your customer id). Oft times a new index fails to make it to one of the slaves, or some function is tweaked on one server by a developer, who then forgets to change it back or propagate it. This program allows a quick and automatable check for such problems.
The idea behind the same_schema check is simple: we walk the schema and check for any differences, then throw a warning if any are found. In this case, we're using the term "schema" in the classic sense of a description of your database objects. Thus, one of the things we check is that all the schemas (in the classic RDBMS sense of a container of other database objects) are the same, when running the "same_schema" check. Only slightly confusing. :)
Not only is this program nice for monitoring (e.g. as a Nagios check), but if you pass in a --verbose argument, you get a simple not-all-on-one-line breakdown of all the differences between the two databases. Let's do a quick example.
First, we download and install check_postgres. We'll pull straight from a git repository for check_postgres. While we have our own repo at bucardo.org, we also are keeping it in sync with a tree at github.org, so we'll use that one:
git clone git://github.com/bucardo/check_postgres.git cd check_postgres perl Makefile.PL make make test sudo make install
Let's create a Postgres cluster with the initdb command, start it up, then create two new databases to compare to each other.
initdb -D cptest echo port=5555 >> cptest/postgresql.conf pg_ctl -D cptest -l cp.log start psql -p 5555 -c 'CREATE DATABASE yin' psql -p 5555 -c 'CREATE DATABASE yang'
We're ready to run the script. By default, it outputs things in a Nagios-friendly manner. We should see an 'OK' because the two databases are identical:
./check_postgres.pl --action=same_schema --dbport=5555 --dbname=yin --dbport2=5555 --dbname2=yang POSTGRES_SAME_SCHEMA OK: DB "yin" (port=5555 => 5555) Both databases have identical items | time=0.01
The message could be clearer and show both database names, but the check worked and showed that things are exactly the same. Let's throw some differences in and run it again:
psql -p 5555 -d yin -c 'create table foobar(a int primary key, b text, c text)' psql -p 5555 -d yang -c 'create table foobar(a int, b text, c varchar(99))' psql -p 5555 -d yin -c 'create schema yinonly' psql -p 5555 -d yang -c 'create table pineapple(id int)' ./check_postgres.pl --action=same_schema --dbport=5555 --dbname=yin --dbport2=5555 --dbname2=yang POSTGRES_SAME_SCHEMA CRITICAL: DB "yin" (port=5555 => 5555) Databases were different. Items not matched: 5 | time=0.01 Schema in 1 but not 2: yinonly Table in 2 but not 1: public.pineapple Column "a" of "public.foobar": nullable is NO on 1, but YES on 2. Column "c" of "public.foobar": type is text on 1, but character varying on 2. Table "public.foobar" on 1 has constraint "public.foobar_pkey", but 2 does not.
It works, but a little messy for human consumption. Nagios requires everything to be in a single line, but we'll add a --verbose argument to ask the script for prettier formatting:
./check_postgres.pl --action=same_schema --dbport=5555 --dbname=yin --dbport2=5555 --dbname2=yang POSTGRES_SAME_SCHEMA CRITICAL: DB "yin" (port=5555 => 5555) Databases were different. Items not matched: 5 | time=0.01 Schema in 1 but not 2: yinonly Table in 2 but not 1: public.pineapple Column "a" of "public.foobar": nullable is NO on 1, but YES on 2. Column "c" of "public.foobar": type is text on 1, but character varying on 2. Table "public.foobar" on 1 has constraint "public.foobar_pkey", but 2 does not.
There are also ways to filter the output, for times when you have known differences. For example, to exclude any tables with the word 'bucardo' in them, you could add this argument:
--warning="notable=bucardo"
The online documentation has more details about all the filtering options.
So what kind of things do we check for? Right now, we are checking:
- users (existence and powers, i.e. createdb, superuser)
- schemas
- tables
- sequences
- views
- triggers
- constraints
- columns
- functions (including volatility, strictness, etc.)
Got something else we aren't covering? Send in a patch, or a quick request, to the mailing list.
Bucardo and truncate triggers
Version 8.4 of Postgres was recently released. One of the features that hasn't gotten a lot of press, but which I'm excited about, is truncate triggers. This fixes a critical hole in trigger-based PostgreSQL replication systems, and support for these new triggers is now working in the Bucardo replication program.
Truncate triggers were added to Postgres by Simon Riggs (thanks Simon!), and unlike other types of triggers (UPDATE, DELETE, and INSERT), they are statement-level only, as truncate is not a row-level action.
Here's a quick demo showing off the new triggers. This is using the development version of Bucardo - a major new version is expected to be released in the next week or two that will include truncate trigger support and many other things. If you want to try this out for yourself, just run:
$ git clone git-clone http://bucardo.org/bucardo.git/
Bucardo does three types of replication; for this example, we'll be using the 'pushdelta' method, which is your basic "master to slaves" relationship. In addition to the master database (which we'll name A) and the slave database (which we'll name B), we'll create a third database for Bucardo itself.
$ initdb -D bcdata $ initdb -D testA $ initdb -D testB
(Technically, we are creating three new database clusters, and since we are doing this as the postgres user, the default database for all three will be 'postgres')
Let's give them all unique port numbers:
$ echo port=5400 >> bcdata/postgresql.conf $ echo port=5401 >> testA/postgresql.conf $ echo port=5402 >> testB/postgresql.conf
Now start them all up:
$ pg_ctl start -D bcdata -l bc.log $ pg_ctl start -D testA -l A.log $ pg_ctl start -D testB -l B.log
We'll create a simple test table on both sides:
$ psql -d postgres -p 5401 -c 'CREATE TABLE trtest(id int primary key)' $ psql -d postgres -p 5402 -c 'CREATE TABLE trtest(id int primary key)'
Before we go any further, let's install Bucardo itself. Bucardo is a Perl daemon that uses a central database to store its configuration information. The first step is to create the Bucardo schema. This, like almost everything else with Bucardo, is done with the 'bucardo_ctl' script. The install process is interactive:
$ bucardo_ctl install --dbport=5400 This will install the bucardo database into an existing Postgres cluster. Postgres must have been compiled with Perl support, and you must connect as a superuser We will create a new superuser named 'bucardo', and make it the owner of a new database named 'bucardo' Current connection settings: 1. Host:2. Port: 5400 3. User: postgres 4. PID directory: /var/run/bucardo Enter a number to change it, P to proceed, or Q to quit: P Version is: 8.4 Attempting to create and populate the bucardo database and schema Database creation is complete Connecting to database 'bucardo' as user 'bucardo' Updated configuration setting "piddir" Installation is now complete. If you see any unexpected errors above, please report them to bucardo-general@bucardo.org You should probably check over the configuration variables next, by running: bucardo_ctl show all Change any setting by using: bucardo_ctl set foo=bar
Because we don't want to tell the bucardo_ctl program our custom port each time we call it, we'll store that info into the ~/.bucardorc file:
$ echo dbport=5400 > ~/.bucardorc
Let's double check that everything went okay by checking the list of databases that Bucardo knows about:
$ bucardo_ctl list db There are no entries in the 'db' table.
Time to teach Bucardo about our two new databases. The format for the add commands is: bucardo_ctl add [type of thing] [name of thing within the database] [arguments of foo=bar format]
$ bucardo_ctl add database postgres name=master port=5401 Database added: master $ bucardo_ctl add database postgres name=slave1 port=5402 Database added: slave1
Before we go any further, let's look at our databases:
$ bucardo_ctl list dbs Database: master Status: active Conn: psql -h -p 5401 -U bucardo -d postgres Database: slave1 Status: active Conn: psql -h -p 5402 -U bucardo -d postgres
Note that by default we connect as the 'bucardo' user. This is a highly recommended practice, for safety and auditing. Since that user obviously does not exist on the newly created databases, we need to add them in:
$ psql -p 5401 -c 'create user bucardo superuser' $ psql -p 5402 -c 'create user bucardo superuser'
Now we need to teach Bucardo about the tables we want to replicate:
$ bucardo_ctl add table trtest db=master herd=herd1 Created herd "herd1" Table added: public.trtest
A herd is simply a named connection of tables. Typically, you put tables that are linked together by foreign keys or other logic into a herd so that they all get replicated at the same time.
The final setup step is to create a replication event, which in Bucardo is known as a 'sync':
$ bucardo_ctl add sync willow source=herd1 targetdb=slave1 type=pushdelta
NOTICE: Starting validate_sync for willow
CONTEXT: SQL statement "SELECT validate_sync('willow')"
Sync added: willow
This command actually did quite a bit of work behind the scenes, including creating all the supporting schemas, tables, functions, triggers, and indexes that Bucardo will need.
We are now ready to start Bucardo up. Simple enough:
$ bucardo_ctl start Checking for existing processes Starting Bucardo
Let's add a row to the master table and make sure it goes to the slave:
$ psql -p 5401 -c 'insert into trtest(id) VALUES (1)' INSERT 0 1 $ psql -p 5402 -c 'select * from trtest' id ---- 1 (1 row)
Looks fine, so let's try out the truncate. On versions of Postgres less than 8.4, there was no way for Bucardo (or Slony) to know that a truncate had been run, so the rows were removed from the master but not from the slave. We'll do a truncate and add a new row in a single operation:
$ psql -p 5401 -c 'begin; truncate table trtest; insert into trtest values (2); commit' COMMIT $ psql -p 5402 -c 'select * from trtest' id ---- 2 (1 row)
It works! Let's clean up our test environment for good measure:
$ bucardo_ctl stop $ pg_ctl stop -D bcdata $ pg_ctl stop -D testA $ pg_ctl stop -D testB
As mentioned, there are three types of syncs in Bucardo. The other type that can make use of truncate triggers is the 'swap' sync, aka "master to master". I've not yet decided on the behavior for such syncs, but one possibility is simply:
- Database A gets truncated at time X
- Bucardo truncates database B, then discards all delta rows older than X for both A
and B, and all delta rows for B - Everything after X gets processed as normal (conflict resolution, etc.)
- The same thing for a truncate on database B (truncate A, discard all older rows).
Second proposal:
- Database A gets truncated at time X
- We populate the delta table with every primary key in the table before truncation (assuming we can get at it)
- That's it! Bucardo does its normal thing as if we just deleted a whole bunch of rows on A, and in theory deletes them from B as well.
Comments on this strategy welcome!
Update: Clarified initdb cluster vs. database per comment #1 below, and added new truncation handling scheme for multi-master replication per comment #2.
In Interchange, You Might Need to [try] [goto]. What's the [catch]?
Interchange provides tags that allow error trapping and handling within ITL--[try] and [catch]--that can be thought of as analogous to perl's eval {} followed by if ($@) {}. However, as I discovered the hard way, the analogy is not perfect.
I set up a block of ITL within [try] that had two major actions, with the 2nd depending on the success of the first. In particular, these two actions were a credit card authorization, followed by a capture of that auth as long as (a) the authorization succeeded, and (b) the merchant's internal rules for analyzing order content compared to AVS results "passed". (b) was necessary as a fraud-protection measure, tightening up the impact of AVS results based on the historic tendency of certain products to be targeted by crooks. In the event that the auth succeeded, but the tests from (b) failed, it is very important that the capture never be attempted because, to the gateway, the auth is entirely valid and the catpure attempt would succeed.
The code that assesses whether AVS passes is done in its own [calc]. From within the code, if the assessment does not pass, the code issues a die(), which in fact does trigger [try] to log the error that becomes accessible in [catch] via the $ERROR$ token, and thus does trigger [catch] to execute its body contents. In that way, the [try] did trap the error, and the error was handled in [catch], but of course that's not the end of the story or this post wouldn't exist.
After the code had been in production for some time, David Christensen brought to my attention that he noticed in development a test order attempt, where the order attempt failed, but both the auth and the capture succeeded. I was highly dubious of this claim and went over in great detail just what he had done. We narrowed down the condition that produced the problem to (b) above: a successful auth, but abort the order attempt anyway because a high-value product in the cart was coupled with a questionable AVS. When I went to the logs, I could see the result spelled out, but the result made no sense to my understanding:
### starting credit card processing ### Real-time full auth succeeded. ID=*** 0 Real-time full capture succeeded. ID=*** Error detected for order xxx in the credit card charge.
The 0 indicated the [calc] had failed (died), yet the capture later in the [try] was still executing. The only conclusion was that, unlike eval {}, when [try] trapped an error, it just kept right on processing the continuing ITL. [try] always went forward and processed all its ITL to the end, and whatever happened to be the last die() called within that batch of ITL would be the thing that [catch] caught and displayed.
To resolve the problem, I introduced [goto] into the block, which stops the instance of interpolate_html() running at the point of encounter and returns. Continuing to use the die() call to populate the error code from [try], immediately after the [calc] test block I called [goto] conditionally on whether the [calc] block, in fact, died. The [goto] call then terminated the instance of interpolate_html() that [try] had invoked on its body, which had the effect of stopping ITL execution at the point of the die().
This approach to emulating eval {}/if ($@) {} has the significant flaw of developers needing to know ahead of time exactly where in the [try] block such failures are expected. If such is unknowable, it leaves developers in the unenviable position of having to follow each tag call with a conditional [goto] that has to know when the previous tag "failed" (i.e., triggered a die() somewhere).
Test::Database Postgres support
At our recent company meeting, we organized a 'hackathon' at which the company was split into small groups to work on specific projects. My group was Postgres-focused and we chose to add Postgres support to the new Perl module Test::Database.
This turned out to be a decent sized task for the few hours we had to accomplish it. The team consisted of myself (Greg Sabino Mullane), Mark Johnson, Selena Deckelmann, and Josh Tolley. While I undertook the task of downloading the latest version and putting it into a local git repository, others were assigned to get an overview of how it worked, examine the API, and start writing some unit tests.
In a nutshell, the Test::Database module allows an easy interface to creating and destroying test databases. This can be a non-trivial task on some systems, so putting it all into a module make sense (as well as the benefits of preventing everyone from reinventing this particular wheel). Once we had a basic understanding of how it worked, we were off.
While all of our tasks overlapped to some degree, we managed to get the job done without too much trouble, and in a fairly efficient manner. We made a new file for Postgres, added in all the required API methods, wrote tests for each one, and documented everything as we went along. The basic method to create a test database is to use the initdb program to create a new Postgres cluster, then modify the cluster to use a local Unix socket in the newly created directory (this side-stepping completely the problem of using an already occupied port). Then we can start up the new cluster via the pg_ctl command, and create a new database.
At the end of the day, we had a working module that passed all of its tests. We combined our git patches into a single one mailed it to the author of the module, so hopefully you'll soon see a new version of Test::Database with Postgres support!
Perl 5 now on Git
It's awesome to see that the Perl 5 source code repository has been migrated from Perforce to Git, and is now active at http://perl5.git.perl.org/. Congratulations to all those who worked hard to migrate the entire version control history, all the way back to the beginning with Perl 1.0!
Skimming through the history turns up some fun things:
- The last Perforce commit appears to have been on 16 December 2008.
- Perl 5 is still under very active development! (It seems a lot of people are missing this simple fact, so I don't feel bad stating it.)
- Perl 5.8.0 was released on 18 July 2002, and 5.6.0 on 23 March 2000. Those both seem so recent ...
- Perl 5.000 was released on 17 October 1994.
- Perl 4.0.00 was released 21 March 1991, and the last Perl 4 release, 4.0.36, was released on 4 February 1993. For having an active lifespan of only 4 or so years till Perl 5 became popular, Perl 4 code sure kicked around on servers a lot longer than that.
- Perl 1.0 was announced by Larry Wall on 18 December 1987. He called Perl a "replacement" for awk and sed. That first release included 49 regression tests.
- Some of the patches are from people whose contact information is long gone, rendered in Git commits as e.g. Dan Faigin, Doug Landauer <unknown@longtimeago>.
- The modern Internet hadn't yet completely taken over, as evidenced by email addresses such as isis!aburt and arnold@emoryu2.arpa.
- The first Larry Wall entry with email address larry@wall.org was 28 June 1988, though he continued to use his jpl.nasa.gov after that sometimes too.
- There are some weird things in the commit notices. For example, it's hard to believe the snippet of Perl code in the following change notice wasn't somehow mangled in the conversion process:
commit d23b30860e3e4c1bd7e12ed5a35d1b90e7fa214c
Author: Larry Wall <lwall@scalpel.netlabs.com>
Date: Wed Jan 11 11:01:09 1995 -0800
duplicate DESTROY
In order to fix the duplicate DESTROY bug, I need to remove [the
modified] lines from sv_setsv.
Basically, copying an object shouldn't produce another object without an
explicit blessing. I'm not sure if this will break anything. If Ilya
and anyone else so inclined would apply this patch and see if it breaks
anything related to overloading (or anything else object-oriented), I'd
be much obliged.
By the way, here's a test script for the duplicate DESTROY. You'll note
that it prints DESTROYED twice, once for , and once for . I don't
think an object should be considered an object unless viewed through
a reference. When accessed directly it should behave as a builtin type.
#!./perl
= new main;
= '';
sub new {
my ;
local /tmp/ssh-vaEzm16429/agent.16429 = bless $a;
local = ; # Bogusly makes an object.
/tmp/ssh-vaEzm16429/agent.16429;
}
sub DESTROY {
print "DESTROYED\n";
}
Larry
sv.c | 4 ----
1 files changed, 0 insertions(+), 4 deletions(-)
Yes, it really is that weird. Check it out for yourself.
The Easy Git summary information from eg info has some interesting trivia:
Total commits: 36647 Number of contributors: 926 Number of files: 4439 Number of directories: 657 Biggest file size, in bytes: 4176496 (Changes5.8) Commits: 31178
And there's a nice new POD document instructing how work with the Perl repository using Git: perlrepository.
In other news, maintenance release Perl 5.8.9 is out, expected to be the last 5.8.x release. The change log shows most bundled modules have been updated.
Finally, use Perl also notes that Booking.com is donating $50,000 to further Perl development, specifically Perl 5.10 development and maintenance. They're also hosting the new Git master repository. Thanks!
Creating a PL/Perl RPM linked against a custom Perl build
My and everyone else's Planet PostgreSQL blogs have been down for some time now. I recently needed to refer to a post I made on March 7, 2007, showing how to build a PL/Perl RPM linked against a custom Perl build. Of course it wasn't there! The archive.org Wayback Machine came to my rescue.
A few things have changed since that time, so I've reworked it here, updated for local Perl 5.10.0 built into RPMs:
We sometimes have to install a custom Perl build without thread support, and to have some specific newer and/or older versions of CPAN modules, and we don't want to affect the standard distribution Perl that lives in /usr/bin/perl and /usr/lib/perl5. We use standard PGDG RPMs to install PostgreSQL. We also use PL/Perl, and want PL/Perl to link against our custom Perl build in /usr/local/bin and /usr/local/lib/perl5.
It's easy to achieve this with a small patch to the source RPM spec file:
--- postgresql-8.3.spec 2008-10-31 17:34:34.000000000 +0000
+++ postgresql-8.3.custom.spec 2008-11-30 02:10:09.000000000 +0000
@@ -315,6 +315,7 @@
CFLAGS=`echo $CFLAGS|xargs -n 1|grep -v ffast-math|xargs -n 100`
export LIBNAME=%{_lib}
+export PATH=/usr/local/bin:$PATH
%configure --disable-rpath \
%if %beta
--enable-debug \
@@ -322,6 +323,7 @@
%endif
%if %plperl
--with-perl \
+ --with-libraries=/usr/local/lib64/perl5/5.10.0/x86_64-linux/CORE/libperl.so \
%endif
%if %plpython
--with-python \
Since we build RPMs of our local Perl, we want this PL/Perl RPM to depend on them, so we make this additional patch before building:
--- postgresql-8.3.spec 2008-10-31 17:34:34.000000000 +0000
+++ postgresql-8.3.custom.spec 2008-11-30 02:10:09.000000000 +0000
@@ -100,7 +100,7 @@
Patch6: postgresql-perl-rpath.patch
Patch8: postgresql-prefer-ncurses.patch
-Buildrequires: perl glibc-devel bison flex
+Buildrequires: local-perl = 4:5.10.0, glibc-devel, bison, flex
Requires: /sbin/ldconfig initscripts
%if %plpython
@@ -227,7 +227,7 @@
%package plperl
Summary: The Perl procedural language for PostgreSQL
Group: Applications/Databases
-Requires: postgresql-server = %{version}-%{release}
+Requires: postgresql-server = %{version}-%{release}, local-perl = 4:5.10.0
%ifarch ppc ppc64
BuildRequires: perl-devel
%endif
After applying the patch(es) (adjusted for your own custom Perl build, of course), rebuild the RPM, and install the postgresql-plperl (8.2 or newer) or postgresql-pl (8.1 and earlier) RPM. With a "service postgresql restart", you're ready to go.
For pre-built PostgreSQL 8.3 RPMs that link against unthreaded local Perl 5.10.0, for Red Hat Enterprise Linux 5 x86_64, see the packages.endpoint.com Yum repositories.
PostgreSQL Conference West 2008 Report
I attended the PostgreSQL Conference West and had a great time again this year. My photos of the event are up here:
http://db.endpoint.com/pg-conf-08
In addition, I shot some footage of the event in an attempt to highlight the benefits of the conference, Postgres itself, and the community strengths. I'm looking for a talented Editor willing to donate time; if none volunteer then I'll probably do it in January. My guess is that there will be several web sites willing to host it for free when it's done.
The Code Sprint was really interesting. Selena Deckelmann gave everyone a lot of ideas to get the most out of the time available for hacking code. At regular intervals, each team shared the progress they made and recieved candy as a reward. It was neat to see other people hacking on and committing changes to the Postgres source tree in meatspace.
Bruce Momjian's Postgres training covered a wide gamut of information about Postgres. He polled everyone in the room for their particular needs, which varied from administration to performance, then tailored the training to cover information relating to those needs in particular detail. Those who attended reported that they learned a great deal of new information from the training. From here, a lot of folks went out to continue interacting with Postgres people, but I headed for home.
Windowing Functions were covered by David Fetter in a talk that addressed ways to make OLAP easier with new features coming to Postgres 8.4. Functionality that used to be slow and difficult in client-side applications can be handled easily right in the database. I made a note to check this out when 8.4 hits the streets.
Jesse Young spoke about using Linux-HA + DRBD to build high availability Postgres clusters. It is working very well for him in over 30 different server installations; he proved this by taking down a production server in the middle of the presentation and demonstrating the rapid transition to the failover server. Just set-it-and-forget-it. I was able to weigh the advantages and disadvantages compared to other clustering options such as shared disk (e.g. GFS) and Postgres-specific replication options (Slony, Postgres Replicator, Bucardo, etc.).
In his talk, PostgreSQL Optimizer Exposed, Tom Raney delved into a variety of interesting topics. He described the general workings of the optimizer, then showed a variety of interesting plans that are evaluated for the example query, how each plan was measured for cost, and why the cost varied. He uncovered several interesting facts, such as demonstrating that the Materialization step (pushing sorts to disk that are too large for memory) doesn't increase the cost associated with that plan. Tom Lane explained that this would rarely, if ever, affect real world results, but that is the kind of information made obvious in the Visual Planner, but hidden by textual EXPLAIN ANALYZE. Tom Raney also demonstrated the three-fold difference (in one case) between the cost of the clustered index and the rest. Optimizing query performance is one of my favorite pastimes, so I enjoyed this talk a lot.
I learned a bit about what was going in Postgres community organizations during Joshua Drake's talk, "PostgreSQL - The happening since EAST". The PostgreSQL.US and other organizations are doing a lot to increase awareness of Postgres among education, government, business, and other developers. The point was made that we should do as much as we can to reach out to widely prevalent PHP applications and web hosting providers.
Common Table Expressions (CTE) were given a good explanation by David Fetter in his talk about doing Trees and More in SQL. Having worked on Nested Set and Adjacency List models, I was very interested in this new feature coming to 8.4. Starting with a simple recursive example, David built on it slide-by-slide until he had built and executed a brute force solution to a Traveling Salesman Problem (for a small number of cities in Italy) using only plain SQL. I'm excited to try this out and measure the performance.
Mark Wong & Gabrielle Roth presented the results of testing that they completed. Selena also covered that information in her post about Testing filesystems and RAID. After that we talked Perl on the way to the Portland Paramount for the party.
On Sunday, I sat in on "Developing a PL (Procedural Language) for PostgreSQL", by Joshua Tolley, as he carefully explained the parts and steps involved. LOLCODE humor peppered the useful information on the related Postgres libraries, Bison usage, and pitfalls.
I was glad to see Catalyst boosted in Matt Trout's presentation. He very quickly covered the design and advantages of Catalyst, DBIx::Class, and Postgres as they related to the implementation of a high profile and complex web site. It was very informative to see the Class structure for the View model, which gave me several ideas to take use for my own development. He demonstrated how a complex 56-way join was coded in very brief and comprehensible perl code relying on the underlying modules to provide the underlying support. The explain tree is so large that it couldn't fit on the screen even in microscopic font, and even with very large data sets, the Postgres optimizer found a way to return the results in one tenth of a second. Matt also demonstrated several flaws in his design, such as how his use of rules to implement updatable views caused multi-row updates to be slower than the equivalent trigger-based system. I use Catalyst for several projects, but I think Interchange still has more advantages. I'm definitely going to take another look at DBIx::Class.
Before lunch, I asked if I could shoot a group photo, so we went to the park. Several people were not in attendance, and I didn't want to take more than a minute or two, so the shots are not as good as I would have liked. Next time I'll ask if we can plan some time for arranging the group. At lunch I had a great time talking to fellow Postgres developers and learning more about their work.
Lightning Talks followed lunch and included a variety of interesting topics. One of my favorites was "I can't do that" by Matt Trout. He explained how wrong it is to believe you can't contribute something to Postgres or any other open source project. If you think your code will be incomplete or buggy, do it anyway, because it may prompt someone else to work on it, or scrap yours and do it right. Don't think you can't contribute to documentation because of your infamiliarity with the system, because that's exactly the advantage you have for documentation contributions: those who need the docs are in exactly your shoes.
Matt also gave the closing talk, "Perl 5 is Alive!", which was a concise, water-tight presentation of Perl 5's superiority over other development environments, including CPAN and job statistics that demonstrate its growing popularity.
Some attendees went out afterwards to finish the conference over a drink. I slept about 11 hours straight to recover from the whirlwind of weekend activity. Overall I'm grateful for the opportunity to interact with the community again and I'm excited for what the future has in store for Postgres.
Moose roles
Perl programmers,
Moose roles give a really nice way of maximizing code reuse within an object system, while favoring composition over inheritance. This makes for shallower inheritance trees, reduced method dispatch spaghettification, and a more comprehensible, maintainable, extensible codebase overall. Revel in the glory.
That is all.
