Our Blog

Interchange news

By Jon Jensen
August 24, 2009

Tomorrow we’ll be having an Interchange community meeting on IRC. All Interchange users and any other interested parties are invited to participate.

Also, just recently, End Point’s own David Christensen joined the Interchange Development Group and became a core committer. Congratulations, David, and keep up the good work!

Perl’s Scalar::Util::dualvar

By Jon Jensen
August 24, 2009

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:

dualvar NUM, STRING

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!

Defining variables for rpmbuild

By Jon Jensen
August 20, 2009

RPM spec files offer a way to define and test build variables with a directive like this:

%define <variable> <value>

Sometimes it’s useful to override such variables temporarily for a single build, without modifying the spec file, which would make the changed variable appear in the output source RPM. For some reason, how to do this has been hard for me to find in the docs and hard for me to remember, despite its simplicity.

Here’s how. For example, to override the standard _prefix variable with value /usr/local:

rpmbuild -ba SPECS/$package.spec --define '_prefix /usr/local'

Text sequences

By Greg Sabino Mullane
August 20, 2009

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'); …

Two quick tips: egrep & SQL dumps, Vim and deleting things that don’t match

By Selena Deckelmann
August 20, 2009

Sometimes, I just don’t want to restore a full SQL dump. The restore might take too long, and maybe I just want a small subset of the records anyway.

I was in exactly this situation the other day—​faced with a 10+ hour restore process, it was way faster to grep out the records and then push them into the production databases, than to restore five different versions.

So! egrep and vim to the rescue!

In my case, the SQL dump was full of COPY commands, and I had a username that was used as a partial-key on all the tables I was interested in. So:

egrep “((^COPY)|username)” PostgresDump.sql > username.out

I get a pretty nice result from this. But, there are some records I’m not so interested in that got mixed in, so I opened the output file in vim and turned line numbers on (:set numbers).

The first thing that I do is insert the ‘.’ needed to tell Postgres that we’re at the end of a COPY statement.

:2,$s/^COPY/\.^V^MCOPY/

The ‘^V^M’ is a control sequence that results in a ‘^M’ (a newline character, essentially). And the ‘2’ starts the substitution command on the second line rather than the first COPY statement (which, in my case, was on the first line).

Next, I want to …

lessons = colors.find_all {|c| c.kind_of? Blue}

By Ethan Rowe
August 19, 2009

As noted in this nice article at Slate.com, the much-loved “Kind of Blue” celebrated its 50th anniversary recently. In the article, Fred Kaplan asks, and attempts to answer, “what makes this album so great?”

As somebody who has made a point of introducing his daughter (at infancy) to Miles Davis, who succumbed to the allure of jazz thanks in no small part to this very album, I would be remiss in my duties as a Caring Human not to blog about it. And yet this is a corporate blog for a consulting company that does lots of stuff with free software (as demonstrated so effectively here). What to do?

Fortunately, there’s something we in the software world can learn from this album and the lessons Mr. Kaplan derives from it. Let’s look at one key paragraph:

So Kind of Blue sounded different from the jazz that came before it. But what made it so great? The answer here is simple: the musicians. Throughout his career, certainly through the 1950s and ’60s, Miles Davis was an instinctively brilliant recruiter; a large percentage of his sidemen went on to be great leaders, and these sidemen—especially Evans, Coltrane, and Adderley—were among his greatest. They came to the date, were handed …

Debugging prepared statements

By Greg Sabino Mullane
August 17, 2009

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 = …

Site Search on Rails

By Steph Skardal
August 14, 2009

I was recently tasked with implementing site search using a commercially available site search application for one of our clients (Gear.com). The basic implementation requires that a SOAP request be made and the XML data returned be parsed for display. The SOAP request contains basic search information, and additional information such as product pagination and sort by parameters. During the implementation in a Rails application, I applied a few unique solutions worthy of a blog article. :)

The first requirement I tackled was to design the web application in a way that produced search engine friendly canonical URLs. I used Rails routing to implement a basic search:

map.connect ':id', :controller => 'basic', :action => 'search'

Any simple search path would be sent to the basic search query that performed the SOAP request followed by XML data parsing. For example, https://www.gear.com/s/climb is a search for “climb” and https://www.gear.com/s/bike for “bike”.

After the initial search, a user can refine the search by brand, merchant, category or price, or choose to sort the items, select a different page, or modify the number of items per page. I chose to …