Our Blog

Speeding Up Some FDW Queries

By Szymon Lipiński
August 8, 2013

There was a very interesting question about PostgreSQL optimization. It was about speeding up a query on foreign tables.

Foreign Data Wrappers

FDW is quite a nice idea, it allows to use different sources of data and access them like a normal database table.

You can find some more information about writing custom FDW handlers or use some already created. This way you can connect to another database, or even use CSV files as Postgres tables without loading them into database.

Introduction

Let’s take a couple of tables and a view created on the top of them:

CREATE TABLE t_10_20(i INTEGER);
CREATE TABLE t_15_20(i INTEGER);
CREATE TABLE t_10_16(i INTEGER);

CREATE VIEW all_tables AS
  SELECT i FROM t_10_20
  UNION ALL
  SELECT i FROM t_15_20
  UNION ALL
  SELECT i FROM t_10_16;

I assume, as it was in the original question, that there are some strict ranges of data in the tables. For the example I encoded the ranges in table names, so table t_10_20 contains values from the range [10,20] and table t_10_16 has values from [10,16];

The above view will be used for getting all data.

For filling them up, I used a function which I wrote long time ago, it returns a random number with uniform …

Buy One Get One Promotion with Spree

By Brian Buchalter
August 7, 2013

Implementing a “Buy One, Get One Free” promotion in Spree requires implementation of a custom promotion action and appropriate use of existing promotion rules. This article implements the promotion by automatically adding and removing immutable “get one” line items whose price is zero and whose quantity always mirrors its paid “buy one” counterpart. Although written and tested with Spree’s 1-3-stable branch, the core logic of this tutorial will work with any version of Spree.

Promotion Eligibility

Begin by creating a new promotion using a meaningful name. Set the “event name” field to be “Order contents changed” so the promotion’s actions are updated as the order is updated. Save this new promotion, so we can then configure Rules and Actions. In the Rules section, select the “Product(s)” rule and click Add. Now choose the products you’d like to be eligible for your promotion. If you’d like to include broader sets such as entire taxonomies (and have implemented the custom promotion rules to do so), feel free to use them. When we implement the promotion action, you’ll be able to make things work.

You should now have a product rule that selects some subset of products eligible for …

Little Spree Big Performance Problems

By Marina Lohova
August 5, 2013

Recently I worked on an online food store serving an area with very little infrastructure. As a result, the orders tended to be really big with lots of products.

The website worked in the following environment:

• Ruby 1.9.2
• Spree 0.60
• Heroku, Bamboo stack
• PostgreSQL 9.2.4

H12 timeout errors

The performance problems started when we migrated Bamboo to Cedar on Heroku and replaced Thin webserver with Unicorn. We started getting a lot of Heroku Request timeout errors - H12:

The problems happened mostly when logging in to admin dashboard or during the checkout for the certain orders. H12 errors occur when a HTTP request takes longer than 30 seconds to complete. For example, if a Rails app takes 35 seconds to render the page, the HTTP router returns a 503 after 30 seconds and abandons the incomplete Rails request for good. The Rails request will keep working and logging the normal errorless execution. After completion, the request will indefinitely hang in the application dyno.

We started debugging H12: we set Unicorn timeout to 20 seconds to prevent the runaway requests and installed the rack-timeout gem with the timeout of 10 seconds to raise an error on a slow request. It all came …

Disney Liquid Galaxy in Sao Paolo

By Dave Jenkins
August 1, 2013

End Point recently had the pleasure to work with Disney and Google to bring the Liquid Galaxy to Disney Expo in Sao Paolo, Brazil.

Disney saw the Liquid Galaxy at the Google office in Sao Paulo and recognized the “WOW!” factor that the display platform can provide, and the Disney Expo organizers saw a great fit for promoting the release of the upcoming animated film PLANES. Disney engaged End Point to develop a custom menu of “fly to” locations featured in the movie.

Attendees at the Expo experienced those locations in immersive high definition across 7 screens surrounding the viewer. End Point created a custom menu for the touch screen with one-touch buttons that “flew” the users to locations featured in PLANES:

• USA — Statue of Liberty
• ICELAND — Reykjavik Botanical Garden
• GERMANY — Deutsches Museum
• INDIA — Taj Mahal
• NEPAL — Himalayas
• CHINA — Great Wall
• MEXICO — Pyramids of Yucatan

The experience for the attendees at the expo was such that they could virtually fly to these locations just like the characters in the movie. Other options on the touch screen menu featured the Disney resort properties:

• USA — Orlando — Walt Disney World (Magic Kingdom)
• USA — Anaheim / California — …

Merging JSONs in PostgreSQL

By Szymon Lipiński
July 29, 2013

PostgreSQL’s JSON support is great, however there is one thing missing, and it will be missing in the next PostgreSQL release. It is not too easy to manipulate the values stored in such a JSON field. Fortunately there is an easy way to do anything you want, you can use some external programming language.

Merging JSONs

Sometimes you need to update one JSON with values from another. Or you just need to change one field in a JSON value. There is no easy way to do it in PostgreSQL, but with the help of external language, it seems trivial.

Let’s use simple JSONs:

WITH j AS (
  SELECT
    '{"a":42, "b":"test"}'::JSON a,
    '{"a":1764, "x":"test x"}'::JSON b
)
SELECT a, b
FROM j;

a           |            b
-------------------------------------------------------
 {"a":42, “b”:"test"} | {"a":1764, “x”:“test x”}<p></p>

Let’s assume I have value a stored in a table, and I want to update it with values from b. So I want to do something like:

UPDATE data SET j = something(j, b) WHERE id = 10;

The question is: how to merge those JSONs.

Merging

For merging I’ve written a simple plpython …

Pretty Printing JSONs in PostgreSQL

By Szymon Lipiński
July 25, 2013

PostgreSQL has huge support for JSON type, like I wrote recently. It also has some operators for converting data to and from JSON, and the JSON type itself is great for ensuring that the JSON stored in database is always valid.

Pretty Printing JSON

The Problem

JSONs can be quite complicated and can have multiple levels. Look at them as normal strings: printing the values can increase their readability. Let’s use a sample JSON like:

{"a":42, "d":{"a":10, "b":[1,2,3], "c":"x2"}, "x":"test", "p":[1,2,3,4,5]}

I think it would be much readable in the form:

 {
     "a": 42,
     "d": {
         "a": 10,
         "b": [
             1,
             2,
             3
         ],
         "c": "x2"
     },
     "p": [
         1,
         2,
         3,
         4,
         5
     ],
     "x": "test"
 }

The Solution

To generate this kind of format, I created a very simple Python function:

CREATE FUNCTION pp_json(j JSON, sort_keys BOOLEAN = TRUE, indent TEXT = '    ')
RETURNS TEXT AS $$
  import simplejson as json
  return …

GNU Screen logtstamp string

By Jon Jensen
July 24, 2013

A short note on GNU Screen configuration:

You can add configuration to ~/.screenrc or another configuration file named by -c $filename upon invocation, and among the many options are some to enable logging what happens in the screen windows. This is useful when using screen as a reattachable daemonizer.

Consider this configuration:

logfile path/to/screen-output.%Y%m%d.log
logfile flush 1
logtstamp on
logtstamp after 5
log on

That works nicely. With logfile we specify the name of the log file, using some % escapes as per “STRING ESCAPES” in the manpage to put the date in the logfile name.

With logfile flush 1 we request that every 1 second the output be flushed to the log, making it easier to follow with tail -f.

The logtstamp on option writes a timestamp to the log after a default 2 minutes of inactivity. We shorten that to 5 seconds with logtstamp after 5.

Finally, log on turns on the logging.

Now, what if we want to customize the timestamp? The default looks like this:

-- 0:process-name -- time-stamp -- Jul/24/13  9:09:56 --

The manpage says that can be customized with logtstampt string ..., where the default is:

-- %n:%t -- time-stamp -- %M/%d/%y %c:%s --\n

The manpage …

has_many filter in RailsAdmin

By Marina Lohova
July 18, 2013

I enjoyed using the RailsAdmin record filtering abilities until one day I needed to find all the orders with the specific product.

class Order < ActiveRecord::Base
   has_many :products, :through => :orders_products
end

The following valid piece of RailsAdmin configuration did not break anything but did not work either:

RailsAdmin.config do |config|
  config.model Order do
    list do
      field :products do
        searchable :name 
      end
    end
  end
end

The reason is that only the belongs_to association is enabled for the search, as stated in the “Field searching” section of the documentation:

(3) Belongs_to associations : will be searched on their foreign_key (:team_id) 
or on their label if label is not virtual (:name, :title, etc.)

Benoit Bénézech, creator of RailsAdmin, confirmed this as well:

has_many are not added to the include for perf reason. That means that AR won’t find the :programs table

We only had a few has_many fields configured across the project, so I decided to look into the source code and see if the limitation can be bypassed.

MainController class in RailsAdmin invokes the “get_collection” method to fetch the records for the list action. It …