Our Blog

Postgres Open: One week to go!

By Josh Williams
September 7, 2011

Wow, time flies, Postgres Open is almost upon us!

I’ll be there giving a talk Thursday morning on monitoring tools and techniques, and possibly helping with the Bucardo 5 replication session Friday afternoon. Sadly I’ll need need to catch a flight shortly after that, so there won’t be much time to explore Chicago around everything going on. But at least it’ll be nice to get out to a conference again!

SQL errors in Interchange

By Jeff Boes
September 7, 2011

Interchange has a little feature whereby errors in a [query] tag are reported back to the session just like form validation errors. That is, given the intentional syntax error here:

[query ... sql="select 1 from foo where 1="]

Interchange will paste the error from your database in

  $Session->{errors}{'table foo'}

That’s great, but it comes with a price: now you have a potential for a page with SQL in it, which site security services like McAfee will flag as “SQL injection failures”. Sometimes you just don’t want your SQL failures plastered all over for the world to see.

Simple solution:

  DatabaseDefault LOG_SESSION_ERROR 0

in your Interchange configuration file, possibly constrained so it only affects production (because you’d love to see your SQL errors when you are testing, right?).

Ruby on Rails Performance Overview

By Steph Skardal
September 6, 2011

Over the last few months, I’ve been involved in a Ruby on Rails (version 2.3) project that had a strong need to implement performance improvements using various methods. Here, I’ll summarize some the methods and tools used for performance optimization on this application.

Fragment Caching

Before I started on the project, there was already a significant amount of fragment caching in use throughout the site. In it’s most basic form, fragment caching wraps a cache method around existing view code:

<%= cache "product-meta-#{product.id}" %>
#insert view code
<% end %>

And Rails Sweepers are used to clear the cached fragments, which looks something like the code shown below. In our application, the Sweeper attaches cache clearing methods to object callbacks, such as after_save, after_create, before_update.

class ProductSweeper < ActionController::Caching::Sweeper
  observe Product

  def after_save(record)
    expire_fragment "product-meta-#{product.id}"
  end
end

Fragment caching is a good way to reuse small modular view components throughout the site. In this application, fragment caches tended to contain object meta data that was shown on various index …

Bucardo PostgreSQL replication to other tables with customname

By Greg Sabino Mullane
September 5, 2011

Image by Flickr user Soggydan

(Don’t miss the Bucardo5 talk at Postgres Open in Chicago)

Work on the next major version of Bucardo is wrapping up (version 5 is now in beta), and two new features have been added to this major version. The first, called customname, allows you to replicate to a table with a different name. This has been a feature people have been asking for a long time, and even allows you to replicate between differently named Postgres schemas. The second option, called customcols, allows you replicate to different columns on the target: not only a subset, but different column names (and types), as well as other neat tricks.

The “customname” options allows changing of the table name for one or more targets. Bucardo replicates tables from the source databases to the target databases, and all tables must have the same name and schema everywhere. With the customname feature, you can change the target table names, either globally, per database, or per sync.

We’ll go through a full example here, using a stock 64-bit RedHat 6.1 EC2 box (ami-5e837b37). I find EC2 a great testing platform—​not only can you try different operating systems and architectures, but …

Building Xpdf on Ubuntu

By Jon Jensen
August 31, 2011

It may happen that you need to use Xpdf, even though it no longer ships with Ubuntu and is considered … outdated? buggy? insecure? In any case, it still renders some PDFs that Poppler-based viewers such as Evince don’t, or allows some troublesome PDFs to print as fonts and line art instead of a rasterized mess.

Here’s how I built and installed xpdf 3.02 on Ubuntu 11.04 (Natty Narwhal) x86_64:

sudo apt-get install libfreetype6-dev libmotif-dev
wget ftp://ftp.foolabs.com/pub/xpdf/xpdf-3.02.tar.gz  # now 3.03 is current
tar xzpf xpdf-3.02.tar.gz
cd xpdf-3.02
./configure --with-freetype2-library=/usr/lib/x86_64-linux-gnu \
    --with-freetype2-includes=/usr/include/freetype2 \
    --with-Xm-library=/usr/lib \
    --with-Xm-includes=/usr/include/Xm
make
# see lots of warnings!
sudo make install

That’s it. Not as nice as the old native Debian/Ubuntu packages, but gets the job done.

Giraffes and Liquid Galaxy

By Josh Tolley
August 25, 2011

We build lots and lots of Google Earth tours, typically for use with a Liquid Galaxy. These tours tell Google Earth to start at one point and fly to another, displaying various images along the way. They’re written in an XML-based language called Keyhole Markup Language, or KML. Various KML elements describe points on the globe, 3D objects, tracks through space, or other items, and a series of Google-specific KML extensions can define an automated tour in which Google Earth flies through these objects.

Now for a bit of dogma: writing large volumes of XML by hand is a drag. Writing KML is no exception. So when I started building KML tours, I was keen to build or find tools to make it easier. For my first KML project, making a tour through each End Point employee’s home city, I built a Perl script to convert a file of employee data to KML. This worked well enough, but I soon learned typing out all the KML was just half the pain. The other half was adjusting camera paths, camera angles, timing, and all sorts of other little things. Unfortunately all these adjustments had to be done by trial and error, and all by hand. Getting Google Earth’s camera to circle a particular point, for …

PostgreSQL log analysis / PGSI

By Greg Sabino Mullane
August 19, 2011

Image by “exfordy” on Flickr

End Point recently started working with a new client (a startup in stealth mode, cannot name names, etc.) who is using PostgreSQL because of the great success some of the people starting the company have had with Postgres in previous companies. One of the things we recommend to our clients is a regular look at the database to see where the bottlenecks are. A good way to do this is by analyzing the logs. The two main tools for doing so are PGSI (Postgres System Impact) and pgfouine. We prefer PGSI for a few reasons: the output is better, it considers more factors, and it does not require you to munge your log_line_prefix setting quite as badly.

Both programs work basically the same: given a large number of log lines from Postgres, normalize the queries, see how long they took, and produce some pretty output.If you only want to look at the longest queries, it’s usually enough to set your log_min_duration_statement to something sane (such as 200), and then run a daily tail_n_mail job against it. This is what we are doing with this client, and it sends a daily report that looks like this:

Date: Mon Aug 29 11:22:33 2011 UTC
Host: acme-postgres-1
Minimum …

jQuery and hidden elements

By Jeff Boes
August 16, 2011

Out of sight, not out of mind.

While extending some jQuery functionality on a page, I noted that a form element’s “change” handler wasn’t being invoked, which meant some of the page initialization code would be left out. So I helpfully added it:

$('input[name=foobar]').change(...).change();

What I failed to contemplate was how this would impact another page, which just happened to have * some * (but not all) of the same form elements, and referenced the same JS code at page load. Specifically, my page’s sibling had:

<input name="foobar" type="hidden">

And of course this broke in production.

Well, that’s interesting. A change handler on a hidden form input field isn’t usually all that useful, so I figured out I really needed:

$('input[name=foobar]').filter(':visible').change(...).change();

It happens that the “.filter()” step is actually more efficient than doing it all in one selector (“input[name=foobar]:visible”), because of some obscurities within jQuery. That little discovery was of value to an End Point co-worker, who realized she could shave a little time off a page load elsewhere, so my minor page malfunction will redeem …