Our Blog

NOTIFY vs Prepared Transactions in Postgres (the Bucardo solution)

By Greg Sabino Mullane
May 3, 2011

We recently had a client use Bucardo to migrate their app from Postgres 8.2 to Postgres 9.0 with no downtime (which went great). They also were using Bucardo to replicate from the new 9.0 mater to a bunch of 9.0 slaves. This ran into problems the moment the application started, as we started seeing these messages in the logs:

ERROR:  cannot PREPARE a transaction that has 
executed LISTEN, UNLISTEN or NOTIFY

The problem is that the Postgres LISTEN/NOTIFY system cannot be used with prepared transactions. Bucardo uses a trigger on the source tables that issues a NOTIFY to let the main Bucardo daemon know that something has changed and needs to be replicated. However, their application was issuing a PREPARE TRANSACTION as an occasional part of its work. Thus, they would update the table, which would fire the trigger, which would send the NOTIFY. Then the application would issue the PREPARE TRANSACTION which produced the error given above. Bucardo is setup to deal with this situation; rather than using notify triggers, the Bucardo daemon can be set to look for any changes at a set interval. The steps to change Bucardo’s behavior for a given sync is simply:

$ bucardo_ctl update sync …

MySQL Integer Size Attributes

By Mark Johnson
April 28, 2011

MySQL has those curious size attributes you can apply to integer data types. For example, when creating a table, you might see:

mysql> CREATE TABLE foo (
    -> field_ti tinyint(1),
    -> field_si smallint(2),
    -> field_int int(4),
    -> field_bi bigint(5)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> desc foo;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| field_ti  | tinyint(1)  | YES  |     | NULL    |       |
| field_si  | smallint(2) | YES  |     | NULL    |       |
| field_int | int(4)      | YES  |     | NULL    |       |
| field_bi  | bigint(5)   | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.03 sec)

mysql>

I had always assumed those size attributes were limiters, MySQL’s way of providing some sort of constraint on the integers allowed in the field. While doing some recent work for a MySQL client, I attempted to enforce the range of a tinyint according to that assumption. In reality, I only wanted a sign field, and would have liked to have …

A Product Variant Code Challenge

By Steph Skardal
April 27, 2011

A while ago, I came across a cute little Ruby challenge that looked interesting:

Given an array of arrays of possible values, enumerate all combinations that can occur, preserving order. For instance:

Given: [[1,2,3], [4,5,6], [7,8,9]], calculate the same result as the code below, but do so with an arbitrary size array:

combos = []
[1,2,3].each do |v1|
  [4,5,6].each do |v2|
    [7,8,9].each do |v3|
      combos << [v1, v2, v3]
    end
  end
end
combos

Entries can be written using one or more functions, and may optionally be written as a class extension (i.e. Array)….

And now some context to why I thought this was applicable to ecommerce: Let’s imagine you have a product. Then, let’s imagine that the product has variations, or variants. We’ll say we have an arbitrary size array of “option types”, each with an arbitrary number of items in the array. Here, we have option types of size, color, and printed logo, which yields multiple variations, variants or combinations of a single product:

Size	Color	Logo
Large	Red
Medium	Blue
Small

And let’s give a real-life example data model, this one from a previous article on Spree’s product …

Three Liquid Galaxy Projects Accepted for Google Summer of Code 2011

By Adam Vollrath
April 26, 2011

Yesterday the student proposals accepted for the Google Summer of Code program were announced. The Liquid Galaxy Project, participating in the program for the first time, accepted three proposals. These projects will dramatically extend the functionality of the Liquid Galaxy system:

New Control Input Devices and Distributed GL Rendering — Paul Hunkin, mentored by Andrew Leahy

Paul is a PhD student and has been running Liquid Galaxy on his university display wall for some time now. This project will initially target Microsoft’s Kinect as an input device for Liquid Galaxy, and may also leverage ClusterGL for distributed GL Rendering of Google Earth and other OpenGL applications.

Network Sync in Irrlicht — Ben Wright, mentored by Ben Goldstein

Google Earth is certainly a “killer app” for the Liquid Galaxy platform, but there are many other applications that could be easily enhanced, coordinating multiple instances rendering portions of a panoramic view. This project will modify the 3D Open-Source graphics engine Irrlicht, enabling many 3D applications using this platform.

Android Phone Accelerometer as Liquid Galaxy Input Device — Reese Butler, mentored by Adam Vollrath

Currently, the …

Postgres query caching with DBIx::Cache

By Greg Sabino Mullane
April 25, 2011

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 …

RHEL 5 SELinux initscripts problem

By Jon Jensen
April 22, 2011

I ran into a strange problem updating Red Hat Enterprise Linux 5 a few months ago, and just ran into it again and this time better understood what went wrong.

The problem was serious: After a yum upgrade of a RHEL 5 x86_64 server with SELinux enforcing, it never came back after a reboot. Logging into the console I could see that it was stuck in single user mode because there were no init scripts! Investigation showed that indeed the initscripts package was completely missing.

I searched on bugzilla.redhat.com looking for any reported problems and didn’t find any. I reinstalled initscripts, rebooted, and the server was fine, but it was not happytimes to have that unexpected downtime.

Tonight I ran into the problem again, this time on a build server where downtime didn’t matter so I could investigate more leisurely.

The error from yum looked like this (the same problem affected the screen package as affected initscripts):

Downloading Packages:
screen-4.0.3-4.el5.i386.rpm          | 559 kB      00:00
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
groupadd: unable to open group file
error: …

Determining dominant image color

By Jeff Boes
April 21, 2011

This grew out of a misunderstanding of a client’s request, so it never saw the light of day, but I thought it was an interesting problem.

The request was to provide a “color search” of products, i.e., “show me all the orange products”. Finding the specific products was not a challenge since it was just a database query. Instead I was interested in how to choose a “representative image” from among the available images for that product. (And as it turns out, the image filename gave me that information, but let’s assume you don’t have that luxury: how do you tell, from a group of images, which one is “more orange” than the others?)

Of course, this depends on the composition of the image. In this case, I knew that the majority were of solid-color (or two- or three-color at most) products on a white background. The approach that was settled on was to severely pixellate the image into something like 20x20 (arbitrary; this could be very dependent on the images under study, or the graphics library in use). If you also supply a color palette restricted to the colors you are interested in matching (e.g., primary, and secondary colors, plus perhaps black, white, and gray), you would have a …

Use ZIP+4, except when you shouldn’t

By Jeff Boes
April 12, 2011

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.